Sorting Columns in FileMaker
If you’re a user of a spreadsheet or any other program that displays information in tabular format, you’re probably familiar with a quick sort technique. You can usually click a column header and the data within the column will automatically sort in some way. You can sort of (pun intended) do this within a table view layout in FileMaker as well, but it’s not as simple as a click. You have to right-click on the header and navigate a Byzantine labyrinth of menu options before you get to what you hope is the expected result. Wouldn’t it be nice if you could provide the typical user experience to yourself and your users within your custom FileMaker app? Well, you can…by naming a few fields/objects on your layout, creating a global variable or two and some buttons that run a script. But we’re putting the cart before the horse; let’s start at the beginning.
The first thing we need to do is name all the fields (objects) on the layout that we’ll want to sort. These names don’t have to be fancy, but they should be unique and sensible to you. I’ll name my fields field1, field2, field3, etc.
Now that we’ve named our field objects, we’ll move on to making the script that performs the sort. Of course, we not only need to know which field we want to sort but the sort order as well, so we’ll be sure to include that in the code. The first part of the script handles the ascending or descending logic. It toggles between the two states, sorting in ascending order by default.
The second part of the script uses a script parameter. We’ll assign the parameter to a button that will trigger the script and tell it which column was clicked so that it knows which field to sort.
The final part is the actual sorting of the records based on the selected field/object. This is much simpler than you might expect. All you have to do is to not assign a particular field in the options portion of the Sort Records by Field script step and it automatically uses the object you just specified via the script parameter as the sort field. If we use some branching logic, like in the image below, we can easily handle the ascending and descending options.
We attach this script to each of the column headers in the list view and add some conditional formatting so that it’s obvious which field is sorted. And that’s it!
Now sorting columns in FileMaker custom apps can be done with a click, as users generally expect when viewing data tables. Learn more FileMaker tips and tricks like how to create calendars and export custom headers from FileMaker.
Karl Ermatinger
November 21, 2019 @ 11:43 am
The script in the article has an undesirable behavior of always alternating the sort direction even if the second click is on a different column header. You’d expect a click on a different column header to always default to ASC. Instead the direction may be DES if the previous sort was ASC. So if you click on one field header it sorts ASC, but then if you click on another field header it would sort to DES.
I chose to solve that by using global variables to track both the sort field ($$sf) and sort direction ($$sd) and then act based on that. See below.
If [ $$sf Get(ScriptParameter) or $$sd = “des” ]
set variable [$$sd; Value: “asc”]
set variable [ $$sf; Value: Get(ScriptParameter)]
Go to Object [Object Name: Get(ScriptParameter)]
Sort Records by Field [Ascending]
Else
set variable [$$sd; Value: “des”]
set variable [ $$sf; Value: Get(ScriptParameter)]
Go to Object [Object Name: Get(ScriptParameter)]
Sort Records by Field [Descending]
End If
Don Clark
November 26, 2019 @ 3:26 pm
Thanks, Karl, for figuring that out. Your feedback it greatly appreciated.
Karl Ermatinger
November 21, 2019 @ 11:45 am
Correction above: If [ $$sf Get(ScriptParameter) or $$sd = “des” ]
Karl Ermatinger
November 21, 2019 @ 11:48 am
This comment function wipes out and removes a less than and greater than symbols. The If is supposed to be “$$sf not equal to Get(ScriptParameter) or $$sd = “des””, but the comment function wipes that out.
Alex
November 22, 2019 @ 4:51 pm
Great tip!
Thank you Don
Don Clark
November 26, 2019 @ 3:25 pm
Thanks, Allessandro, but the thanks goes to the Support Group = they guest post here.