5 Replies Latest reply on Dec 4, 2013 6:46 AM by beverly

    Filtering Sum values according to relationship

    ithink

      Hi guys,

       

      I don't know whether my post title is representative of what I am trying to accomplish. But here goes.

       

      I have a BUDGET TABLE and a O/M month/year relationship with an EXPENSE TABLE.

       

      In the EXPENSE TABLE I have a text field (PURCHASE LABEL) with a self defined value list. Example: Office, Fuel, etc.. I also have a corresponding number field (AMOUNT SPENT) representing the amount spent on one of the different (PURCHASE LABEL) values. I have a corresponding text field (PURCHASE LABEL) in the BUDGET TABLE. The (PURCHASE LABEL) values in the BUDGET TABLE are pulled from the (PURCHASE LABEL) values created in the EXPENSE TABLE.

       

      I created a sum calculation in the EXPENSE TABLE to sum the values of the (AMOUNT SPENT) field. What I'm having a hard time with is summing the (AMOUNT SPENT) based upon the unique (PURCHASE LABEL) value and then filtering it down based upon the TRANSACTION DATE field.

       

      In other words if I made 2 purchases for 200.00 and 300.00 respectively under the (PURCHASE LABEL) Office and 3 purchases for 60.00, 70.00 and 80.00 respectively under the (PURCHASE LABEL) Fuel, I only want to sum the 2 Office purchase amounts (500.00) and sum the 3 Fuel purchase amounts (210.00) within the NOVEMBER 2013 date range. Those amounts would be brought over into the budget table and into the NOVEMBER 2013 budget record and depending on which Budget (PURCHASE LABEL) value is selected, the corresponding summed amounts (500.00 for Office) or (120.00 for Fuel) would be subtracted from the corresponding NOVEMBER 2013 (BUDGET AMOUNT). The result would be the remaing budget amount that could be spent for OFFICE items or FUEL within the NOVEMBER 2013 date range.

       

      I tried different portal filtering and calculations but I still keep getting weird totals in the sum (AMOUNT SPENT) field that don't make any sense or the value doesn't change when going from one BUDGET record month to the next. The BUDGET AMOUNT - sum (AMOUNT SPENT) result seems to work. The label relationship seems to work as well in that there is information loading into the sum (AMOUNT SPENT) field when the BUDGET LABEL = the EXPENSE LABEL. The numbers are wrong in the SUM (AMOUNT SPENT) field.

       

      Thanks for any direction or ideas.

        • 1. Re: Filtering Sum values according to relationship
          erolst

          While it's easy to filter a portal and also relatively easy to show statistics from a filtered portal, it's less straightforward to get at the numbers in a calculation. That’s because the portal filter is for display, but calculations working with related records use the underlying relationship, disregarding any portal filters.

           

          What you can do is create a summary field in Expenses (not a calculation field in Budget!) as Sum ( amount ), then on the Budget layout put the related field into a one-row portal with the same filter as your other portal(s), give the field an object name, and query its value via GetLayoutObjectAttribute ( objectName ; “value” ); this would be your total amount spent for the label(s) used as portal filter.

           

          That said, it may be better to amend your overall structure a bit, which would give you a fair amount of reporting and filtering flexibility and does not rely on layout objects. This requires a fair amount of scripting, so you may not be interested. If you are, let me know.

          • 2. Re: Filtering Sum values according to relationship
            ithink

            Thank you for your response.

             

            I'm not quite sure what you mean when you refer to querying the value of the Expense (SUMMARY AMOUNT) field. Does this mean that a script has to be run to get a result? I need this to update on the fly or in other words any time that a record is accessed or the (BUDGET AMOUNT) is modifyed.

             

            My initial setup was a summary field in the EXPENSE TABLE which summed the (AMOUNT SPENT).

             

            Anyway, I put the Expense (SUMMARY AMOUNT) field in a single portal row of the table occurence - Expense (MONTH YEAR) which I had hoped would filter the data by DATE by syncing with the current Budget (MONTH YEAR) record.

             

            The portal filter was: Expense (PURCHASE LABEL) = Budget (PURCHASE LABEL) which I thought would filter the data by syncing labels.

             

            I don't know whether it is relevant but technically I have it broken down into a sort of split ticket with 4 separate Expense (AMOUNT SPENT) fields which correspond with 4 Expense (PURCHASE LABEL) fields. But I figured if I can get the one functioning properly I can apply the same logic to the rest. I vacillate on whether that line of thinking is correct but I think it is.

             

            In any case each Expense (PURCHASE LABEL) field would be limited to 7 values. One of those 7 values can then be selected to define 7 budget items in the corresponding Budget record which should trigger the correct calculation amount per budget item.

             

            In other words the portal filter calc would change slightly for each related Budget Label. Example: Budget label field 1 - Expense (PURCHASE LABEL1) = Budget (PURCHASE LABEL1). Budget label field 2 - Expense (PURCHASE LABEL1) = Budget (PURCHASE LABEL2) and so on up to 7 label fields.

             

            But under the aforementioned setup I can get both the correct Expense (AMOUNT SUMMED) and the correct (AMOUNT REMAINING) result on the first record but not on the second record.

             

            How difficult was your second suggested option?

             

             

            Thanks

            • 3. Re: Filtering Sum values according to relationship
              erolst

              See if this gives you some ideas. It uses a dashboard and globals, but is basically the same as using a BudgetMonth record. Note also that it has a dedicated Category (aka Labels) tables and uses IDs internally, instead of text.

              • 4. Re: Filtering Sum values according to relationship
                ithink

                Thank you for your help and suggestions. I guess I should have mentioned that my skill level is not as high as I would like so with some of your suggestions I would need a little more in depth explanation.

                 

                Nevertheless, since I had already established layout design and other relationships I felt like to avoid confusion in my mind I should probably stay with what I had. I went ahead and just created a bunch of table occurances according to the relationships that you suggested, and got rid of all the single portals and filtering. It made for an ugly relationship graph but the time effort was probaly less than creating all those single portals with filters and it works like a charm.

                 

                Thanks so much for all your help!

                • 5. Re: Filtering Sum values according to relationship
                  beverly

                  ithink, this article is another way you can do this. If you are filtering a portal, make a copy of it - one row only - and put your summary fields (from the child table) in there. VOILA! your sum values match the filter-portal list.

                   

                  <http://www.filemakerhacks.com/?p=5950>

                   

                  Beverly