Portal Sorting, part 3

Today we’re going to look at a couple more approaches to dynamic sorting, from opposite ends of the complexity spectrum. The simple one, portal sorting, circa 2002, is something I built in the FM 5.5 era. It uses a “smoke and mirrors” approach to achieve its objective, and apart from converting to .fp7 format, and consolidating into a single file, I’ve left it as is.

Behind the scenes this file has eight relationships from the parent table to the child; all based on the same keys, but with different sort orders. There are eight layouts with eight seemingly identical portals, but each portal is attached to a different sorted relationship, and of course the sort indicator icon changes with each layout. Using this approach, you don’t even need a “sort” script; each button simply invokes a “go to layout” action.

If the triple repetition of the word “eight” in the previous paragraph sets off any alarm bells, good for you. On general principle, we want to avoid kludgy repetitive approaches to solving problems. However, we live in a world where we sometimes choose to disregard that principle, and as I said at the outset, this is a simple solution for this problem.

If we were to implement this approach from scratch in a modern version of FileMaker, we could use hidden tab panels instead of separate layouts, and we could apply the sort order directly to each portal, so we would only need a single unsorted relationship, rather than the eight sorted relationships that were required in the old days.

Obviously this approach doesn’t scale well, but it can be implemented without posing any particular mental challenges to the developer, whereas a more elegant approach would require more planning up front — at least the first time. Truthfully, when I built this demo, the biggest “challenge” was determining the proper placement for the engraved selected icon relative to its embossed neighbor, so the user wouldn’t see any incongruous shifting as they clicked from one icon to another.

Okay, we’ve seen the simple approach; what about the complex one? This comes courtesy of the author, Ugo Di Luca, one of the most original FileMaker thinkers I’ve had the privilege of learning from. His demo file, Easysort, dates from the FileMaker 7 era, and its name belies the elegance and sophistication of its construction.

Elegance in software design can be an elusive concept to define, but one criterion certainly would be no more “moving parts” than necessary, and by that measure Ugo’s approach is elegant indeed. [This demo file showcases dynamic list sorting, as opposed to portal sorting, but the fundamental issues are the same.]

Question: how many dedicated fields do you need for dynamic sorting? (See Portal Sorting, part 2 for background on this.) When the GetField function was introduced in FM 5.5, I thought the answer was four. I knew that dates and times could be represented as numbers (we didn’t have timestamps yet, but the same holds true for them), so I figured I could define four calculated fields: c_asc_text, c_desc_text, c_asc_number and c_desc_number, and then apply this sort order:

And as long as I ensured that only one of these four fields contained data at any given time, dynamic sorting worked as intended. Later (as per the previous article in this series), I realized it was possible to encode numeric data as text, so rather than four fields, two would suffice.

Well, my friends, Ugo has managed to do it in a single field, and the calculation for that field is a thing of beauty and wonder:

Ugo’s stroke of brilliance was to realize that you can fake a descending sort, when the actual sort order is ascending, if you invert the values behind the scenes; and he uses a separate inversion method for each field type.

I particularly like the way he transforms text characters, taking advantage of these FileMaker behaviors:

a) Substitute is case-sensitive

b) Sorts are case insensitive (provided you don’t monkey with the language setting for the field… if you change it to Unicode, then the sort most definitely will be case-sensitive)

So, having first cast all the text as upper-case, he then transforms A to z, B to y, C to x, and so on through to Z. If he hadn’t done this song-and-dance with case transformations, he would have encountered substitution collisions halfway through the alphabet, and the result would have been a big mess.

[Incidentally, it will not detract from Ugo’s noble achievement at all if I mention that I did see this clever substitution approach once before: Geoff Gerhard showed it to me at DevCon 2001 in Orlando, Florida.]

Well, there are any number of additional observations one could make, but it’s time to bring this article to a close: my support group for dealing with feelings of inadequacy and inferiority starts in a few minutes, and I don’t want to be late.