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
, th开发者_如何学Cen 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,
- populate first row's
YearlyCost
from the previous view'sCurrentFYSpend
- allow overriding the first row's
YearlyCost
, and populate subsequently-added rows'YearlyCost
default value toTotalCost - sum(YearlyCost)
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.
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.
精彩评论