Let's make sure I'm reading you correctily.
If you have a personnel budget of 100,000.00 and then modify it to 110,000.00, you want to see a row that reads:
Personnel | $110,000.000 | $10,000.00 | ?????? |
It's the total part that is unclear. Should it be a running total of all budget items for a given program or ?
Sorry about the confusion. What I'm looking for with your example would be:
Personnel | $100,000 | $10,000 | $110,000|
If that was modification 1, I would want mod 2 to pull the $110,000 like so:
Personnel | $110,000 | $10,000 | $120,000 |
Does that make sense? I would potentially sort them by program number and then modification number. Pulling the most recent/highest mod number's third column into the first of a new record when it is created.
I may be going about this the wrong way but that's why I love this forum! Thanks in advance!
OK, before you explained, I was mentally flipping a coin to decide between what you want and what I posted...
A self join relationship can be set up so that the first column is actually a field from a related occurrence of the same table based on the budget item and program number. Here's the relationship, but you'll need to modify it to work with your actual field and table occurrence names:
BudgetMods::ProjectID = BudgetModsByItem::ProjectID AND
BudgetMods::BudgetItemID = BudgetModsByItem::BudgetItemID AND
BudgetMods::ModID ≠ BudgetModsByItem::ModID
In this relationship, you create BudgetModsByItem by selecting BudgetMods in Manage | database | Relationships and clicking the duplicate button (has two green plus signs). Then you double click the new occurrence in order to change its name. Then you double click the relationship and specify a sort order for BudgetModsByItem to sort it in descending order by ModID so that from any given record on your layout, this relationship refers to the most recent BudgetModification for the same budget item but not to itself.
To set up your layout, base it on BudgetMods and use BudgetModsByItem::cBudgetAmount for column 1. Use BudgetMods::cBudgetAmount for the last column.
To define this field, do it in two steps:
Step 1: Define cBudgetAmount as a simple number field. This puts it in the field list so you can refer to the related field in it's calculation.
Step 2: Now change it to a field of type calculation select BudgetMods in the "context" drop down and give it this expression:
If ( BudgetModsByItem::cBudgetAmount ; BudgetModsByItem::cBudgetAmount + ModificationAmount ; ModificationAmount )
Hmmm, kinda painted us into a bit of a corner there. This works, but requires that your initial budgetMod record document the original budget amounts by having a budget amount of 0 and a modification amount = to the initial budget amount. That works, but may not look like what you want for the first set of modification records. You can, however, omit these initial "starting balance" modification records from your found set and then your report may look like what you want here.
An alternative approach is to make the first column field a simple number field, but use a script to create each new budgetMod record which also uses a set field step to copy the calcualted budget amount from BudgetModsByItem. Either way works and another person may read this post and suggest a different method...