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.
To be clear, when this formula evaluates as true, the caret symbol will be hidden; otherwise the caret will be visible.
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”
Thanks for this technique. As I was reading it I remembered using something similar in version 5.5 or 6! I had completely forgotten about it! It was necessarily a bit more complicated and used a global field with 2 repeats instead of the variables!
I do like the simplicity of using text and conditional formatting for the indicators. Let’s hope a future version will eliminate the need for the 500pt trick :)
Thanks for the blog – always and interesting and thought provoking read.
PJ
I appreciate the kind words, Paul!
I agree about the “roots” of this technique going way back… I’ve been using some variation on it for at least 12 years, maybe longer.
Very nice! I’m going to give your technique a try. Don’t you normally provide a quick download of your example files? Didn’t see that within this article.
One caveat – I’d like for you to be clearer on what you consider “normal found set size”. I work with, as I’m sure you do as well, many different systems, and normal might be 50 records in one, and normal might be 8000 out of 200,000.
Thanks again for your contribution,
Alan Barker
Hi Alan,
a) there is a demo file in paragraph #5, reproduced here for your convenience: “Feel free to follow along in today’s demo file, dynamic-list-sorting, if you are so inclined.”
b) As the saying goes “your mileage may vary.” Personally, I would consider 200K to be a large found set. It also depends on whether your users are accessing the database via LAN or WAN.
Regards,
Kevin
Does this realy work in List view? Looks like Table view to me…
Hey Kevin,
great one again. ON rendering the text to be 500 pt. I have done a conditional format to just change the color to background when not selected rendering it invisible as well.
THis is a whole lot easier than some other ways i have seen as well as this can be placed into a custom function which will happen as soon as i am completed with this comment.
ONly ISSUE I have is with the fieldname annual_Sales
The other thing i may have an issue with is the Let statement;
Let (column = GetFieldName ( salespeople::annual_sales );
$$fieldName ≠ column OR
( $$fieldName = column AND $$sortDirection ≠ “desc”)
) // end let
** How can we not use this specific name “salespeople::annual_sales” for code portability and perhaps use another global name $$sort_field or something similar. Again, i am learning still & dont understand the shortcomings of FMP>
_thanks Kevin on doing this blog..
-ian
Ian,
Please see my recent Conditional Invisibility, part 1 posting re: a possible pitfall of using white text to make text “invisible”. Another more obvious pitfall that I didn’t mention: if your background color changes, you will have to redo your work.
With regards to your other concern… I don’t have the answer at the moment, but I have little doubt there is room for improvement. My goal is to preserve readability and I worry about techniques that are so abstract that only their creators can understand them… something I have probably been guilty of myself on more than one occasion.
— Kevin
Thanks for the conditional Invisibility issue. Haven t had the problem yet, but definitely see where it has potential to kick my butt in the future.
I will look into what you have in regards to code and see what i can do . Will post my findings if i have any! : )
-later for now..
-i
First of all many many thanks for the great work and your blog in general is full of great stuff – Especially for novices like me.
I use this sort example and it works really well when I have one file containing everything but just stops working when I move the solution to splitting out the data and the interface. Just stops working even though all the table occurrences are in the interface file as is the sort script.
Is there an obvious reason for this? (please forgive my ignorance I am just trying to learn Filemaker as has sort of been dumped on me for my job!)
Also, the database will eventually have a million or so records in it. Is this method of sorting problematic on found sets of that size in any event?
Anyway, again thanks for the good work and if I’m missing anything obvious – Would be very grateful for pointing in the right direction.
Rob
Hi Rob,
a) The method in this article doesn’t work with the “separation model” because the $$variables would no longer be in the same file as the calculated sorter field. You can use global fields instead of $$variables, and then the method will be separation-friendly.
b) As I said in the article and in comments above, “your mileage may vary” when it comes to deciding whether the sort speed is acceptable. I wouldn’t typically want my users to be sorting a million records using *any* method.
Regards,
Kevin
Kevin,
Many, many thanks for help. Will do that. Owe you a beer.
Rob
Hey Kevin, I am back @ this and i am wondering how / when / documentation on this particular comment .
“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.)”
– These are nuances i am learning about, but where would i find the answer as it has taken days to find this out?
thanks
-ian
Hi Ian,
I think it’s just a matter of sticking with it, and learning about the nuances of the product. Keep participating on the lists, and every day you will learn more about how things work in FileMaker. We were all beginners once. Before you know it, you will be the one offering advice to those with less experience than you have.
HTH,
Kevin
Hey Kevin, you are the bomb! Thanks sooooo much for this. It works like a dream and I am absolutely stoked. Thank you, Thank you, Thank you,
Hi Kevin this site is just fantastic, and the tips here are wonderful! One question – it all works wonderfully in IWP except for the cute indicators that use conditional formatting, as that is not supported by IWP. Any tips on how to get that same fab effect in IWP?
thanks Heaps!
karen
You could use a calculated container field displaying either an upward or downward pointing triangle.
Hello Kevin,
This is a great technique, and I’m wondering if it might be able to help with woeful sorting performance on a found set of around 3K records, over a WAN connection. The technique as you’ve described doesn’t do much for the performance, and I can see why, but I was wondering if you could imagine any way of leveraging the ‘perform script on server’ script step in FMP and FMS13 that might offload some of the heavy lifting to the local server, and improve things at the user end? Something like that would be incredibly useful in terms of increasing the viability of FMP over a WAN connection….
Hi James,
Agree that sorting of large found sets (as well as import/export) can be frustratingly slow on a WAN. I would expect this technique to be *slower* on a WAN than a standard sort, as per the second paragraph of the article.
My general rule of thumb is to design WAN-deployed systems to a) avoid these problems where possible, and b) where not possible, educate the users so their expectations are managed.
For what it’s worth, this article shows how one WAN performance problem (unfortunately not list sorting) was solved — http://filemakerhacks.com/2013/05/27/fast-grid-display-on-a-wan/
Have not found a way to fix slow list sorting on a WAN with Perform Script on Server; would love to know if anyone has.
Regards,
Kevin
Just gotta say thanks for this detailed tutorial and example file! I was struggling to find a way to sort columns in list view in both directions, and this took my UI to the next level.
FileMaker Hacks definitively merits the Nobel Prize for this and all the other fabulous lessons published in this blog!
Thanks a lot & Grazie 1000 from Milan/Italy
Hello Farid,
I appreciate you writing to say so, and I’m glad you’ve found the blog helpful.
Best wishes,
Kevin