Print 
PDFThe 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.
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.
This is a FANTASTIC blog post! A perfect blend of clarity and effectiveness… not too complicated to understand or execute. I’ve done a ton of research on effective portal sorting with buttons and this is, by far, the most elegant solution. THANK YOU!
Thank you Tony. Glad you found it useful.
Thank you Kevin! This is a fantastic post.
Just wondering if this solution works for a concurrent users situation, specifically a remote DB accessed by around 10 concurrent users? Would multiple users attempting to sort the same portal at the same time interfere with one another’s sorting? I’m keen to implement your solution on a system I’m developing if it handles the above situation.
Thanks again for your clear and informative post and a great site in general.
Hi Daniel, I think it will work just fine. I currently use this technique in a 30-user system, and there are no problems when multiple users sort simultaneously. Obviously the technique will slow down if you have hundreds or thousands of rows in your portal, but assuming that related record counts aren’t excessive, you should have no problem.
Good luck,
Kevin
Cheers Kevin!
I think the code does not work when the number of decimals is not fixed, e.g.:
0.1
0.05
0.025
In this case, the sort order (asc) with this code is:
0.05
0.1
0.025
Hi Bruno,
You’re right; this was addressed in the comments section of part 3, but I’ll replicate the relevant portion of that reply here, since this is where it actually belongs.
The issue is that for this technique to work, we need to know in advance what the maximum number of decimal places will be, and then multiply by a large enough multiple of ten to eliminate the decimals entirely. In the example used in part 2, that multiplier is 100, because I was only anticipating a maximum of two decimal places (to accommodate US currency). Since your maximum number of decimal places is four, multiplying by 10000 should fix the problem, e.g.,
Right ( “0000000000000000″ & 10000 * GetAsNumber ( ( GetField ( $$fieldName ) ) ) ; 16 )
Incidentally, a limitation of the above code is that it will only accommodate numbers as large as 999,999,999,999.9999 — beyond that the number of leading zeros, and the second argument of the Right() function will need to be adjusted accordingly.
Also, this technique, as well as Ugo’s technique in part 3, breaks down utterly when it comes to negative numbers. See my recent article, Easy Sorting of List Views, part 2, for some solutions, which can be applied to portal sorting as well.
Regards,
Kevin
One note on the GetFieldName usage:
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.
That’s a great solution for reasons above, but I quickly discovered that you can only use it if you have the TOs mapped the same way from parent/global table to child, and from the child source table to the field in question. My child field is in a separate file with different TO mapping so I need to set the field with the TO path that works in the child file. So as a result, I cannot use the correct GetFieldName (table::field) on the parent parameter call because that path doesn’t exist.
It works fine if I just call correct field path in quotes, but you are then stuck with hard-coded text for this.
whenever any body teaches to show demonstrations like this they never show first the most minimalistic one Single Instruction required for having portal sort two different ways”.
i not underunderstand it !! its way over my head ! its more than what i need to learn for tring to get a portal to simply sort two different ways using the most minimalistic steps. very nice taking time to share depths though.
Very nice! – keep it simple.