Summary List As Relational Predicate, part 2

Disclaimer: This article features an undocumented technique. As with all material on this site, use at your own risk and test thoroughly.

8 Feb 2015: Demo updated to include a “refresh window / flush cached join results” step before GTRRing across a summary list relationship

Last week we looked at a technique to allow a summary field to be used as a relational predicate, directly, i.e., without the need to use a “helper field” as a predicate. If you’re not comfortable employing an undocumented hack in your solutions, I encourage you to read on anyway, because what you’re about to see may still be worth doing, whether you use last week’s method, or the safer (but a bit slower) “helper field” approach as delineated here on FileMaker Hacks last year in Summary List Fields in FM 13, part 1.

We’ve established that a summary list field can be used as a relational predicate (either directly or indirectly); the aim of today’s article and demo file, FM-13-Summary-List-As-Predicate, is to offer a compelling reason to do so.

2-2-2015 11-31-55 AM

In this demo, you can compare two ways to jump from a given found set of Web Visit records to the corresponding found set of related State records:

1) via “Go To Related Record” for found set (a.k.a. “GTRRFS) using a standard relationship

2-2-2015 11-56-24 AM

2) via “Go To Related Record” for the current record using a summary list field (see last week’s article for how to define this relationship)

2-2-2015 12-09-28 PM

You’ll also note that the demo contains three buttons to control found set size, as follows:

  • If you’re testing on a WAN, use “visits >= 25K”
  • If you’re testing on a LAN, use “visits >= 10K”
  • If you’re testing locally, use “All”

2-2-2015 12-24-30 PM

Once you’ve generated the proper found set, compare what happens when you click the “Standard GTRRFS” vs. the “Summary List GTRR” buttons. The difference is not subtle is it? Here are the timings I’m seeing:

  • WAN: 10 seconds vs. <1 second
  • LAN: 13 seconds vs. <1 second
  • Local: 5 seconds vs. <1 second

Note: to avoid possibly seeing skewed results due to caching, you should exit and restart the demo between each test.

7 thoughts on “Summary List As Relational Predicate, part 2

  1. Kevin Frank Post author

    P.S. Recently, by applying this technique on a WAN-based system, I was able to bring the time required to generate a report down from 35 seconds to 3 seconds.

    Reply
  2. Mardi Kennedy

    It would be good if someone else could test this in a different environment, but in my own, I’ve been using this technique with a slight twist.

    Whenever I need it, I capture the ListOf results in a variable and then set this data to a global field. The ‘found set’ relationship is then based on the global field (rather than directly on a ‘thingamyjigged’ Summary field). The script can be either used as a trigger, or invoked by some other UX-relevant action.

    As far as I can tell (limited testing), the performance is about the same as the above technique, and it’s a bit more ‘documented’ so hopefully relatively stable.

    Reply
    1. Jerry Salem

      Mardi

      I also am using this technique using a global field. I like that it is not dependent on a hack.
      In my experience, GTRR with a summary field is about 5 times faster than regular GTRR.

      Using GTRR and a global field is the same speed as GTRR and GTRR with a summary field.

      Jerry

      Reply
  3. Kevin Frank Post author

    Mardi and Jerry,

    I appreciate your comments, and have long used the method that you mention. It has worked since the FM 3 days, is rock solid, and as Jerry points out appears to be just as fast as using a summary field directly as a predicate.

    Kevin

    Reply
  4. Daniel Wood (@FMweetbicks)

    Very cool! I would love to know just what is going on under the hood here. in the GTRRFS you are doing a projection from a large number of records to a smaller set, and in the second you are doing from a single record multi-key to the found set. There must be a fundamental difference in the way FileMakers query engine is carrying out these separate requests. The first I would assume has to evaluate each individual record for a GTRR and then intersect the results. The second may just be doing a single request? It’s all speculation but there is definitely a clear difference here, and a very useful one to have in the ol’ noggin, thanks !

    Reply
    1. Kevin Frank Post author

      As per Mardi and Jerry’s comments, I am now inclining towards simply using the tried and true “populate a global multiline key + GTRR” method.

      I’ll probably throw together another demo showing that approach, which has worked for nearly 20 years. What clinched it for me was the realization that I need to include a “refresh window / flush cached join results” step before GTRRing across the summary list relationship for the technique to reliably work. It wasn’t obvious at first, but your fellow countryman Rob Russell was kind enough to draw my attention to this unfortunate fact, and repeated testing confirmed he was correct.

      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