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

vcohen — Thu, 22 Oct 2009 21:08:04 GMT

That seems to do the trick -- thanks very much! You used an interesting technique in the formula for the default value in YearlyCost -- I'd never have thought to multiply it against the boolean value returned by testing if Remaining > 0.

Thanks again!

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

Jimmy — Thu, 22 Oct 2009 12:50:25 GMT

Please inspect the attached XSN, which should be a simple mock-up of what you described, though all in one view. The logic in this form is contained in:

The rule on the CurrentFYSpend field
The default values on YearlyCost, RunningTotal, and Remaining (Note that the "Recalculate this value" box is unchecked for YearlyCost)

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

vcohen — Tue, 20 Oct 2009 00:37:24 GMT

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!

Controls

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

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

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