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

Question

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)

Many thanks!

Answer 1

OK, I got an answer from Alec Pojidaev, who's quite the InfoPath expert. He says:

You have couple problems I was able to pinpoint. The first is you checked checkbox "Update this value when result of the formula is recalculated" on YearlyCost field. Its one time operation so you dont want to recalculate it.

The second problem is formula itself. The result of your formula ../../../my:TotalCost - sum(.) is absolutely the same as ../../../my:TotalCost - .
sum() function have no sense in that context.

If I understand your intention correct the right formula should be: ../../../my:TotalCost - sum(../../my:FY/my:YearlyCost)

After I made these changes, the form worked as desired.

The sum(.) in the formula was automatically generated. I built the formula thru point-and-click rather than writing the XPath expression. When I added the sum() function and selected the YearlyCost field, the formula editor replaced YearlyCost with a . I figured it knew what it was doing, so I left it alone. I see I need to pay close attention to what underlying XPath is being generated.

Answer 2

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.