How to conditionally sum up the values from a repeating section

  • 16 January 2019
  • 8 replies
  • 13 views

Badge +5

Happy New Year!!!

So, long story short.. (maybe happy.png)

LAY OF THE LAND

We have a form with a repeating section.

In the repeating section there are four controls.

The two of primary concern for this question are a single line textbox (overdraftAmount) control and a yes/no (managerApproved) control.

The overdraftAmount textbox accepts a currency value, the managerApproved yes/no, hmmm, you know what that does happy.png

When currency values are typed into the overdraftAmount textbox they are summed up in another textbox, totalOverdrafts. < no issues here. We have a second textbox totalOverdraftAmountApproved that needs to conditionally sum the values in the overdraftAmount textbox if the managerApproved yes/no control is checked, and vise versa when it is unchecked.

Clear as mud?

THE QUESTION

How to conditionally sum up the values in a single line textbox when an associated checkbox is checked/unchecked.

I'm including a screen capture that may assist us in our hunt for truth and knowledge happy.png


8 replies

Userlevel 5
Badge +14

Should be pretty straight forward. 

Here is a quick example. 

Control Layout:

222017_pastedImage_1.png

Control Names & Settings: 

(single line text for currency input)

222018_pastedImage_3.png

(yes/no for Approval) 

222019_pastedImage_4.png

(calculated control for total money in all rows)

222020_pastedImage_5.png

(calculated control for total money that has been approved - see below for formula)

222021_pastedImage_6.png

Formula For 'Filtered' Calculation: 

(function(moneyValues, approvalValues){
  var approvedValues = [];
  for(var i = 0; i < moneyValues.length; i += 1) {
    if (approvalValues[i]){
      approvedValues.push(moneyValues[i]);
    }
  }
  return sum(approvedValues);
}(someMoney, moneyApproved))

(note: at the bottom, you'll need to replace someMoney and moneyApproved with there NAMED CONTROL references as shown here:)

222023_pastedImage_8.png

Results: 

222022_pastedImage_7.png

This should get you to where you're trying to go, but please remember that you might want to think about implementing a third party library to handle the currency input checks and totaling due to how javascript natively handles floating point math!!!!!

222025_pastedImage_31.png

I'm currently using currency.js much to my liking, but it takes a little bit to get used to how it handles things. You'll still need to probably create a validation rule to normalize the input for the currency (and to possibly restrict automatically or by way of validation) "what" passes as an acceptable numeric input, but as for totaling, it works fairly easily. 

Hope this helps! 

Userlevel 5
Badge +14

other possible approach that doesn't need javascript:

- add one another calculated value control into repeating section, and configure its formula like

if(managerApproved,overdraftAmount,0)

- name the calculated control, eg. approvedAmount

- if you do not want to show the control to the users configure a formatting rule to hide the calculated value control

- update total approved amount formula like 

sum(approvedAmouont)
Badge +5

N M, first of all, I sure hope that is a picture of you!!! second, thanks for making the time to put together such an AMAZING reply! I appreciate the time and effort you put into this. THANKS!!

I'll give it a look see today.

Badge +5

Marian, thanks for making the time to respond. I will take a look at this today.

Userlevel 5
Badge +14

it is, and you're welcome! grin.pnglaugh.png

Badge +5

We went with your no code solution.

Thank you so much for your answer!

Badge +5

We have an operation directive to go with no code solutions when we can.
Your coded answer is amazing and I've captured it for future reference.

Thanks

Userlevel 5
Badge +14

Awesome hotdogs! Happy calculating! 

Reply