Level 1

0% to

Level 2

Tip /

to gain points, level up, and earn exciting badges like the new
Mission!

View all

## Calculate cumulative Total

TB17
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile

TB17
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile
TB17
Level 2

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)

jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

43 solutions
View profile

jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

43 solutions
View profile
jasotastic81
Level 4

20-05-2015

Here's my solution to this problem.

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.)

TB17
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile

TB17
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile
TB17
Level 2

20-05-2015

Thank you! I was able to get it to work using if (!nfPayment.isNull)

jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

43 solutions
View profile

jasotastic81
Level 4

Likes

35 likes

Total Posts

329 posts

43 solutions
View profile
jasotastic81
Level 4

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
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile

TB17
Level 2

Likes

6 likes

Total Posts

20 posts

3 solutions
View profile
TB17
Level 2

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