Question

Forms - perform calculation within table row if conditions are met

Forms

Updated October 3

asked on October 1 • Show version history

Hello,

I have a form with a table with columns like below:

In some cases, these columns are populated via a lookup rule (bottom row). In others, the mileage rate and amount due columns are not populated. When they're not populated, I'd like to have the Amount Due column multiply the Quantity column by the Mileage Rate column. That way, the user only has to enter the mileage rate, and then the Amount Due will calculate for them.

Is there a 'safe' way to do that so that this only runs on the rows I want it to? It *should* still always result in the same number, but I don't want to run into any conflicts with the lookup rule and the calculation both being performed.

Thanks!

0 0

Answer

SELECTED ANSWER

replied on October 3

Something like this should work - although I haven't built out a test to confirm.

=IF(OR(INDEX(Ticket_Summary.Quantity,ROW())="",INDEX(Ticket_Summary.Mileage_Rate,ROW())=""),"",ROUND(PRODUCT(INDEX(Ticket_Summary.Quantity,ROW()), NDEX(Ticket_Summary.Mileage_Rate,ROW())), 2))

To make it a little easier to see what I did, here it is displayed across several lines. You can see that the 8th line is your exact formula. Lines 4 and 5 are parts of an OR formula that says if one or the other of the values (copied out of your formula) are blank, then the IF formula is going to evaluate as TRUE. Line 7 is what is returned if it is TRUE, which is a blank string. Line 8 (your formula) is what is returned if it is FALSE.

=
IF(
  OR(
    INDEX(Ticket_Summary.Quantity,ROW())="",
    INDEX(Ticket_Summary.Mileage_Rate,ROW())=""
  ),
  "",
ROUND(PRODUCT(INDEX(Ticket_Summary.Quantity,ROW()), INDEX(Ticket_Summary.Mileage_Rate,ROW())), 2)
)

0 0

Replies

replied on October 2

You probably need to have different versions of that Amount Due field - one populated by the lookup and one populated by the formula.

You could even have both of these hidden and then show a third field. The third field can use an IF formula to see if the field from lookup has a value and displays that value if it does, and otherwise displays the value from the field with the formula instead.

1 0

View 1 previous reply

replied on October 3

Hi Matthew,

I might try just not populating the Amount Due column, and use the Quantity and Mileage Rate columns within a formula. It's the same numbers, so rather than pull in the Amount Due, I'll just calculate it on the form.

What would my formula look like if I wanted to include an IF statement to only perform the calculation once my two fields have data? Here's what I have currently, which runs immediately, even if I don't have anything in the Mileage Rate column, which is expected.

=ROUND(PRODUCT(INDEX(Ticket_Summary.Quantity,ROW()), INDEX(Ticket_Summary.Mileage_Rate,ROW())), 2)

I'm trying to mess around with adding an IF statement, but I'm not getting the syntax right. I basically only want to run this calculation when both columns in a given row have data so as to not confuse the user.

Thanks for the help!

0 0

SELECTED ANSWER

replied on October 3

Something like this should work - although I haven't built out a test to confirm.

=IF(OR(INDEX(Ticket_Summary.Quantity,ROW())="",INDEX(Ticket_Summary.Mileage_Rate,ROW())=""),"",ROUND(PRODUCT(INDEX(Ticket_Summary.Quantity,ROW()), NDEX(Ticket_Summary.Mileage_Rate,ROW())), 2))

=
IF(
  OR(
    INDEX(Ticket_Summary.Quantity,ROW())="",
    INDEX(Ticket_Summary.Mileage_Rate,ROW())=""
  ),
  "",
ROUND(PRODUCT(INDEX(Ticket_Summary.Quantity,ROW()), INDEX(Ticket_Summary.Mileage_Rate,ROW())), 2)
)

0 0

replied on October 3

Additionally - you still might want to retain separate fields since you are doing a sometimes this comes from lookup and sometimes it is manual thing. Any changes that trigger the lookup to happen again could overwrite anything your users have entered manually, even if it is just overwriting them with blanks.

Best practice is to keep manual fields and lookup fields separate.

One thing I do on a form that does this - is I keep separate versions of the fields for the manual entry and the lookup entry and use field rules to either show the lookup version if anything came from the lookup or show the manual version otherwise. Then the formula on the total can check which one to use like previously discussed.

0 0

replied on October 3

That all makes sense and should do what I need it to. Thanks!

1 0

You are not allowed to follow up in this post.

Question

Question

Forms - perform calculation within table row if conditions are met

Answer

Replies

Sign in to reply to this post.