Search code examples
infopathrepeat

How to pre-populate a summed field in an Infopath repeating table

I have an Infopath 2007 form with several views, meant to be used in a browser. I'm having trouble pre-populating a field in a repeating table, starting with a given value and then using a formula-derived default value.

On the Summary view, I ask for a project's TotalCost and the current fiscal year's projected spending (CurrentFYSpend):

+------------------------------+
|  Project Summary             |
+------------------------------+
|  Total Cost:       $100,000  |  -- TotalCost
|  Current FY Spend: $ 25,000  |  -- CurrentFYSpend
+------------------------------+
On the next (Detail) view, I have a repeating table FYSpending where the user needs to break out the project's TotalCost over an arbitrary number of fiscal years. The FYSpending table has a YearlyCost field which holds that year's estimated spending. Under the repeating table I have RunningTotal and Remaining fields. RunningTotal has a Default Value of sum(YearlyCost); Remaining has a Default Value of TotalCost - sum(YearlyCost); together they show the user how much of the total has already been accounted for, and how much they still need to break out into a Fiscal Year row.

When we first enter the Detail view, the YearlyCost field of the first row of the FYSpending repeating table should be populated with the CurrentFYSpend value:

+--------------------------------+
|  Project Detail                |
+--------------------------------+
|  Total Cost:        $100,000   |  <- TotalCost
|                                |
|  Fiscal Year Spending:         |
|  +---------------------------+ |  <- FYSpending repeating table
|  |  FY  |        Yearly Cost | 
|  +---------------------------+ |  <- FYSpending repeating table
|  | 2009 |           $ 25,000 | |  <- YearlyCost[1], from Summary CurrentFYSpend
|  +---------------------------+ |
|  | Add Fiscal Year |           |
|  +-----------------+           |
|                                |
|  Running Total:     $ 25,000   |
|  Remaining:         $ 75,000   |  <- TotalCost - sum(YearlyCost)
+--------------------------------+

When the user adds a new Fiscal Year row, I want the default value of the new row's YearlyCost field to be calculated as the remaining amount: YearlyCost[n] = TotalCost - sum(YearlyCost):

+--------------------------------+
|  Project Detail                |
+--------------------------------+
|  Total Cost:        $100,000   |
|                                |
|  Fiscal Year Spending:         |
|  +---------------------------+ | 
|  |  FY  |        Yearly Cost | |
|  +---------------------------+ |
|  | 2009 |           $ 25,000 | |
|  | 2010 |           $ 50,000 | |  -- YearlyCost[2], calc'd by rule on Add Row  
|  +---------------------------+ |
|  | Add Fiscal Year |           |
|  +-----------------+           |
|                                |
|  Running Total:     $ 75,000   |
|  Remaining:         $ 25,000   |
+--------------------------------+

I'm having trouble because the two prepopulating actions conflict with one another and appear to be creating a race condition. I tried to set a Rule on the Summary's CurrentFYSpend field to set the value of FYSpending's YearlyCost, then set a Default Value on the YearlyCost field to set Value: TotalCost - sum(YearlyCost). When I save the form, I get the following error:

An error occurred in the form's code. The number of calls to the Changed event for a single update in the data exceeded the maximum limit.

(Thanks, InfoPath, for such specific error message! How much do I need to pay to get the name of the object that fired the event, or that you tried to update?)

Anyway, can anyone describe a technique for accomplishing this? In summary,

  1. populate first row's YearlyCost from the previous view's CurrentFYSpend
  2. allow overriding the first row's YearlyCost, and populate subsequently-added rows' YearlyCost default value to TotalCost - sum(YearlyCost)
Solution

  • And another technique from Jimmy Riche at on the infopathdev.com forums:

    CurrentFYSpend has a rule that sets YearlyCost = .

    RunningTotal has a Default Value of sum(YearlyCost), updated when the formula is recalc'd. Remaining has a Default Value of TotalCost - RunningTotal, updated when the formula is recalc'd.

    Finally, the YearlyCost field in the repeating table has a Default Value of

    (Remaining) * (Remaining > 0), not updated when the formula is recalc'd. (neat trick, huh?)

    This works nicely, although the formula for YearlyCost is, um, non-obvious. But, I'm finding that a lot of XPath is like that -- you have to find non-obvious solutions to things that would be dead simple in an imperative language. Anyway, this works, so I'm implementing it.