9 Replies Latest reply on May 22, 2012 2:58 PM by GuyStevens

    Jobs and Account budgets - revisited

    brooklyngeorge

      Title

      Jobs and Account budgets - revisited

      Post

       

      Hi there -

      This is a followup question to "Job and Account budgets" (http://forums.filemaker.com/posts/b74524245e). I have set up my tables per the screenshot here.

      I then created a layout with leading Sub-summary by Projects_ID with just Costs 2:Projects_ID, followed by a Sub-summary by Costs 2:ChartOfAccounts_ID, followed by a Body with Costs 2:Costs_Date, Costs 2:Costs_Description, Costs 2:Costs_Source, Costs 2: Costs_Actual, and finally a trailing Sub-summary by Costs 2:ChartOfAccounts_ID with Costs 2:Costs_sActual, Budgets:Budgets_AccountAllocation and Budgets_cAllocationSpent.

      When I run the report (i.e. Preview mode) by sorting Costs 2:Projects_ID followed by Costs 2:ChartOfAccounts_ID however the Budgets:Budgets_AccountAllocation for each ChartOfAccounts_ID is the same across all Projects. Basically, no matter which project it is, it shows the Budgets:Budget_AccountAllocation from the first Budgets record that matches the ChartOfAccounts_ID. I can't figure if my Sub-summary set up is wrong, my Sort order is wrong, or if my relationships is wrong.

      Any help greatly appreciated!!!

      Picture_3.png

        • 1. Re: Jobs and Account budgets - revisited
          GuyStevens

          You are talking about all kinds if fields from a Costs table, But I don't see that table in your relationship graph.

          How is it related to the other table occurences?

          And what table is your report based on?

          Also in subsummary reports you often need a summary field that displays the total of an amount. And then you need to put that, either on the subsummary (leading) part or you cerate a subsummary (trailing) part (print below)

          Or if you want to see the grand total you put this field on a Trailing grand summary part.

          This demo file has subsummary parts in it. You might want to take a look:
          http://dl.dropbox.com/u/18099008/Demo_Files/BillsMonthly.fp7

          • 2. Re: Jobs and Account budgets - revisited
            brooklyngeorge

            Oh, geez, my apologies there. Thank you for the speedy reply. I had forgot that I had renamed the tables in that above example. Here's an accurate snapshot: http://imgur.com/IuiEo. As you can see, the Purchases tables are now Costs and Costs 2, and the Jobs table is now Projects. The report is based on the second occurence of Costs ("Costs 2") which is related to Budgets via ChartOfAccounts_ID.

            I have created a trailing Sub-summary part based on Costs 2:ChartOfAccounts_ID. It has three fields: Costs 2:Costs_sActual (which is Sum of Costs_Actual), Budgets:Budgets_AccountAllocation and Budgets_cAllocationSpent (which is Budgets:Budgets_AccountAllocation - Costs 2:Costs_sActual).

            Everything initially appeared to be working, but Budgets:Budgets_AccountAllocation is not working correctly. Instead of showing the budgeted amount for the related ChartOfAccount of a specific Project, it's showing the same budgeted amount for each ChartOfAccount (regardless of Project).

            Does that make more sense?

            • 3. Re: Jobs and Account budgets - revisited
              GuyStevens

              Does that make more sense?

              I wish it did  :(

              I have been looking at your relationship graph but I can't seem to be able to wrap my head around it.

              Isn't the problem however that you are trying to get related data from different records while being on one record.
              Maybe it would be better to add a calculation field in the Cost table that gets the AccountAllocation from the Budgets table.

              That way every records get's it's own related value trough the relationship.

              When you are on that one record in the costs 2 table you can only see one related record in the budgets table.

              Try changing records in the costs layout to see if that Account Allocation value changes.

               

              • 4. Re: Jobs and Account budgets - revisited
                brooklyngeorge

                Hello and thank you again. I have attached a snapshot of the desired layout in Preview mode. The report is sorted by Projects_ID followed by ChartOfAccounts_ID, so you can see all the costs grouped by type under each project. If you look at the Budget column, you will see that "Freelancer - production assistant" under PROJECT0001 has the same budgeted amount ($3,000) as "Freelancer - production assistant" under PROJECT0002. However, in the Budget table, PROJECT0002 has $1,200 budgeted for "Freelancer - production assistant." For some reason the report is showing the budgeted amount for "Freelancer - production assistant" for PROJECT0001 for "Freelancer - production assistant" for all projects.

                Isn't the problem however that you are trying to get related data from different records while being on one record ... When you are on that one record in the costs 2 table you can only see one related record in the budgets table.

                This is correct; however, I thought that using the Sub-summary part you could have different records appear for different groupings of data. Is this not the case?

                Try changing records in the costs layout to see if that Account Allocation value changes.

                Is this possible in Preview mode? My Budgets_AccountAllocation field is in the Sub-summary part (Trailing) so I only see it in Preview mode.

                • 5. Re: Jobs and Account budgets - revisited
                  GuyStevens

                  I'm not sure of this I haven't tested it. It was just an idea. It's something you need to try out.

                  It is not possible to change records in preview mode. But if you go to browse mode you should be able to change the record by using th pager icon.

                  Then you can go back into preview mode to see if the budget amount has changed.

                   

                  If that is the case then you need to make a calculation field in the Cost table. Like I said in my previous post:

                  Maybe it would be better to add a calculation field in the Cost table that gets the AccountAllocation from the Budgets table.

                  So a calculation field, call it c_budget or something.

                  That contains the following calculation:

                  Budgets:Budgets_AccountAllocation

                  Then on your report, change the budgets amount field from the budgets table with the field from the Costs2 table.

                  Now I haven't tested this, it's just an idea.

                  • 6. Re: Jobs and Account budgets - revisited
                    brooklyngeorge

                    ... if you go to browse mode you should be able to change the record by using the pager icon.

                    Ah, understood. I tried this now; I entered Browse mode, paged to the next record, then entered Preview mode and the Budgets_AccountAllocation figure remained the same for each cost type no matter which record I was on prior to re-entering Preview mode ("Freelancer - production assistant" = $3,000.00, "Camera - operator" = $5,000.00, "Travel - ATM fees" = $55.00).

                    So a calculation field, call it c_budget or something. That contains the following calculation: Budgets:Budgets_AccountAllocation. Then on your report, change the budgets amount field from the budgets table with the field from the Costs 2 table. Now I haven't tested this, it's just an idea.

                    Thank you - it's a fresh idea I had not thought of. Unfortunately, whether I inserted the new c_budget field from the Costs or Costs 2 table, it resulted in a blank field in both Preview and in Browse mode across all records.

                    Your feedback has been much appreciated. Based on the original thread from September 2011 I really thought this was the correct path, but now I'm not so sure.

                     

                    • 7. Re: Jobs and Account budgets - revisited
                      GuyStevens

                      If there was a way you could send me a copy of the file, maybe without any data I could have a proper look.

                      Like it is now I have no clue because I can't really imagine your database.

                      • 8. Re: Jobs and Account budgets - revisited
                        brooklyngeorge

                         

                        DaSaint - thank you so much for even taking a look. Please let me know if this doesn't work for you. It's FMP12. Hope that's okay:

                        https://www.dropbox.com/sh/ekxbrxejkgzbtb4/T67gWWI5sb

                        u: admin, pw: admin

                        • 9. Re: Jobs and Account budgets - revisited
                          GuyStevens

                          I have a question.

                          Your budget for project one has two entries for Freelance Production assistant.

                          Your cost table has two entries for Jane Killer.

                          But there is no link between the two.

                          So the cost has two records, but the budget can only show the total of the budget for this Chart Of AccountsId  ( 60055300040 )

                          You can't have a budget amount on every line like you have your costs on every line.

                          Basically the only information you have for a budget record, is what project it is for and what Chart of Accounts number it is in.

                          So therefore you can only display the total, because there is no relationship whatsoever between the cost records and the budget records.

                          This is not a problem, but means you can only show the total budget for every Account Number.

                           

                          What I did was I added a summary field that counts the total of the BudgetsAccountAllocation.

                          And I added that to the trailing sub summary part.

                          I Also added a relationship between Cost 2 and Budget so that Both the ProjectId and the Account Code are related.

                          And I changed the calculation of the Variance to use the Summary field of the budget in stead of the amount.

                          Anyway, my eyes are falling shut, so you better take a good look to see if it's correct. But from my end it's looking pretty neat  :)

                          http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/BrooklynGeorge/Budget_Test_DaSaint.fmp12