Solved

365 Responsive Form - Real time sum calculation not working

  • 1 February 2024
  • 7 replies
  • 55 views
G
Badge +5

I have 4 different “total” fields, which are calculating correctly, doing a multiplication when values are entered. 

  • Transportation Costs
  • Total Cost of Hotel
  • Total Cost Per Deim
  • Total Cost of Meals

I’d like for the grand total field, “Total Cost of Travel” to be updated in real-time.

 

The problem I’m having is that unless the user inputs a value in every field, even a 0, it won’t work. I’ve also tried to set a default value of 0 in each field, but it still only calcs if every field is inputted. 

The only work around I’ve found is to make all the fields required so that the user is forced to input something, even a 0, in every field. But I’d prefer not to make these required fields unless I have to. 

 

So in this example below, there are no food or hotel costs, but there is a “Transportation Cost”, which is picked up from a previous page in the form. So the total at this time for “Total Cost of Travel” should also be $10. But I can’t get that $10 to show up in the total field unless I put 0 in each of the six previous fields used for food and hotel. If I put something in every field, the grand total works great.  

 

FORMULA

sum([Form].[Total cost of hotel],[Form].[Total Cost of Meals],[Form].[Total Cost Per Diem],[Form].[Mode Cost] )

 

What am I missing here? 

 

 

icon

Best answer by GoIllini 2 February 2024, 21:19

View original

7 replies

Garrett
Userlevel 6
Badge +16

Hi @GoIllini 

Which type of 0365 form? New Responsive?

Do you mind to share the form rules to update the grand total field, “Total Cost of Travel ?

 

Let use Hotel as an example. If “Number of Nights” is not filled, the “Total Cost of Hotel” should default to ZERO, right? This default is set for all the sub-Total, right?

 

G
Badge +5

It is the New Responsive type for an SPO list item.

The rule for the grand total field is… If Title does not contain {    This condition should always be true as the title field is read-only and programmatically set and will never have this character. 

 

All the other “total” fields mentioned above, generally work the same way. If the two associate fields have values, perform a multiplication, else make value 0. Here is the Hotel total rule, for example. 

 

 

Garrett
Userlevel 6
Badge +16

Hi @GoIllini 

Your form rules logic is sound. 

IF you want the condition to always be TRUE, I use the following “1” Equals “1”

 

I created the form with just the Sub-Total and the Grand Total. It is working.
The fields are not mandatory (Required is NO)

 

I added a Unit Price, Qty and Total field. Grand Total calculation works when Qty is left empty 

 

The only other thing which I can think off is to check is the order of your form rules. 
Is the Grand Total rules below the Sub total rules?
 

G
Badge +5

In your IF statement, how did you get the values of 1? I tried a couple of different formulas but it won’t simply let me put a 1 there. 

The grand total is not the last rule. Transportation cost is below it. I don’t see how to reorder though. Googling it did not return answer for this version of forms. How do I reorder? Drag n drop won’t work. 

I had not tried to just do a quick form with only these fields. Of course, I can get it to work fine there just as you did! 😣

 

Garrett
Userlevel 6
Badge +16

Hi @GoIllini 

  1. Insert as Formula. “1”. That’s why there is the green FX symbol.
  2. There is no way to rearrange the rules. 
    This is a workaround
    Disable the existing “Total All Cost. Keep it as a reference.
    NEXT, 
    Duplicate or clone the “Total Transporter” rule - Click the Copy icon
    Rename the “Total Transporter - Copy” to “Total All Cost”
    Reconfigure the rules to be similar to “Total All Cost”.    
G
Badge +5

Thank you for the 1 = 1 tip. I got that working and will use in future. 

 

I tried disabling and then recreating the rules to alter the order, but based on my tests on a couple of forms, the order did not seem to matter for performance of the calcs. 

 

I finally got it working but it doesn’t make sense. Like you, I too got it to work fine in a test form with ONLY these fields. But my larger production form, 50+ fields, still would not work. What I figured out was, the “meals” field was the only one that was an open entry field. All others were sub-total fields where value is set by rule. 

 

So I made a “meal total” field that was hidden. I also created a rule that multiplied the meal total * 1 and placed it in this hidden field. 

 

Initially, that was not enough to get it working either. For the grand total rule, I stripped it out and started from scratch. I readded the fields one at a time. Updating the rule, and then preview the form to test. I did this one by one until all fields were in the SUM formula. It finally calculated correctly after doing a more granular update. Not sure why these steps were needed but I finally got the result I was after.

Thank you @Garrett for your guidance!

Garrett
Userlevel 6
Badge +16

Hey @GoIllini 

Glad you manage to solve the issue. 
 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings