Partager via

XPath Powers: Calculating Totals

  • Article

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

OrderTotal.xsn

Comments

  • Anonymous
    December 19, 2006
    The comment has been removed

  • Anonymous
    December 21, 2006
    Boris, I'm afraid there isn't currently a workaround for this issue - the maximum number of event handlers/rule actions that can be executed is hard-coded to 16. We will try to address the issue (i.e. make the limit configurable) in InfoPath vNext. -Alex

  • Anonymous
    April 06, 2011
    Regarding to this 16 Rules limit, what does it mean? Can you please explain a little bit with example? Does that mean i only can have 16 actions in one rule?

  • Anonymous
    August 21, 2013
    I've done the same with  "calculated value field" but I'm not able to display this value to the SharePoint coulumn. When I populating coulumn to the SharePoint library I can see the column but the value is missing. May you can solve this issue Thanks in advance Pribub@hotmail.de

  • Anonymous
    January 28, 2014
    We have an application with a form control to display.  We are getting this error and was wondering if a 2010 assembly is getting referenced because office 2010 is installed without infopath and infopatb stand alone 2007 is installed.  I am trying to trace what version the assembly being called that has this 16 limitation.

  • Anonymous
    May 31, 2014
    how to step by step insert calculation fields. When I use the above example I get validation errors. I want quantity + $5 x subtotal = Total