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.
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) = 0) * 0, substring(../preceding-sibling::my:Item/my:RunningTotal, 1, (count(../preceding-sibling::my:Item) = 1) * string-length(../preceding-sibling::my:Item/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.