On a recent project I had a requirement to create a running total in a repeating table in an InfoPath form. I did a lot of searching and found several people that seem to be confused about what a running total is. I wanted an extra column in my table that showed the total up to that line. This turns out to be tricky to find but quite easy to implement.

I found an answer on Stack Overflow by Stas Slabko who describes how to calculate a product with a repeating table. I used this as a basis for my running sum.

So we need a really simple InfoPath form with a repeating section in

In the table’s footer I have a the TotalValue field that has a simple formula of sum(Value). The running Total field on the repeating line has a slightly more complex formula as follows:

../my:Value + concat((count(../preceding-sibling::my:Item[1]) = 0) * 0,
substring(../preceding-sibling::my:Item[1]/my:RunningTotal, 1,
(count(../preceding-sibling::my:Item[1]) = 1) *
string-length(../preceding-sibling::my:Item[1]/my:RunningTotal)))

The RunningTotal field on a line is calculated by adding the Value field to the RunningTotal of the preceding-sibling (i.e., the line above). If we were to do this on the first row of the repeating table then preceding-sibling would return NaN (Not a Number). In order to overcome this we check if the count of the preceding sibling is 0 and get either a true or false, we multiply that by a string length to trim out any values that would say NaN.

So how does this look in Preview:

Stas in his article lists the following:

- Works in a browser form in any sharepoint environment
- Is not impacted by “16 calculations” threshold
- The formula is robust to row removal and shifts

Be aware that this solution is impacted by the 16 calculations threshold. When you update a row then this cascades to all the subsequent rows as cascaded calculations. Therefore if you change a row that is too far back this will cascade and could hit the limit. When I implemented I used a Formatting Rule to disable values that were too many rows from the end of the table. However the way I did that will work only in client InfoPath.

Hope this article helps someone and that my version of a running total matches your version.

### Like this:

Like Loading...

*Related*

What if I would like to do this (I got it to work on one of my forms that I’m working on) but I would like to start with a balance (think of a new $100 re-loadable gift card) and see a running total on each row that includes a deduction of any purchases made as well as any payments put onto the card (re-loading).

$100 balance at the beginning of the month. $5 purchases need to be deducted from this $100 balance, as well, any “deposits” made to the current balance have to be added in to create a new balance from which subsequent $5 purchases will be deducted.

I’m fairly new to this (we’re talking a couple of weeks – I know, jumping on the InfoPath “bandwagon” AFTER MS announces there will be no more InfoPath). I am using 2007.