You are viewing limited content. For full access, please sign in.

Question

Question

Expense table calculation if then, when Rate is outside the table

Forms Version 10
Updated December 3, 2020
asked on November 26, 2020 • Show version history

I need help with an expense form table in calculating the amount if the drop-down choice is Transportation.

I could maybe have the values in the drop-down choice as above and have it calculate the amount directly from there, with the zeros so there's no calculation if they pick something other than Transportation? 

Or not use the values in the drop-down and have it only calculate into a new field if transportation is picked? Can I have the mileage rate be in a hidden field outside the table?  This is as far as I get, but I don't know how to make it calculate based on what it finds in a field outside the table:

=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation", MULT(INDEX(Expenses.Kilometers,ROW()), ???,) ???

 

 

0 0

Answer

SELECTED ANSWER
replied on December 3, 2020 • Show version history

As to the issue with putting a value into a field with a calculation, this is typically why you would have a Field for the user to add the value of the Expense as a separate field and then you would add those values (Expenses and Transportation) together into the Amount to be Reimbursed field which instead of a IF would then become a Sum calculation.

Amount to be Reimbursed = =SUM(INDEX(Expenses.Expense,ROW()),MULT(INDEX(Expenses.KM,ROW()),0.50))

You can use a Rule to Show / Hide the KM / Expense field when the selected value is Transportation. I typically would set the Total field to be read only as well so they cannot attempt to put a value into it.

The Table would end up looking like this when completed (FYI, The KM Column will only appear if the Transportation category is Selected on any row)

1 0

Replies

replied on November 27, 2020

Can you just use the rate number directly in the formula instead of get the value from the a field outside table?

such as "=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation", MULT(INDEX(Expenses.Kilometers,ROW()), 0.59))"

0 0
View 8 previous replies
replied on November 27, 2020 • Show version history

Thanks, Xiuhong.  I get this when I try:

So, in this scenario, I pick Transportation out of the drop-down, then move on to the Kilometers field and enter a value, then tab to the next field which is for the calulated result and has the above formula in it.  But it just stays blank and when I go back to the form layout page it shows the error there.

I have double and triple checked the spelling of all the variables.

0 0
replied on November 27, 2020

I even tried adding another column to hold the Kilometers rate and used this formula:

=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation", MULT(INDEX(Expenses.Kilometers,ROW()), MULT(INDEX(Expenses.K_Rate,ROW()))

and this

=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation", PRODUCT(INDEX(Expenses.Kilometers,ROW()), INDEX(Expenses.K_Rate,ROW()))

0 0
replied on November 29, 2020

Hi Connie, your IF formula does not have the else portion of the condition.

IF(var1=“Value”, Result1, Result2)

Not seeing where you are entering the amount for the other expense types, your Reimburse Field should be the calculation to add the other expense types as well as the Transportation types.

0 0
replied on November 30, 2020 • Show version history

Are you asking me what field the calculation is in?  It is in a separate field on the table, labelled Calculated_Result. 

Okay, so I'm missing the ELSE portion.  Hmm... Holy Cow, that worked!  I just used the same statement for the ELSE, just to see if I could get something and it immediately worked!! Thanks, Steve!  Example that worked: 

=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation",MULT(INDEX(Expenses.Kilometers,ROW()),0.59),MULT(INDEX(Expenses.Kilometers,ROW()), 0.59))

There is still the original Amount_To_Be_Reimbursed field.  Originally, I was hoping that the Amount field could calculate any "Transportation" amounts based on the formula, but let it be filled manually for the other types, but I expected that was likely not do-able.  I tried putting the .59 in as the Variable for the Transportation choice and 0 as the variable of all the other choices.  

0 0
replied on November 30, 2020

Hi Connie, a Choice selection's value must be unique, so typically you cannot have multiple choices with the same values. If you open you choices field you may see where LF automatically added to your choice values to account for this.

0 0
replied on November 30, 2020

Ya, I was pretty sure that wouldn't work.  I ended up adding the Calculated_Result field that they will have to look at and then transfer manually into the Amount_To_Be_Reimbursed field.  Is that my only option, or could it be handled in another way?

Thanks for your help!

0 0
replied on November 30, 2020

Maybe some JavaScript that would say, If Calculated_Result is not empty, fill Amount_To_Be_Reimbursed with Calculated_Result?  Then I could hide the Calculated_Result field?

replied on November 30, 2020 • Show version history

Oh!  Maybe this would be simpler!  An IF THEN statement in the Amount_To_Be_Reimbursed field?  Wow!  That's what I needed all along!!  Now I can delete the calculated result field!!

=IF(INDEX(Expenses.Expense_Category,ROW())="Transportation",MULT(INDEX(Expenses.Kilometers,ROW()),0.59), 0)

0 0
replied on November 30, 2020 • Show version history

Oh!  :(  There's a downside to this.  Now every time I ADD another line, anything but the transportation lines zero out!  Even though I manually entered a Meals amount before adding another line.

0 0
replied on December 1, 2020 • Show version history

This is something we will look into improve. The data user entered should have higher priority than the auto calculated value.

1 0
SELECTED ANSWER
replied on December 3, 2020 • Show version history

As to the issue with putting a value into a field with a calculation, this is typically why you would have a Field for the user to add the value of the Expense as a separate field and then you would add those values (Expenses and Transportation) together into the Amount to be Reimbursed field which instead of a IF would then become a Sum calculation.

Amount to be Reimbursed = =SUM(INDEX(Expenses.Expense,ROW()),MULT(INDEX(Expenses.KM,ROW()),0.50))

You can use a Rule to Show / Hide the KM / Expense field when the selected value is Transportation. I typically would set the Total field to be read only as well so they cannot attempt to put a value into it.

The Table would end up looking like this when completed (FYI, The KM Column will only appear if the Transportation category is Selected on any row)

1 0
replied on December 3, 2020

This works beautifully, Steve!  Thanks!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...