How To Sort Portals by Clicking on Column Labels

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:

  • Calc
  • Unstored
  • 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:

  1. 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.
  2. 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).

Advertisements

6 Comments on “How To Sort Portals by Clicking on Column Labels”

  1. […] ← The Let Function How To Sort Portals by Clicking on Column Labels […]

  2. thomann061 says:

    What do the relationships look like?
    Thank you for the post.

    • Dave says:

      Well, the field which controls the whole thing, INT_g_SortPref, is a global field, so no relationships are necessary to make that bit work. I’d be tempted to use a global variable, unless you’re using data separation, where you need a field.

  3. AK says:

    This is a beautiful thing : ) Thank you so much for sharing – sorting portals has been unreasonably difficult in Filemaker and I’m not sure if there is another method that is quicker to implement than this now? But this is much better than previous, thanks so much again.

  4. Thiyagarajan says:

    Hi

    Thanks for good technique.

    I used your technique to sort my portal columns ( 3 fields – Data Type – Text , Date, Number ).

    Text and Date – data type fields are working fine. But “Number” data type field is not sorting properly in the portal.. I think this is because of calculation result ( PGM_i_SortAscending and PGM_i_SortDescending ) is “Text”.

    Thats why number field is not sorted correctly. Do you have any idea how we can sort number field also in the portal.

    Thanks
    Thiyagu

    • Dave says:

      Hello Thiyagu. I would strongly recommend the technique explained by Kevin Frank (see the link at the start of the blog post). It’s similar, but better, and has the advantage of dealing with text and numeric data, as well as date fields.
      Dave.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s