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.
Thanks Kevin for another thought-provoking Filemaker tip. Oh, and I beat you to the support group by days, if not weeks!
I instituted one of your previous sorting techniques on a list of medical billing codes, and the result was…disaster. I have not had the time to correct, but the billing codes range anywhere from 0 to 4 decimal places, and the left padding of “0”s elevated many of the codes. Other than that, perfect.
Thanks again.
PS-the fix I am considering is using a variable number depending upon the length of the code and position of decimal point.
-John
Hi John,
Thanks for taking the time to comment. I’m guessing you’re referring to part 2 in this series, and will proceed on that assumption.
The issue is that for this technique to work, we need to know in advance what the maximum number of decimal places will be, and then multiply by a large enough multiple of ten to eliminate the decimals entirely. In the example used in part 2, that multiplier is 100, because I was only anticipating a maximum of two decimal places (to accommodate US currency). Since your maximum number of decimal places is four, multiplying by 10000 should fix the problem, e.g.,
Right ( "0000000000000000" & 10000 * GetAsNumber ( ( GetField ( $$fieldName ) ) ) ; 16 )
Incidentally, a limitation of the above code is that it will only accommodate numbers as large as 999,999,999,999.9999 — beyond that the number of leading zeros, and the second argument of the Right() function will need to be adjusted accordingly.
Also, part 2’s technique as well as Ugo’s technique in this article, break down utterly when it comes to negative numbers. See my recent article, Easy Sorting of List Views, part 2, for some solutions, which can be applied to portal sorting as well.
Regards,
Kevin
Hi,
I am not a big fan of this way of sorting. Not anymore.
Soon after FileMaker Pro’s portals could be sorted on a field, I don’t remember which version it was, 5 or 6, nor how long ago, I thought of the possibility to have a clever unstored calculation as the sort field, and we implemented it. Most of our portals were sortable on 10 columns or more, up and down. It works good as long as the number of rows is not too big. And too big starts at around 50. A lot of people were annoyed each time they had to wait for 200 or more rows to be sorted. So when tabs came, some years ago, I designed a system with zero width tabs, a tab for every sorting order and an efficient script for tab-switching. For up and down sorting the number of tabs doubles the number of columns, and the lay-out of invisible tabs which are barely bigger than the portals is tricky. But the sorting is far, far faster. For the last years we and our clients are happy with this system, far happier than with the unstored calculation sorting.
Hi Martin,
There must be something else going on with your system. I just tested and it takes me less than 2 seconds to sort 3000+ records using this method on a LAN. Are your users accessing via WAN?
Or perhaps your sorter calc wasn’t as efficient as it could have been? (Not saying it wasn’t, just trying to think of a possible explanation.) Or there are unstored calcs or summary fields recalculating when you sort. Generally speaking I would expect to not find more than a few hundred records in a portal — in my own designs, at any rate — and this technique works quite well… none of my users have complained at any rate.
Regards,
Kevin
Well, you’re probably right. In FileMaker 5 and 6 only one sort field was allowed per portal. So we used one unstored calculation for each record. A global was set to indicate the sort order, and then for each record a specific sort value was calculated.