Calculate cumulative Total

TB17

20-05-2015

The following is designed as a repeating subform with new row instances added via an Add button.

I need each Cumulative Payment field (on each row) to auto-calculate based on either:

1. the "current" and "all previous" payment amounts (i.e. cumulative payment 3 ($1,800) = sum of payments 1, 2, and 3)

or

2. the previous cumulative payment + current payment (i.e. cumulative payment 3  ($1,800) = cumulative payment 2 ($1,100) + payment 3  ($700)

Period    Payment     Cumulative Payment

     1.          500               500

     2.          600               1,100

     3.          700               1,800

Can someone please assist. I understand I need to grab the"previous instance(s)" put not sure what I'm doing wrong...

Accepted Solutions (1)

Accepted Solutions (1)

jasotastic81

20-05-2015

Here's my solution to this problem.

765989_pastedImage_0.png

Note the i<=this.parent.index in the condition of the for loop. That stops the calculations on whatever line you're entering numbers into. (You wouldn't want to add the payment from line 3 to line 2's total, for example.)

Answers (3)

Answers (3)

jasotastic81

20-05-2015

Not at my computer to test, but I wrap the whole function in another if statement like

if (!nfPayment.isNull)

so your cumulative total isn't entered unless there's something in the payment field. The function should already be able to handle blank fields as far as adding them together. If not, you could wrap the subtotal calculation in an if statement, too.

if (!rows.item(i).isNull)

(Please excuse any typos as I'm doing this from my phone.)

TB17

20-05-2015

jasotastic -

I really appreciate your help on this. It's exactly what I was looking for...! I was using a different script that worked for the most part, but it failed to update the Cumulative total whenever the user changed a payment amount. Your script does update the total, so I'm a very happy camper.

The only thing I wonder is if it's possible to not show the Total value(s) on subsequent rows if no payment amount has been entered on active row (null) and/or if the payment field is blank on that row. For instance, when the form is first opened, I have 3 instances show up by default and this is what I see:

NOTE: ... = blank or null value

Period    Payment     Cumulative Payment

     1.          ...               ...

     2.          ...               $0

     3.          ...               $0

If I then proceed to enter a payment amount in the first row, I get:


Period    Payment     Cumulative Payment

     1.         $500            $500

     2.          ...               $500

     3.          ...               $500

What I would prefer (if possible) is:


Period    Payment     Cumulative Payment

     1.         $500            $500

     2.          ...               ...

     3.          ...               ...

and/or something like this:

Period    Payment     Cumulative Payment

     1.         $500            $500

     2.          ...               ...

     3.         $600             $1,100

     4.          ...               ...

     5.         $9,500          $10,600

Please advise, and THANK YOU for your assistance thus far.