A common feature I like to provide on my FileMaker list view layouts is clickable headers that sort records based on that column. Realistically, you don’t always have the luxury of being in a simple 1 table list view. In this blog, I will walk through a consistent way to dynamically sort related data in portals, using the column headers.
I would like to preface this blog by saying this is not a concept I invented. It was taught the concept from a coworker – who learned it from a blog or forum. I still believe I have broken the concept down to its simplest form and can explain the parts and how they work in a way that will easily translate to any custom FileMaker solution.
Tables and calculations
For the sake of this blog, I have set up a very straightforward demo file, made up of only two tables: Person and Company. We will sort people records inside a portal on a company layout. Inside the portal sort dialog, you don’t have the ability to sort by related tables, so you must create calculations inside the Person table. Managing the data in those calculations will manage which field and which order that the portal will use for sorting.
Looking into these four calculations will require an understanding of JSON. Also be aware that there is only a 1 word difference between SortTextAscending and SortTextDescending and the same goes for SortNumericAscending and SortNumericDescending. By providing one of each you should be able to rebuild these calculations and make the fields specific to your data. Notice that SortTextAscending and SortTextDescending take only
The script
The main purpose of your script is to manage a single global variable that is referenced in the four sorting calculations, in this example $$PersonSort.
You will also need a simple custom function I use to point to field names rather than field values, called GetFieldNameOnly. We will use this in parameter passing and it will allow us to use the same script for every column used in sorting.
The global variable needed is a 2-key JSON object that will manage the Sort’s:
1. ”field” – The custom function points to field name only.
2. “order” – Either “Ascending” or “Descending”
The same script can be applied to every column header button. The only difference will be the parameter, which will be GetFieldName ( Table::FieldYouWantSorted ). You don’t need to specify Order in the parameter, the script will default to Ascending order, and automatically alternate on double clicks. This provides a professional look and feel that is sure to please users.
So essentially, the purpose of the script is to set the 2 keys of the JSON object and the purpose of the calculations is to read the JSON object.
Visuals
If you’re willing to go the extra mile, you can layer 3 different buttons with hide conditions. Buttons will need to be named and have script triggers to refresh since they are dependent on a $$GlobalVariable, but in my opinion the extra work is worth it. Sort order icons provide a totally professional look. The 3 different buttons needed will be:
1. An Up arrow for Ascending Order
2. A Down arrow for Descending Order
3. No arrow for when a different field is being sorted.
In my experience, users will either be super excited when you add a portal sort feature OR they have no reaction at all because they’re so accustomed to professional UI’s and websites that a feature like that is subconsciously expected. In either of those scenarios, it is a useful FileMaker development tool to have in your arsenal. It is a frequent situation and request, that I once struggled with until, I found some consistency in my approach. I hope this blog was helpful.