Earlier this year, I posted a three-part series on Portal Sorting, and part 2 focused on dynamically sorting a portal when a column heading was clicked. Well, with just a few tweaks, this technique can be applied to dynamic sorting of found sets, and of course the most likely place to employ something like this would be on a list view.
I should note that on very large found sets, sorts using this technique can be noticeably slower than traditional “hard-coded” scripted sorts. (Performance is fine with normal found set sizes.) The benefit of using this technique, is that a new field can be added to a layout and sort-enabled in about 60 seconds without touching the script itself.
Self-plagiarism alert: rather than frequently refer the reader back to the earlier article, I’ve elected to re-use much of the text from that article here, so it can stand alone. And I have found an improvement to save your users some mouse clicks, so read on…
The basic idea behind dynamic list sorting is to define one or more calculated “sorter” fields, whose contents will change based on user actions… most often by clicking on a column heading. Typically the first click sorts a column in ascending order, and a second click on the same column heading sorts it in descending order, and an indicator of some sort appears adjacent to that particular column heading, pointing up or down as necessary.
Of course there are various ways to make this happen. My goal today is to strike a balance between cleverness and clarity, and also to take advantage of some of the features introduced in recent versions of FileMaker (hence the “version 10 or later” category for this article). Feel free to follow along in today’s demo file, dynamic-list-sorting, if you are so inclined.
Let’s start by looking at what happens when a user clicks a column heading.
All the column headings invoke the same script, “sort list”, but with a unique parameter consisting of the fully qualified field name (tableOccurrence::field). By utilizing the GetFieldName function, we ensure that a) the parameter won’t break if the referenced field is renamed, and b) that we’re passing a valid field name in the first place.
Here is the “sort list” script in full.
The main thing to notice at this point is that two global ($$) variables are declared: one for the sort direction (“asc” or “desc”), and one for the field name. These will be used by a pair of calculated sorter fields, and also by the sort indicators. Don’t worry about the $spType variable for the time being; I’ll explain it later in this article.
Here’s what the Sort Records step does…
…and now we see the calculated sorter fields I just mentioned. They will both start out empty (because the user has not yet clicked a column heading), but once a column heading has been clicked, one of the fields will contain data and the other will be empty.
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.
Here is the definition for sorter_asc, a calculated text field in the salespeople table (the calculation for sorter_desc is identical, except the first logical test is $$sortDirection <> “desc”, rather than “asc”).
Taking the Case statement line by line… 1) we only want the sorter field to display data if the sort direction is ascending, so if the sort direction is not ascending, show nothing; 2 ) if the field type of $$fieldName is text (calculated or plain), then simply display its contents; otherwise 3) assume the field type of $$fieldName is number, date, time, or timestamp (again, calculated or plain), and massage the contents into text that will sort properly.
Incidentally, the above calc ignores the possibility of container fields, which I think we can agree would typically not be good candidates for sorting.
Also, a few words about the FieldType function, buried innocuously in the above calc. It always returns four values, separated by spaces, and the second one is what the sorter calcs care about; specifically, is the value “Text” or not?
For example, when the user clicks Annual Sales, the $$fieldName variable is set to “salespeople::annual_sales” and
FieldType ( Get ( FileName ) ; $$fieldName )
returns
Standard Number Unindexed 1
The first three values are obvious; the fourth refers to the total number of repetitions defined for the field. FileMaker’s online help has a very informative entry on the FieldType function if you’d like to learn more.
So, to reiterate, the highlighted code parses the second word, so it can answer this crucial question: “Is this a text field: yes or no?”
If yes, then simply display the contents of the field; otherwise treat it as a number… even though it might be a date, time or timestamp, all of these will sort properly when treated as numbers, because they’ve been properly “massaged”.
Bearing in mind that our sort order is…
…here’s what happens behind the scenes when a user clicks the label for a text field (in this case, State). Since “State” is a text field, and since $$sortDirection = “asc”, the data appears in sorter_asc, like so:
And then when they click the same label a second time, sorter_asc is cleared and sorter_desc is populated; only one of them can contain a value, and the $$sortDirection variable determines which.
And this is desirable for text fields… the first click should sort ascending, and then if the user clicks the same label again, sort descending. But typically if a user clicks on a number or date field, it’s because they want to see the biggest number or the greatest date, which brings us to the $spType variable ( the one I said not to worry about when we looked at the “sort list” script).
When the $$sortDirection variable is set, it takes $spType into account, and if $spType is anything other than “Text”, the initial sort direction will be descending rather than ascending. Just think how many mouse clicks this will save your users… they will be eternally grateful and will shower you with lavish praise.
Since “date_of_birth” is a non-text field, and since $$sortDirection = “desc”, the data appears in sorter_desc, and has been zero-padded so that it will sort properly as text. And of course the second mouse click does what you would expect:
Incidentally, the “zero padding” takes place at both the beginning and the end of the data. The reason we use padding on the right side is to facilitate sorting of currency, and this is accomplished by multiplying by 100. The following is sorted by Annual Sales, and take a look at what’s going on in the first two rows.
A numeric decimal cannot end in zero… annual_sales in row one above is really 308691, and in row two, 315384.3 — trailing zeros on currency are just a formatting illusion. Tricks like this are necessary when one wishes to sort numbers in a text field. (In the demo if you remove the “* 100” from the sorter calcs, you’ll see that the Annual Sales field does not sort correctly — values as per rows one and two above will end up in the wrong position.)
What about the sort indicators? For many years I used triangular graphics (pointing up or down) inside calculated container fields, but lately I’ve been leaning towards a simple text-based caret symbol (^), and using conditional formatting to determine visibility or lack thereof.
Step 1: Place text objects containing carets next to each of your column labels. Format the text as bold and set the text color to red; also make sure the backgrounds are clear.
Step 2: Assign a conditional formatting formula to each of the five objects.
Since conditional formatting doesn’t have a “visible/invisible” setting, we start with all objects visible, and use a conditional formatting work around to hide the ones we don’t want to see (i.e., all but one of them). The conditional formatting formula assigned to each of the carets will be indentical, except for the field specification (highlighted below), which will need to be changed for each object.
Step 3: Click the More Formatting button and set a custom size of 500 points.
This work around has the effect of making the caret too large to display within the tiny confines of its text object, effectively rendering it invisible.
Step 4. Select all of the caret objects, duplicate them, rotate them 180 degrees (the Rotate command is under the Arrange menu), and position them above the originals, like so:
Step 5. For each object change the conditional formatting formula to refer to “desc” rather than “asc”, e.g.,
Step 6: And finally, slide the inverted carets down so they overlap their upright brethern like so:
And that’s all there is to it.
Self-plagiarism alert: rather than frequently refer the reader back to the earlier article, I’ve elected to re-use much of the text from that article here, so it can stand alone. And I have found an improvement to save your users some mouse clicks, so read on…
The basic idea behind dynamic list sorting is to define one or more calculated “sorter”