Fast Summaries

Have you ever wished you could pull the values from a summary report, and use them in a script or a calculation? Back in the FileMaker 6 days, Mikhail Edoshin introduced a technique to do just that.

He called the technique Fast Summaries, and I came to employ it extensively in my own solutions. Eventually I built a demo file, kf-fast-summary, showcasing one application of this technique, and taking advantage of some of FileMaker’s newer capabilities. I feel that Fast Summaries deserve wider recognition, and my next article will feature them as one component of a somewhat complex procedure, so this seems a good time to dust off the old demo, and examine the technique in some detail.

The demo consists of a simple flat file showing donors and donations.

So, how do we get from that, to this?

The basic idea is to loop through the found set, using the GetSummary function to grab summary info (in this case, donation count and dollar amount) for each individual. We don’t need to visit every record, just the first one for each unique donor. This ability to skip lightly through the found set is part of what makes the technique fast.

So how do we identify the first record for each donor?

Given that the first record in the found set is guaranteed to be a valid starting point, a better question would be, “How do I decide what record to go to next?”

Deciding where to go next is relatively simple, assuming that a) your found set is sorted by donor, and b) you’re sitting on the first record for a given donor. If both these assumptions are true, you simply add the current record number to the count of donations for the current donor. For example, if you are sitting on record #1, and there are six donations for that donor, then the next record you will be visiting is record #7.

How do you count the records for the current donor? First you define a summary field to count the records, like so:

(You need to count a field that is guaranteed to contain a value for every record; the primary key is generally a safe bet. A calculated constant field would be another.)

Once you have a summary count field, you can use the GetSummary function to determine the number of records in the current group, as per the first Set Variable step below (this is a portion of the “Process Records” script).

Another thing that makes this technique blazingly fast is the use of variables. There is only one Set Field step, and that happens at the very end, after all the summary data has been assembled in the $report variable.

A third thing that makes this technique fast is a Freeze Window step, which is at the very beginning of the Process Records script. In some cases temporarily switching to form view while a loop runs can yield a major performance boost as well, but I did not find that to be the case in this particular demo.

Once you have this technique under your belt, you will find many applications for it, so I encourage you to spend some time with the demo file. You’ll be glad you did.

9 thoughts on “Fast Summaries

  1. Ron Hoagland

    Kevin, First I want to thank you for all your hard work on this site. It has been SO helpful and educational, please keep up the good work.

    I know this is an old demo but I have found it very useful in putting together email reports. What I am needing to know is how you were able to get the report field to format in columns. Even if I duplicate the report field and use a second SetField step with the exact same calculation as you use in the first SetField it does not format the same.

    Any help would be greatly appreciated.
    Thanks,
    Ron H.

    Reply
    1. Kevin Frank Post author

      Hi Ron, thanks for the kind words. If you click on the Report field in layout mode, you can assign tab stops… depending on which version of FileMaker you are using, this is done in slightly different ways, but in recent versions this would be done via the Inspector like so:

      Tab Stops

      Hope this helps,
      Kevin

      Reply
  2. Ron Hoagland

    Thank you very much for the quick response. I completely forgot about the Tabs section in inspector. Its one of those little gems that Filemaker has that just doesn’t get used much for me, it will now.

    Thank you for the help.

    -Ron

    Reply
  3. Adam Glick

    Kevin, I’ve been meaning to test this method out at some point and today it saved the bacon when a client threw me back to the drawing board after two builds of a complex interface they needed to aggregate and summarize data wasn’t going to work the way they were hoping. So, I threw it out and built a new one from scratch around this way. Super slick. THANK YOU!

    Reply
    1. Kevin Frank Post author

      Wonderful news, and of course credit goes to Mikhail Edoshin, the originator of the technique.

      I appreciate you taking the time to post that.

      Reply
  4. robert

    Hi Kevin,
    Thank you for maintaining your tips, solutions, hacks and especially the sample files.
    As old as the Fast Summaries technique may be, it is even relevant today.

    Do you have a suggestion on how the FS technique could be used to Group the [sample file] records into columns (like all the names starting with “ABC,DEF,GHI…”)?

    rl

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s