Running Total with InfoPath

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 inRunningTotalDesign

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:

RunningTotalFormula

../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:

RunningTotalExample

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.

Advertisements
This entry was posted in InfoPath and tagged . Bookmark the permalink.

One Response to Running Total with InfoPath

  1. Chelan Schultz says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s