6 Replies Latest reply on Jan 4, 2017 2:08 PM by philmodjunk

    Capturing summary data from records

    novenoga

      I currently run a script that creates a summary report. I then export the data and reimport to capture the summary numbers. Is there a way to do this in FMP? I would like to set global fields with the summary data. Appreciate any help with this.

        • 1. Re: Capturing summary data from records
          bigtom

          I have used ExecuteSQL with a SUM to store summarized data in global variables or fields. Works well And you can also run it with PSOS with a return result and usually faster that way.

          • 2. Re: Capturing summary data from records
            philmodjunk

            You can reference the value of summary fields just as you would any other field.

             

            The value that you get, however, depends on the context at the time you reference the field.

            If you refer to a summary field from your current layout's table occurrence, you get a value based on all the records in your found set.

             

            My guess is that you need a subtotal such as you can see in a subsummary part. To get that value use the getsummary function.

            • 3. Re: Capturing summary data from records
              bigtom

              If you need a simple summary and not multiple sub-summaries, Phil has a good suggestion.

              • 4. Re: Capturing summary data from records
                philmodjunk

                It works with multiple subsummaries as well. You just have to select the correct current record ( to get the right group) and the correct break field (the sorted by field specified in the sub summary part).

                 

                But this sound familiar to a process that I that I set up years ago for one of my clients. At the end of each day, I had a script generate a new record in a "summary table" where each record represented one type of material purchased that day, the total quantity and the total price paid for those items. This boiled down over a thousand records into less than 20 that I then used for reporting purposes without the delays I'd get reporting from the original data.

                 

                In my script, I didn't need getSummary. I had a "flag" field that the script set to a value each time that record was used to generate a record in the summary table. I thus used a looping script following this basic outline:

                 

                Loop

                   Find all records whose flag field indicated that they haven't been used to create a summary record

                   Exit loop if no records found

                   Find all records of the same material type and date as that of the current record

                   Create the summary record referencing summary fields that provide the needed totals.

                End Loop

                 

                Since I had a found set of one type of material at each iteration of the loop, I didn't need the getsummary function.

                • 5. Re: Capturing summary data from records
                  bigtom

                  Essentially the same type of loop I run with PSOS and ExSQL using a summary table. Same result different details in the process.

                   

                  ExSQL is less forgiving in some ways but for me it is nice to have a simple custom function grab summarized data as needed without a bunch of layout changes and sorts.

                  • 6. Re: Capturing summary data from records
                    philmodjunk

                    Those sound like good ideas, but they weren't options I could use back in the FMP 3 or 4 era when I first set up the script. Now it's a case of "ain't broke, don't need fixin'"