3 Replies Latest reply on Sep 16, 2011 8:39 AM by philmodjunk

    Keeping Track of Budget Changes



      Keeping Track of Budget Changes


      I am trying to figure out a good way to go about tracking budget changes.  The company I work for has multiple programs to track.  Each program will usually have the budget adjusted a few times before the program is over.  We track these as modifications.  

      What I would like to do is have a layout that will have three columns with several different elements.  (See attached for current layout of Budget Modification sheet).  I want to sort by program number, then modification number.  When I create a new record I would like to pull, from the total column of the most recent modification and insert into the Budget column of the new record.  Is this possible or is there a multi-table/layout solutiton?  Any is appreciated as always.


        • 1. Re: Keeping Track of Budget Changes

          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 ?

          • 2. Re: Keeping Track of Budget Changes

            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!

            • 3. Re: Keeping Track of Budget Changes

              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...