Portal Sorting, part 2

The other day we looked at static portal sorting, where the developer decides in advance how the portal will sort, and “hard codes” those settings into the portal. Sometimes, though, we want to provide users with an interface where they can dynamically sort a portal by clicking on column headings…

…and we’re going to look at a technique to accomplish this today. But first a bit of background. Portal sorting in FM is nothing new — when portals were introduced in FM3, developers quickly realized they could change the sort order by manipulating the related records in the child table, e.g.,

A. go to related records / sort / export / unsort /reimport (update)
or
B. go to related records / sort / duplicate / delete originals

These methods were primitive but effective. A few years later, we got the ability to sort relationships, so any portals based on those relationships were sorted by the same criteria as the relationship itself. And then in FM7, a “Sort” option was added to Portal Setup, so that a given portal could be sorted independently of the sort order (or lack thereof) of the underlying relationship… but the killer feature for dynamic portal sorting was introduced in FM5.5: the GetField function (more on this below).

The basic idea behind dynamic portal sorting is to define one or more calculated “sorter” fields, whose contents will change based on user actions (e.g., 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.

Well, it turns out there are many 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, portal sorting, part 2, if you are so inclined.

[If you use the separation model, here is a separation-friendly version; the difference being that this demo uses global fields instead of variables: portal-sorting-separated.zip.]

Let’s start by looking at what happens when a user clicks a column heading.

All the column headings invoke the same script, “sort portal”, 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.

The script is quite simple…

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) the calculated sorter fields and b) the sort indicators, both of which we’ll be getting to in a moment.

(Instead of Set Field + Commit Records, we could instead use a Refresh Window step, but that would cause the entire FileMaker window to redraw; this method only redraws the portal contents, and is less disruptive for the user.)

Here are the sort settings for the portal…

…and now we see the sorter fields that were mentioned previously. 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.

Here is the definition for sorter_asc, a calculated text field in the line_items 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?

E.g., when the user clicks Qty, the $$fieldName variable is set to “line_items::qty” 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.

Here’s what happens when a column heading is clicked. Since “Sales” is a non-text field, and since $$sortDirection = “asc”, the data appears in sorter_asc, and has been zero-padded so that it will sort properly as text.

When the user clicks the same column heading 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 of course if the user clicks on a column header for a text field, then the result is text, rather than a zero-padded number.

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. And if you’re wondering about the “tickle” variable in the Let portion of the formula, that causes the conditional formula to reevaluate whenever the primary key in Products changes (which you’ll recall happens when the “sort portal” script runs).

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.