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.
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) 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)
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”
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.
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.
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.
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
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
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 !
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.
… and an undocumented technique is just an awesome technique too cool for documentation ;)