How To Sort Portals by Clicking on Column LabelsPosted: May 23, 2012
UPDATE – the technique described below is a good one, and works fine, but Kevin Frank has described a more elegant and portable way of doing it here. The concept is essentially the same, i.e. having the portal permanently sorted on the same two fields, but Kevin makes it much more straightforward. Note, though, that his solution relies on global variables, which won’t work in a separated solution – in this case, you need to use global fields instead. He explains this in his own post.
Anyway, here’s my original post, for posterity. But do it Kevin’s way!
This is a handy technique for doing quite elegant sorting on a variety of column headings. The behaviour is the standard “toggling”, i.e. click once to sort on that column in ascending order, click again to sort descending. The nice thing is that it only uses 2 sort fields, the contents of which are set by a simple script, which makes it very portable.
So the portal is permanently sorted on 2 fields, “ascending” and “descending”. The one you’re not sorting on always contains “1”, to ensure that it doesn’t appear to affect the sorting of the portal.
The other component is a global field to hold the current sorting selection. This is used for 2 things – i.e. toggling the preference (e.g. if it’s set to “ascending name order”, we’ll want to reverse that to “descending name order”, and vice versa), and driving the calculation on which the sorting is based.
Step 1 – Create “Current Sort Order” Global Field
In the appropriate “globals” table (INT in this example), add a field to contain the current sort order, e.g. INT_g_SortPref. It’s just a global text field – you could use a global variable, but not if you are using Data Separation (global variables don’t span files).
Step 2 – Create Sort Fields
In the source table (on which the portal is based), add 2 fields to define the sort order, ascending or descending, named (e.g.) PGM_i_SortAscending and PGM_i_SortDescending.
The fields must be:
- Always evaluate
- Result – Text
The calc should be something like this:
PGM_i_SortDescending will be similar, but with the field names associated with the descending settings, i.e.
This is what happens when the user clicks on a column heading:
- A script is performed, to set the global INT_g_SortPref field to the value passed to the script as a parameter. In this example, the parameter will be “PGM_Date_a”, or “PGM_Date_d”, etc.
- Because the calc of PGM_i_SortDescending and PGM_i_SortAscending depends on INT_g_SortPref, the updating of INT_g_SortPref causes the calc in PGM_i_SortDescending and PGM_i_SortAscending to be re-evaluated. So, for example, if user has opted for “Date descending”, INT_g_SortPref gets set to “PGM_Date_d”, which causes PGM_i_SortAscending to be set to “1” in every PGM record, and PGM_i_SortDescending to be set to the value in the specified date field in each PGM record. (NB – these sort fields are not globals, therefore likely to have a different value in each record.)
So in the in the calc, there’s a pair of possible values for each sortable column. In the SortAscending calc, all descending fields are set to “1”, and the ascending fields are set to the field contents of the field on which we want to sort. (Note the need to use “GetAsNumber” for date fields – this is because the result of the calc is text, which will not sort dates correctly otherwise.)
Step 3 – Sort the Portal
Enable sorting for the portal, and specify BOTH sort fields, as shown below:
i.e. the portal is permanently sorted on both fields. This is why we need to put “1” in the descending sort field when we want to sort in ascending order, i.e. PGM_i_SortDescending makes no difference to the sort.
Step 4 – Create the Sorting Script
It’s not really a sorting script at all – it just stores the sort order selected, which has been passed as the script parameter. The sorting itself is done automatically by Filemaker’s own “portal sort” functionality, based on the contents of PGM_i_SortDescending and PGM_i_SortAscending. The trick is in setting those sorting fields according to the column label clicked by the user. (Note – I’ve used the “GSP” custom function here (from the Brian Dunning site), but “Get(ScriptParameter)” will do fine.)
Step 4 – Attach the Script to Buttons in Column Headings
For each column heading, create a button which calls the script with the sort order as a parameter.
Step 5 – Add Sort Order Indicators
There needs to be an indicator for each sortable field. The easiest way is just to have a pair of conditionally formatted text objects at the top of the column, i.e. ▲ and ▼. The conditional format formula is (e.g.) INT::INT_g_SortPref ≠ “PGM_date_a”, text colour is white (i.e. only show the symbol when the relevant sort order is in effect).