XPath Powers: Calculating Totals
InfoPath makes it trivial to track totals for repeating structures, such as customer orders. Just create a repeating table of line items, and sum up the totals for individual line items. However, sometimes, totals for line items may not be available; it is still possible to perform dynamic calculations across the repeating structure, even when interim results (line item totals) are not stored in the data source. This article will explain a way to make this work without code.
Let's explore the simple case first.
| Form at Runtime | Data Source |
 |
 |
Line item total is set by using a default value (price * quantity) on the lineItemTotal node. Creating an order total is just a matter of adding an expression box that uses the built-in SUM function:
sum(my:group1/my:lineItems/my:lineItemTotal)
And voila, we're done, totals will be calculated correctly.
The reason why we are here: the complex case.
What if the interim results (line item totals in our scenario) cannot be persisted in the data source? This situation might arise if you're operating on a fixed schema, or if you're an "XML purist" (I know I am :-)), arguing that there is unnecessary redundancy in your XML if you store calculated values.
The goal is still the same - but the data source is different.
| Form at Runtime | Data Source |
| |
 |
The line item total would be an expression box instead of the text box; it would be calculated simply as (price * quantity).
But how do we calculate the order total? Your first instinct may suggest to use sum (price * quantity), but you'll soon discover that the SUM XPath function only takes a nodeset...
Let's recall the clever technique of iterating through repeating items by using just XPath: it was described in detail in this article. Let's use the following value for the order total expression box:
sum(xdMath:Eval(my:lineItems, "my:price * my:quantity"))
Why does this work? Let's go inside-out:
1) The eval function evaluates the price * quantity expression in the context of line items, and returns a nodeset with the results.
2) The sum function takes in a nodeset as a parameter, and sums up its contents, giving us the desired total.
I'm attaching a sample form template that has this technique implemented; save the XSN locally before opening it up.
This method works in InfoPath 2003, 2007, and is supported in browser-enabled form templates.
Alex Weinstein
Program Manager