4 Replies Latest reply on Feb 16, 2015 8:10 AM by philmodjunk

    Get Summary on a second table instance

    Stu412

      Title

      Get Summary on a second table instance

      Post

      I'm trying to create a second set of data based loosely on some already fully functioning reports which have extensive use of GetSummary within them.  The first report is a list layout based solely on one table.

      When I create a second instance of the source table, create a new list layout and set break parts up based on the second instance table, the GetSummary is blank.  I have of course tried referring to the first and second instance of the tables to pull the GetSummary data.

      I've checked the sort order, it's the same as the break parts and the GetSummary is breaking on a sorted part.

      Any ideas why this is behaving this way?  Perhaps there's something I've missed,

        • 1. Re: Get Summary on a second table instance
          Stu412

          Painful

          I've just tested this on a clean dummy DB and it looks like that for a second table instance you need to create additional Get Summaries referring to it. Any advances!?

          • 2. Re: Get Summary on a second table instance
            philmodjunk

            The details of exactly what you are trying to set up for your report are a bit vague.

            GetSummary, as you have found out, only returns a value if:

            a) the summary and break fields are both defined in the same field as the calculation field where you've used getSummary

            b) the current found set is sorted on the specified break field.

            But this is not the only way to calculate a subtotal, particularly where relationships are set up that match to groups of records for which you want an aggregate value such as you might otherwise calculate with getSummary.

            • 3. Re: Get Summary on a second table instance
              Stu412

              Ok, what I'm trying to achieve is a bunch of summary and average figures placed onto customer layouts which themselves are based on the customer table.  The summaries and averages are based on a value table and the two are joined by the customer ID.

              I need to show a range of figures, summaries and averages for customers, counties, regions, countries and sliced by years.  All of these figures then need to be on the customer pages (most likely via portal).  I think the portals can filter by year.

              There are 6 countries and 90 odd counties.  From what I've seen so far (and I may be incorrect) in order to summarise by any sort of defined group, first define that group in formula 1 (for example, 'If Group=1, then value, else"") and then Summarise that itself in formula 2.  

              In order to use that aside from the source table, you need to use a GetSummary of the summarised data in formula 3.  Repear this process for Group 2, Group 3 etc.

              I feel certain this is completely wrong as it feels so inefficient!

               

              • 4. Re: Get Summary on a second table instance
                philmodjunk

                If you used the calculation fields you specify, the one thing you would not use is GetSummary. A summary field that summarizes such a calculation field would give you the sub total you need.

                But this is just one of several methods that might be used.

                If you are using FileMaker 12 or newer, you might try an ExecuteSQL calculation: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                And there are also ways to use a global field plus an intermediary table to produce subtotals after a script updates the global field (which is defined in the intermediary table to hold a key value such as a customer ID that is then used to limit what records currently match to the records of the intermediary table.)

                But it occurs to me that perhaps you simply need to base your layout on the table of summarized data rather than the customer table. This allows you to create a basic Creating Filemaker Pro summary reports--Tutorial of your data and you can perform a find on such a layout to limit your records to just those linked to a particular customer record.