5 Replies Latest reply on Oct 29, 2014 3:12 PM by sccardais

    Trailing Grand Summary Problem

    sccardais

      Title

      Trailing Grand Summary Problem

      Post

      My sub summary report is based on a table "ORGs" that is related to itself using two match fields: Price Tier and Status.

      The report shows the count of records in a related table using a calculated field - "c_CountOrgsByPriceTier"

      The calculation for this field is Count(ORGs_self_tier::c_PriceTier) This calculation correctly displays the number of related records.(e.g 22 in Tier 0, 100 in Tier 1, etc.)

      I want to sum these values in a Trailing Grand Summary. I have a summary field that totals c_CountOrgsByPriceTier. I place this in a Trailing Grand Summary but the results are incorrect.

      I've looked at this every way I can think of but cannot understand why the total is not working.

      What might I be doing wrong?

      The attached screenshot shows how the relationship is defined and the structure of the Layout.

       

      Screenshot_of_FileMaker_Pro_Advanced_%2810-29-14%2C_8%3A20%3A49_AM%29.png

        • 1. Re: Trailing Grand Summary Problem
          philmodjunk

          Your summary field is totaling these calculation fields once for each record in orgs when you need to get a total that is once for each group of records as grouped by your sub summary layout part.

          • 2. Re: Trailing Grand Summary Problem
            sccardais

            Sorry but I don't understand your explanation. Are you saying my Summary field is based on the wrong calculation field or that I need to add a different sub summary part or ??

            I know I'm making this more difficult than necessary but I'm still not able to get this column of numbers to Sum.

            The layout is based on ORGs. The leading sub summary part is sorted on c_PriceTier in ORGs.

            The first column is ORGs:: c_PriceTier.

            The 2nd column is c_CountOrgsByTier. This calculation counts the related records in a self join that matches based on c_PriceTier AND Status. It evaluates the calculation from the context of the self join.

            The values in the 2nd column are correct. To get a summary that totals c_CountOrgsByTier, I thought a summary field that totals c_CountOrgsByTier would work but it isn't.

             

            • 3. Re: Trailing Grand Summary Problem
              philmodjunk

              Sorry but I don't understand your explanation. Are you saying my Summary field is based on the wrong calculation field or that I need to add a different sub summary part or ??

              Say you have this data:

              Apple            4
              Apple            4
              Orange          5
              Orange          5
              Orange          5

              Where the fruit name identifies the group and the total from related records is the number.

              You want a grand total of 5 + 4 = 9

              but your summary field is computing 4 + 4 + 5 + 5 + 5 = 23

              What you need is (4 + 4 ) / 2 + ( 5 + 5 + 5 ) / 3 = 9

              If you add a count of summary field to your table that counts your Org records, you can set up this calculation:

              GetSummary ( YourCurrentSummaryFieldHere ; BreakField ) / GetSummary ( CountingSummaryFieldHere ; BreakField )

              And then yet another summary field can compute the total of this calculation field to get your grand total from the related records. "BreakField" should be the field you are using as the "sorted by" field in your sub summary layout part.

              • 4. Re: Trailing Grand Summary Problem
                sccardais

                Thank you. I wasn't able to make the process you described above work but I did find another way to do it that I wanted to share.

                My layout is dynamic in the sense that the user can click a radio button set containing different values for Org "Status" (Client, Canceled, etc.) The radio button set is in a popover on the layout. The sub summary updates the Org count by Price Tiers based on the user's choice of "Status".

                It dawned on me that the total I wanted must always equal the found count when searching on Status so, once the status has been selected,  a script that uses the same Status to finds all matching records in ORGs, and sets a $$ variable with the found count. The $$ variable is displayed in a Trailing Grand Summary part.

                A script trigger connected to the Status radio button field in the Popover exectutes the script whenever the Status field on the layout is modified.

                It works and it's fast. As usual, thanks again for all of your help on this.

                • 5. Re: Trailing Grand Summary Problem
                  sccardais

                  Phil -- I forgot to mention that I learned something from your Apples and Oranges explanation. I would NEVER have made that connection.

                  Based on your reply, I created a list summary field based on c_CountOrgsByPriceTier and suddenly it became very clear. This field showed that each record with a Status of x contained hundreds of return separated values - each value being the count of matching records. This was an "aha" moment.

                  That led me to the final solution that I described above.

                  The hardest part of learning FileMaker - for me anyway - is understanding why something works or doesn't. Your Apples and Organges example above helped.