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

Question

Question

SUM Calculation With Multiple IF Statements Not Calculating Correctly

Forms
Updated February 14, 2022
asked on February 9, 2022

I am building a brand new form in Forms 11.0.2106 using the new designer and am having an issue with a SUM calculation.  The form is a Travel Authorization Form that has a table that fills in the GSA amounts for food and the submitter then selects which meals were covered that day by the travel policy and of those meals, which were provided by the event (thus they would not be reimbursed for them).

The "Amount" currency field has a calculation written that only includes the Breakfast GSA amount in the line total if "Breakfast" is checked in "Daily Meals" and "Breakfast" is NOT checked in "Covered by Event".  When the SUM statement ONLY has the function for Breakfast then it works fine, as seen above where "Amount" is $15.00. Below is the calculation used.

=SUM(IF(TEXT(INDEX(Food_Estimation.Daily_Meals.Breakfast,ROW()))="TRUE",IF(TEXT(INDEX(Food_Estimation.Covered_By_Event.Breakfast,ROW()))="FALSE",INDEX(Food_Estimation.Breakfast_GSA,ROW()),0),0))

When the calculation is updated to include Lunch, it processes the Breakfast portion of the IF statement fine but then includes automatically without even checking it, as can be seen below where the "Amount" is now $35.00.

Below is the calculation that includes the IF statements for both Breakfast & Lunch.

=SUM(IF(TEXT(INDEX(Food_Estimation.Daily_Meals.Breakfast,ROW()))="TRUE",IF(TEXT(INDEX(Food_Estimation.Covered_By_Event.Breakfast,ROW()))="FALSE",INDEX(Food_Estimation.Breakfast_GSA,ROW()),0),0),
IF(TEXT(INDEX(Food_Estimation.Daily_Meals.Lunch,ROW()))="TRUE",IF(TEXT(INDEX(Food_Estimation.Covered_By_Event.Lunch,ROW()))="FALSE",INDEX(Food_Estimation.Lunch_GSA,ROW()),0),0))

If I remove the Breakfast code and just use the Lunch code then it works fine with the Lunch checkboxes so I know each individual piece of the code is correct.  I also tried removing the SUM part and using + but that produced the same results. 

I know to work around this that I can write the values to other hidden fields or create a super complex nested IF statement to get this to work.  This post is to see if anyone has gotten Forms to SUM different IF statements within the same calculation?  Or is this a bug in the new forms designer?  Or is this something that has never worked and there are no plans to ever have it work?  

0 0

Answer

SELECTED ANSWER
replied on February 13, 2022 • Show version history

I confirmed it is a bug in Forms 11 Update 1(11.0.2106) and has been fixed with Forms 11 Update 2(11.0.2201).

1 0

Replies

replied on February 11, 2022

Good/Bad News...Your formula works for me.

 

Caveat: I just updated to Forms 11 Update 2...so maybe that's your answer.

1 0
replied on February 14, 2022

Thanks for checking.  Glad it was just a bug and not something silly where I'd just missed a comma.

0 0
replied on February 11, 2022 • Show version history

Have you tried adding just one more set of parentheses around the entire breakfast and lunch sections?

=SUM((IF(TEXT(INDEX(Food_Estimation.Daily_Meals.Breakfast,ROW()))="TRUE",
     IF(TEXT(INDEX(Food_Estimation.Covered_By_Event.Breakfast,ROW()))="FALSE",
     INDEX(Food_Estimation.Breakfast_GSA,ROW()),0),0)),
    (IF(TEXT(INDEX(Food_Estimation.Daily_Meals.Lunch,ROW()))="TRUE",
     IF(TEXT(INDEX(Food_Estimation.Covered_By_Event.Lunch,ROW()))="FALSE",
     INDEX(Food_Estimation.Lunch_GSA,ROW()),0),0)))

0 0
replied on February 11, 2022

I hadn't tried that.  Great idea laugh... but no luck crying.  It still has the same issue unfortunately.  I even tried your parenthesis suggestion and changed it from SUM to just have a + between the two and it had the same incorrect result.  Thanks for the great idea though!

0 0
replied on February 11, 2022

Well poo! I'm actually working on a reconciliation form and plan to do something similar. I'm going to give the new designer another whirl on this one. So I'll let you know if I can get it working.

0 0
replied on February 11, 2022

I am building out a work-around where I am calculating each if statement in a hidden field and then doing just the final calculation in the Amount field.  Not ideal since it requires 3 pointless fields to bloat the form/database just to work around a deficiency in the Calculation portion.  I'll definitely let you know if I do find some other way to do this though without having to resort to a work-around or stored procedure. 

1 0
replied on February 11, 2022
0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...