3 Replies Latest reply on Dec 21, 2013 8:33 AM by Jesse_Wright

    Summary of a summary of a summary...oh my

    Jesse_Wright

      Issue is that I pretty much need to run summary on a field that is calculating a GetSummary command.

       

      Simply put...it can't run a Summary on a Summary of a Summary, so I'm stumped at the moment for how to work around it.

       

       

       

      To try and help you visualize the issue (and maybe I'll see the solution writing it out )

       

      Calculation produces UtilityInvoice_TotalPrice : Summary of UtilityInvoice_TotalPrice creates Utilty_GrandTotal : GetSummary Audit::Utility_GrandTotal pulls the summary forward into a non-summary layout to create Utility_TotalCost

       

       

       

      Payments::Utility_TotalCost

      = GetSummary (Audit::Utility_GrandTotal)

       

      Audit::Utility_GrandTotal

      Summary = Total of UtilityInvoice_TotalPrice (running)


      Audit::Total of UtilityInvoice_TotalPrice

      = Calculation (Utility Price * Qty)

       


       

      So I was asked to add subsummary totals to a report for each sorted group. What I need to happen is perform a "Running Total" Summary on the GetSummary field: (Utility_TotalCost) and restart summary for each sorted group,

       

      but I can't perform a (Running Total when Sorted by "Utility") Summary on a "Get Summary" field. Problem is that the GetSummary field produces the numbers I need to sum for the running total in the report.

       

       


       

      So I think I'm going to have to think about how I can possibly break this down and try to evaluate it a different way. Not sure what that is yet...

       

      Wanted to post this out here for those way more savvy than I. Thank you for your help, hope it's not too convoluted.

       

      Best!

        • 1. Re: Summary of a summary of a summary...oh my
          erolst

          Actually it is convoluted/confusing, and a sampe file or an illustration may prove helpful …

           

          Just wanted to note that …

          Jesse_Wright wrote:

          but I can't perform a (Running Total when Sorted by "Utility") Summary on a "Get Summary" field.  Problem is that the GetSummary field produces the numbers I need to sum for the running total in the report.

           

          … you can use a “GetSummary” field in a summary – as far as the summary is concerned, that field is just a number field (albeit a calculated one).

           

          I'm just not sure if you meant you cannot use it in general, or that it doesn't make sense in your particular setup.

          1 of 1 people found this helpful
          • 2. Re: Summary of a summary of a summary...oh my
            LyndsayHowarth

            Jesse... you need to read the middle para I just wrote here about Sub-Summary Parts.

            https://fmdev.filemaker.com/thread/73825?tstart=0

            Better still research sub-summary parts in the FIleMaker Help Centre...

             

             

            Summary fields give you a count or total... and in this case you are making them running. Running totals are not always appropriate but obviously when they are used in a statement where credits and debits are applied and a balance needs to be shown... is when they need to be shown. To make running summaries working properly you have to show all records or have another mechism for calculating opening and closing balances for the set of records found.

             

            When sorted you can create calculation fields which use the GetSummary (FIELD NAME, BREAK-FIELD)  function. The BREAK-FIELD is the field by which you sort your data AND it is dependent on which data range you are showing.

             

            That GetSUmmary() field can be calculated. You might have

            GetSummary (InvoiceTotal, CustomerID) - GetSummary (PaymentTotal, CustomerID)

            to give you the amount owing.

             

            That field can then be summarised using a Summary field.

             

            - Hope that makes sense...

             

            - Lyndsay

            1 of 1 people found this helpful
            • 3. Re: Summary of a summary of a summary...oh my
              Jesse_Wright

              Thanks to you both, I'll make a demo file or a better drawing but I did a miserable job of trying to explain.

               

              My problem really just boils down to when I go to do a sub-summary, the calc field using GetSummary is greyed out.  So it won't allow a SubSummary of the GetSummary calculation field.

               

              If I bypass that calulation and run a sub-summary on the fields that the GetSummary calculation was totaling, it only shows the last value in the repot.  If the last record was $8,976, my summary total is $8,976.  So it feels like I need to sub-summary running total onthe totals from the GetSummary calc.  IF it would total up those numbers I would have the correct result, but alas I am unable to select the calc field when setting up the sub-summary.

               

              I'm sure this is no less confusing...which is why I'm confused

               

              I'll go put together a demo file