FileMaker Portal Sorting Redux

I got some great responses to my last article, and I’d like to share a few of them with you (with permission from the developers).

First up, from Chris Manton of BabelFix: rather than gathering the child IDs with a script, he set up a separate sorted relationship for each sort order. This lets you use a simple List function to instantly grab the list of IDs.

Great idea, but it turns out, testing with thousands of related records, that the slow part is not gathering IDs, but the portal’s Sort that introduces the bigger delay. (And remember, the field we’re sorting on is an unstored calculation.) Yes, the List function is king of speed, but you won’t notice the difference in portal sorting unless your related record set is in the hundreds or thousands.

Chris, undaunted, figured out a way to take the portal’s Sort out of the picture! He used the virtual list technique that Bruce Robertson developed. The portal displays the “virtual” records, which are already sorted, so the portal doesn’t need to do it.

If you sort thousands of records in portals, this is a killer technique. It’s really two techniques: 1. gathering the IDs using sorted relationships and the List function; and 2. a non-sorted portal that uses virtual records. Part 1 is useful, but does require an extra relationship in your graph for each sort order. It’s part 2 that’s the real breakthrough in terms of speed for large record sets.

Our next example is from Bruce Robertson of Concise Design, who pioneered the use of virtual lists, and naturally that’s the meat of his implementation. What’s interesting is that his approach is kind of the inverse of Chris’s: he uses sorted relationships, but on the virtual records. He also demonstrates a really clever way of grabbing the data by evaluating a script parameter.

Last but not least is a file modified by Matt Petrowsky of FilemakerMagazine.com. What you notice immediately is how great it looks. Subtle shading in the portal header, reverse shading on the header’s separator lines, and neat little sort direction arrows. Even the font sizes are just so.

Behind the scenes, he kept essentially the same simple relationship graph, with one important change: an additional “refresh” criteria in the relationship. You can read more about how that works at FileMaker Weetbicks. Essentially, it handles refreshing the portal without the screen flashing that comes with a refresh script step.

Matt also adds ascending/descending sorts, and uses some interesting custom functions. He uses one for the portalSort field. He uses another with GetNthRecord to create the child ID list. (As I mentioned in the comments of the previous article, GetNthRecord is not a problem for small sets of related records.)

Ultimately there is no single “right” way to sort a portal in FileMaker. The technique you choose depends on where it will be applied. For now I’ll be sticking with my original approach, it doesn’t require any changes to my graph (although I’m going to play around with the relationship-based refresh) and the setup is straightforward enough that other developers I work with should be able to make use of it without much trouble.

Many thanks to Chris, Bruce, and Matt, and everyone who gave feedback on Portal Sorting that Doesn’t Suck. Have fun digging into these files!

PortalSort Manton.zip (260 KB, contains large sample data)
PortalSort Petrowsky.zip (24 KB)
PortalSort Robertson.zip (16 KB)

Tags:

This entry was posted on May 25th, 2011 and is filed under FileMaker. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.