9 Replies Latest reply on May 13, 2012 6:28 PM by brooklyngeorge

    Job and Account budgets



      Job and Account budgets


      Hi, using FMP11 on Mac OS 10.5

      This may have been handled before in this forum, but wasn't sure what term to search for...

      I have a number of Jobs (codes like 11EX500, 11EX502, 11EX503 etc.) in a Jobs Table, and a number of Accounts (codes like 6-5440, 6-5445 etc) in an Accounts table, linked into a Purchases table.

      All well and good, but now I'd like to track the budgets for these various Job/Account combinations - each Job has a full list of Account codes, each Account has a budget amount assigned to it, but of course the budget amount differs for the same Account in a different Job.

      So a Budget table becomes obvious, with a record for each Job/Account/Amount combination, linked to the Jobs and Accounts tables, but then how do I interrogate that amount when processing a payment, reduce the remaining budget for that Job/Acoount line, and generate a report showing each transaction on a new line, summarised by Job then Account, with the original budget amount, line item amount and remaining budget. I'm OK with sub-summarising, but can't get a handle on how to calculate/display budget amounts.

      It's only bare bones at the moment, but as always, any help appreciated.


        • 1. Re: Job and Account budgets

          The BudgetAmount field should not be changed when you "process a payment" in your purchases table or update and existing such transaction. Instead, a calculation field should subtract the total of these values from the Budget amount and it can then be used to display the remaining balance for that budget.

          Add a new occurrence of Purchases and link it to budgets by Acc_no = account_code.

          Then either of these two expressions would calculate the budget balance if defined in the budget table:

          Amount - Sum ( Purchases 2::Amount )


          Amount - Purchses 2::sAmount

          In the second example, sAmount is a summary field defined to compute the total of Amount in the purchases table. Which version works best for you can depend on your layout design so you might want to experiment with both options to see which works better in your database.

          • 2. Re: Job and Account budgets

            Hi Phil, thanks, but not getting the desired result, what I'm seeking is a Report listing transactions, sorted by Job No., then Account No., with the budget for each Job/Acc combination displaying alongside. At the moment I'm getting only the first record in the budget table for each purchase record.

            I've edited original post with another screenshot

            • 3. Re: Job and Account budgets

              Due to a bug in the forum software. I may not see the new screen shot unless you 1) edit the post to delete the original. Save the post. 2) edit the post again to upload the new screen shot.

              Since I don't see the new occurrence that I suggested, I must assume that this is the older screen shot. Frown

              • 4. Re: Job and Account budgets

                Thanks Phil, I tried a few times to delete and replace the screenshot, but nothing happened - however I now find that the new screenshot is indeed there! Laughing

                • 5. Re: Job and Account budgets

                  I still only see one occurrence of Purchases. Frown

                  • 6. Re: Job and Account budgets

                    well this is way weird, because I see two ...... do you see a table with red text with budget figures? .... maybe yours is cached somehow.... ?

                    anyhoo, I'll try and upload the screen shot again..(and now i note that when replying, there's an opportunity to upload an image - is this new?)

                    • 7. Re: Job and Account budgets

                      Yes, it's newly added by ModMan. It's revealed a bug in the software. That's why you see the controls doubled, but I'll gladly put up with that bug to be able to upload more screen shots more easily to posts here.

                      Your report layout would be based on Purchases 2, not budgets and that way you'll see all the transactions. You can then add fields from budgets to your layout in order to display values from it. Often, these fields would go into a sub summary part so that they report a sub total in a sub heading for each group of transactions.

                      • 8. Re: Job and Account budgets

                        Thanks Phil - nearly got it sorted now, just some fine-tuning in the sub-summaries..

                        • 9. Re: Job and Account budgets

                          Hi Phil & symbister - sorry to be jumping in on the party so late, but I feel very fortunate to have found this thread on just the solution I've been working on this past weekend. Thank you!

                          I re-configured my tables to match symbister's revised structure as recommended by Phil and my report layout works! Reviewing the report however it appears that only Purchases with related Budget records are appearing in the report. This is problematic as I would like to use the report to show all costs for a specific job, whether or not all the accounts were budgeted (as there are hundreds of accounts).

                          I was thinking that an option could be when one creates a Purchases record, if there is not an associated Budget record for that job it would create one with a budget of $0 but I'm not sure the best way to implement.

                          Any insight greatly appreciated.