Easy Sorting of List Views, part 3

Update 22 Jan 2013: Demo file and screen shots have been revised to fix bugs identified by Matt Ayres and David Schwartz (see comments at the end of article).

Ever since I posted part 2 of this series, I’ve been torn between, on the one hand, wanting to move on to other topics, and on the other, the realization that I wasn’t quite done with this one yet. So, here is what I expect will be my final posting, and final demo (dynamic list sorting, v3 rev5), on this subject.

Thus far, we’ve looked at various methods to facilitate dynamic list sorting (by “dynamic” I mean that the field to be sorted is determined programatically). Most of these methods use two fields — one of them uses four — and you can see them all in part 2.

But in the back of my mind has been the knowledge that Ugo Di Luca pulled this off with a single field back in 2004 (EasySort.fp7, shared by permission of the author, and previously discussed last April in an article entitled Portal Sorting, pt 3).

Well, it turns out that Ugo’s demo suffers from the same shortcoming that caused me to write part 2 in this series: it doesn’t sort negative numbers correctly. For that matter it doesn’t sort currency values correctly either, but these are minor blemishes on a brilliant demo that the author no doubt could have easily remedied had he so chosen.

How much work would it be to get my demo to sort using a single sorter field? The answer turns out to be, not much work at all. “Method B” in my previous posting used substitution with numeric values, but if want to have just a single sorter field, I’m going to have to extend the substitution approach to text values as well. Or borrow code from someone who already has.

As I wrote back in April…

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… 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…

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.

So, if I lift the highlighted portion from Ugo’s sorter calc…

…and insert it into a revised version of my sorter calc…

UPDATE 9 May 2021: I recently identified an “edge case” problem, which you can avoid by changing the f declaration in the above calculation to

For more information see Bypassing Scientific Notation.

…and revise my “Sort Records” step to reference a single field…

All will be right with world, and maybe, just maybe, I can finally lay this topic to rest.

---

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 this example the multiplier is 100, because I am only anticipating a maximum of two decimal places (to accommodate US currency). But if your maximum number of decimal places is, say, four, multiplying by 10000 will fix the problem, e.g.,

Right (
"0000000000000000" & 10000 * GetAsNumber ( ( GetField ( $$fieldName ) ) ) ; 16
)

And in the interest of fully disclosing all potential problems (including those unlikely to occur in the real world): 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.

If you either don’t know how many decimal places your numbers will go to, or simply don’t want the hassle of worrying about this, I recommend you play it safe and use Method C from part 2 (or some variation thereof) instead.