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)
Tom – I recently build an adaptation combining the VL techniques from Bruce and Daniel’s refresh techniques that Matt employed. In my case I needed 6 portals worth of data, but each portal has a differing found set built on the fly based on user interaction with the portals. Essentially a “parent” set of portals for selecting things, then displaying “child” portals below.
Combining the techniques allowed an insanely “fun” user interaction experience and instant UI responsiveness in a client / server environment without any lag and no extra overhead on the server as well.
The direction these techniques are taking is really allowing developers to build entirely new experiences never imagined possible in FileMaker. Too much fun! :)
Thanks for getting the word out as well as keeping the conversation and ideas flowing.
What a great compilation of related ideas! I am adding this to the list of MUST READ articles for my team…
Thanks, Tom!
Love the follow up ideas to this. Didn’t know about them until today, when a coaching client ran into issues implementing in his database based on the examples. I’d mentioned the original article.
The first problem was an easy fix, having to do with commented out calculations in the vList table when he copied to his solution.
The second one took a bit longer to figure out, as it worked for some fields and not for others. Finally discovered he had empty values in some of the fields that did not work, thus the List function resulted in a lesser number of values for the field than for the ID list.
Something to keep in mind if null values are common in the data set.
Thanks, Lisette. I’d have to go back and look at those commented out calculations, it’s been a while. Not sure what file your client started with — there are several example files they could have downloaded.
As far as the ID list, this must be in one of the variations of my original file. My version didn’t have a “vList” field, it only used an ID list.
If you have time to send along an updated version of whichever file had the issue, I’ll be glad to post an update. Thanks again. Glad to see people still getting use out of this technique!
Tom,
This was all in Bruce’s modified file with the vList table and relationships to handle the sorting. So, when the user copied tables over to his solution, and recreated those relationships, he didn’t realize any auto enter calculations referencing relationships necessarily created _after_ he imported the table, would be commented out because those TOs and relationships did not (and could not) exist when he imported the vList table.
So, they are correct in the sample file, but didn’t make the transition when he tried to migrate that technique into his file. I’ve pointed him to Todd Geist’s handy checklist for moving code, which clearly points this out. https://www.geistinteractive.com/checklist-moving-filemaker-code/
The other issue was that the technique using the List() function to gather the sorted field data through that relationship will have fewer values than the List(table::primaryID) when the field data includes null values. This throws off the PortalSort calculation, and the resulting sequence we were getting for one number field was such that it took a while to see the problem, especially since it worked just fine on the first few fields, because they didn’t have gaps in the data.
You can see this with the revised example file from Bruce if you make a few of the number fields empty.
We discussed having an auto entry of 0 for some of the number fields, which would have worked, but then realized he also had text and date fields where empty was a valid state. Too many fields to have a partner calculated version that resulted in some value for that field for this purpose.
In the end, I sent him back to your original file, with one small modification, to use ListOf to gather the IDs, which wasn’t around when you created the file.
As always with FileMaker, many options of how to achieve a result, and which you choose depends upon your data and solution requirements. In this case, the number of related records will top out at a hundred or so, and the sort won’t be slow, so your original method works fine and is easier for a junior developer to grok. But in a solution where the number of records is greater, and there are no null values, using List() and Virtual List certainly has advantages.
Had I known of these updated versions, I might have warned him as I had no intention of sending a coaching client into virtual list alone and unprepared. Love the technique and teach it a lot, but would have held his hand through it. At least now, we have the basic structure in place for when it will be helpful.
I send folks to your original article fairly often, as this technique rocks over replicating the portal with different sort orders, and the title of that post is so easy to remember!
Thanks, Lisette! Very helpful. On the plus side for your client, it was probably a great learning experience. I recently completed a Python course from MIT at edx.org. The degree of difficulty was such that it basically forced me to fail at first, and try, try again. The end result: learned much more than if I’d been able to get everything right the first time.
Tom, so true that we learn more by failing. The pain makes the “why” lesson stick better than when you just follow directions for how.
I think that then watching me piece together why it wasn’t working, when he did get stuck, was valuable as well. I didn’t hit upon the right answer at first, especially on the list issue with the number since those empty fields were further down the portal, and everything had worked with the text field. So, I checked more obvious possibilities, making sure I’d grabbed the right field to pass in the parameter, that it really was a number field and that we didn’t have an error in that relationship for sorting.
So often less experienced developers beat themselves up when they fail, and watching me go through a process to solve the problem, without hitting on the solution with my first few tries, does much more to alleviate that than me telling them about said process.
When I started with PHP I took a couple of classes from VTC, and the most helpful part of it was watching the instructor make mistakes and debug. Those blank screens when I’d done something wrong were maddening until then.