Easy Sorting of List Views, part 2

Well, I thought I’d said everything I had to say on this subject, but yesterday afternoon I noticed a glaring omission in part 1’s demo — what happens if the user manually unsorts the found set?

The sort indicator doesn’t disappear the way a good little sort indicator should. Fortunately this is easily remedied, thanks to the Get(SortState) function.

I always have to look Get(SortState) up in the online help, because it seems to me that 2 should be 1 and vice versa… but here it is in black and white (and a third color of indeterminate hue).

We need to tell the sort indicator to vanish if the sort state is anything other than 1, so we need to modify the conditional formatting for each sort indicator by adding the highlighted code.

Okay, problem solved, and with minimal effort. I was feeling pretty pleased with myself… for about 60 seconds, but then I got a call from a colleague, which began with a back-handed compliment, and went down hill from there.

“Hi, I was bored today, so was reading your blog.” [Was he trying to cure a bad case of insomnia?] “Are you aware that your list sorting technique doesn’t work on fields containing negative numbers?” No, as a matter of fact, I wasn’t. I’ve used this technique for many, many years and never once did it involve negative numbers, so I hadn’t given them a moment’s thought… but he was right, and the results were not pretty.

Off the top of my head, I can think of three ways to remedy this situation, and to cut to the chase, here are three demo files showing the three methods:

• Displace — dynamic list sorting, v2 method a
• Substitute — dynamic list sorting, v2 method b
• Add Fields — dynamic list sorting, v2 method c

First let’s review the calculation syntax of one of our original sorter fields:

And let’s also take a look at the original sort order:

…and bear in mind that in part 1 I said, “The two calculated sorter fields will never both contain data, which is why a single Sort Records step can serve for both ascending and descending sorts.”

Method A

Well, in Method A, we can disregard that, because the trick to Method A is to determine whether a given record’s value is a negative number, and if so, make it positive and display it in the “other” sorter field, like so:

If you’re curious, here’s the revised definition of sorter_asc for Method A; and the definition of sorter_desc is identical except the words “asc” and “desc” are swapped.

UPDATE 9 May 2021: For methods A and B, I recently identified an “edge case” problem, which you can avoid by changing the f declaration to

For more information see Bypassing Scientific Notation.

Method B

Method B uses substitution rather than displacement… Method A shifts a positive version of a negative number over to the “other” sorter field. In Method B, the digits 0 through 9 are transformed into letters, one set for negative numbers and a different set for positive numbers:

Here’s the definition of sorter_asc (sorter_desc is identical except the word “desc” is used in place of “asc”):

Method C

Method C is the simplest of all: rather than defining two calculated sorter fields, you define four of them and update the Sort Records step in your “sort list” script to take all four fields into account. As long as all four fields are present, the order is unimportant because only one of them will contain data at a time (It was only in Method A that we changed that rule).

With regards to the calculated field definitions, the “Let” portion is the same for all four…

…with the xxxxxxxxxxxxxxxxx portion varying by field in this manner:

• sorter_text_asc:  If ( a = “Text” and c = “asc” ; b )
• sorter_text_desc:  If ( a = “Text” and c = “desc” ; b )
• sorter_num_asc:  If ( a <> “Text” and c = “asc” ; b )
• sorter_num_desc:  If ( a <> “Text” and c = “desc” ; b )

…and of course on the two numeric fields, make sure the Calculation result is…

And I think that’s enough for today.