I posted this question on StackOverflow at http://stackoverflow.com/questions/1590703/how-to-pre-populate-a-summed-field-in-an-infopath-repeating-table , but haven't received any responses; apparently not many InfoPath experts hang out there. Then I found this forum; hopefully someone here can weigh in (or go over to StackOverflow; the InfoPath tag could use some love).
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 pre-populating 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!