Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

XPath Powers: Calculating Totals

XPath Powers: Calculating Totals

  • Comments 6

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

Attachment: OrderTotal.xsn
Leave a Comment
  • Please add 8 and 4 and type the answer here:
  • Post
  • As a part of a complex form both Rules and CLR responsible for calculation

    and recalculation balances on a tax form.

    Following error is thrown when attempting to open form with too many

    sections that require calculations:

    ---------------------------------------------

    An error occurred in the form's rules or code. The number of rule actions or

    the number of calls to the OnAfterChange event for a single update in the

    data exceeded the maximum limit.

    The rules or code may be causing an infinite loop. To prevent this, ensure

    that the rule action or event handler does not update the data which causes

    the same rule action or event handler to execute.

    --------------------------------------------------

    There is a limit from what I gather of number of calculations performed on

    the form.  And the limit is 16. Is there a way to set the limit to a higher

    number.

    Is there a way to resolve the error?

    --

    Boris Kleynbok

    Web Developer

    DotComFactory

  • 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

  • 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?

  • 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

  • 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.

  • 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

Page 1 of 1 (6 items)