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.
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.
This is freaking awesome. Thankyou so much!
I’ve got this working well with text fields great. My date and number fields will only sort Ascending, any idea what I could be doing wrong?
Off the top of my head, I would suggest taking a look at the definition of your sorter_desc field, specifically the bottom line of code, which deals with non-Text fields.
Hi Kevin,
I’m trying to get this to work, but when I excute the “sort portal” script the changes are only being done in the first portal row record. Any idea what is wrong?
Best,
Mariano
Hi Mariano,
Hard to say… you should review each step and make sure you’ve implemented it correctly in your solution. Two thoughts…
1) is this a single file solution? (This technique is *not* separation-model friendly.)
2) are you passing the correct parameter to the script?
Hope this helps,
Kevin
P. S. The technique can be *made* separation-model friendly by using global fields instead of $$variables.
Thanks Kevin,
After trying different things, I tried working with a global field and now it works like charm!
Best regards
Mariano
Brilliant solution, thanks very much!
Thank You! Tried a lot of other solutions, and this one is by far the best! I used global fields instead of ($$) global variables and “Refresh Window”. Works great, thanks again for the contribution.
Globals instead of $$vars are a good way to go — especially if you use the Separation Model.
Looking for a little assistance… I must have a bug that I can’t find. Clicking my column header button executes the script that sets up the variables correctly, but the calculation field for “asc” (or “desc” if set) only shows a question mark. The field is big enough, and I have determined that GetField ($$fieldName) is the culprit. If I replace the calc with GetField (“the_field_I_want_to_sort”) it works (quotes required). So why does the variable fail? (Quoting the variable fails too). The demo solution works great, and I copy/pasted the calculation just to ensure typos were not the issue…
Hi Brian,
Is this a single file solution? And are your calculation fields properly defined?
(As per previous comments in this thread.)
Regards,
Kevin
When you see the ? in sorter_asc, what is the value in $$fieldName?
Remember this technique is not separation friendly, so if you’re using the separation model, you’ll need to use global fields instead of $$vars.
It is a single file solution. The calculation fields are an exact copy/paste with text as the result and unstored. There are 3 fields in the portal. As I click them, the $$fieldName variable changes. Currently it is Main_Service_Ticket::Ticket_Sub_Type. Main_Service_Ticket is a table occurance of the table Service_Ticket. I haven’t tried using the global field yet…
There’s no reason to go with globals in a single file solution. $$vars will work fine. And it sounds like your calc fields are defined correctly (but see my final question below).
What happens if you copy and paste the contents of $$fieldName into the data viewer and wrap it in Evaluate? E.g.,
Evaluate ( Main_Service_Ticket::Ticket_Sub_Type )And based on your earlier description, it doesn’t sound like this is the culprit, but just to rule it out, is the layout where the field appears based on the Service_Ticket TO?
Finally, in your calculated fields, at the very top of the calc definition window, is the “Evaluate this calculation from the context of” pointed at the correct TO? This is easy to overlook, and you can get some very wonky results if it’s using the wrong TO.
You are awesome! The context was “Service_Ticket” when it needed to be “Main_Service_Ticket”. I thought it was a relationship issue but forgot to look at that. Thank you so much for your time!! The solution is now even better thanks to people like you… Brian
You’re welcome Brian. Glad we got to the bottom of it.
This is a great solution and it works like a charm. It took me a bit of thinking because the description wasn’t quite clear (to me). But finally I got it working and it is just great.
This is brilliant – great job on this, Kevin! Thank you!
a) I appreciate you saying so, and b) you’re welcome.
Thanks for sharing. One thing to watch out for… In the calculation field make sure it is being evaluated from the Table Occurrence you will be using with your portal relationship.
Absolutely. As per my comment May 7th of this year. But it can’t be repeated often enough since it seems to be a perpetual topic of confusion. Thanks.
This is really good and it will be wonderful when I get it working properly in my solution, thank you! I’m obviously missing a vital step however, and can’t seem to find it. Clicking on my column headers fires the script and I can see the values changing in ::sorter_asc and ::sorter_desc field as expected. Problem is, the portal rows don’t actually sort. I do have sorting set-up on the portal using the same sorter fields – Any ideas what idiotic thing I am (not) doing?
Thanks again!
Hi Ben,
This will turn out to be one little thing you missed, e.g.,
a) Make sure your calculations are unstored
b) Make sure the calculation result type is Text
c) Make sure your calculation starting context is correct (as per my May 7th comment)
d) If you’re using the separation model, then you need to use global *fields* not variables
Item “c” has turned out to be the culprit for several people.
Let me know if you’re still having problems.
Kevin
P.S. e) make sure your script parameter is correct
Thanks Kevin.
This is now working. Thanks again for documenting this so well!
My issue was actually different to a,b,c or d above. I’m not proficient enough to know why, but by trial and error I had to modify the sort script to commit the record and then refresh the window as well. Using ‘Set-Field/Commit Record’ didn’t work on its own, and ‘Refresh Window’ didn’t work on its own. But ‘Commit Record/Refresh Window’ does (no need to ‘Set Field’).
Apologies Kevin,
‘Set-Field/Commit Record’ works perfectly. The problem I had was that I didn’t have anything in the ‘Set Field’ calculation parameter, so my script step erroneously looked like: Set Field [MyTO::ID].
It should look like: Set Field[MyTO::ID; MyTO::ID]
Kevin,
I seem to be having a problem getting the calc in sort asc and sort dec to evaluate properly. Context is working ok as it works for number and date fields just fine. It comes up against the problem when trying to identify a text field and it seems that the FieldType function seems to be failing (not evaluating at all). The separate parameters for that function evaluate fine [Get (FileName) and $$fieldname.
Have tried it on other solutions and it works fine, but this seems to fail on one particular solution (non-separated).
Using FMA 14.
Best regards,
Samuel
Hi Samuel,
Let’s start with an easy question: are you sure the result type of the sort calculation is text? What you’re describing sounds suspiciously like the behavior that would result if the result type were number.
Kevin
Thanks for getting back to me Kevin. Yes these calc results are text. That was also my first thought.
Samuel
The technique is valid — it’s just a question of figuring out what you’re doing wrong. Are the sorter field calculations unstored? Are you sure your Evaluate from context is correct? Are you passing the correct script parameter? Have you reviewed all the previous comments for this article? I would be very surprised if this hasn’t already been discussed.
Fortunately, the owner of the solution had a thought – there was a period in the file name. This was what was causing the failure. All good now.
Thanks,
Samuel
Awesome write-up and answers!
One little fine point is I could not use the Set Field option so I used the Commit & Refresh Window but I had to have the Refresh Window Command -“Flush cached Joins” for mine to work; this cost me a few hours to figure out so I’m sharing this. Note I use $$var fields.
Works well with my one file, but not in the separation model.
I tried using Global Fields, using ones in the related file and in the main file, but it seems that since the “context” in related file cannot reference the “context” in the main file I am having some difficulties.
Hi Laura,
Perhaps this separated version of the demo can help you zero in on what’s not working in yours?
http://kevinfrank.com/fmh/portal-sorting-separated.zip
Kevin
Ok, I think I see what the difference is:
The one layout is based on a table in the one file while the related table belongs to another file. (Instead of both the layout and the portal table being in a different table.)
Anyways, thank you!
Hello – if anyone still monitors this thread, I have a question re this:
“All the column headings invoke the same script, “sort portal”, but with a unique parameter consisting of the fully qualified field name (tableOccurrence::field). ”
What is a column heading? Any word can be a column heading but how will a script get the name of the field in the column?
Hello, I have a question re this:
“All the column headings invoke the same script, “sort portal”, but with a unique parameter consisting of the fully qualified field name (tableOccurrence::field). ”
Any word can be a column heading. How does a random word over the column get the name of the field in the column?
You manually specify GetFieldName ( tableOccurrence::fieldName ) as the script parameter as per the second screen shot in the article.
This was an awesome posting and still being viewed nearly 6 years later, obviously. It was way over my head to start with but I have managed to implement it and have it working great. Just need to finish it off now, with the carets symbols. Thank you Kevin for explaining it so well.
Amazingly helpful blog. I am having trouble with text based fields sorting properly. The calculations seem to work perfectly for numbers but not text fields. The calc returns a ? Any guesses
Hi Dan,
As I wrote to Ben a couple years ago…
Let me know if you’re still having problems.
Kevin
Hi,
I am new to FM.
Thanks for your great tutorial. It works very fine but I have a problem.
I have 2 Portals on my Layout. If I klick the Label of the above Portal, both contents of portals (different relational tables) are been sorted. Is it depending on the global Variables? Scriptparameter is correct.
Why?
Thx
Tanja
Hi Tanja,
I’m guessing that you need to use separate variables for each portal.
Regards,
Kevin
What a wonderful script Kevin. Thanks for sharing this.
Congrats for that great post, really elegant.
What I’ve decided for my FMPA file is that signs for the sort direction are obsolete because the user intuitively sees onto which direction the sort has gone but thats just my opinion.
Just experimenting with FMPA 17 I ended up in a portal referring to the actual table itself in an extra layout page which gives a user more information instead of using the Master/Detail concept.
Unfortunatly that portable can’t be sorted. So I decided to use a global var for the sort direction and a ‘sorting script’ for each of the buttons forming my column headers, i.e. like this for the ID in pseudo language:
user abortion off
if $$sortdir is desc
set $$sortdir to asc
sort ID asc (once configured via dlg)
else
set $$sortdir to desc
sort ID desc (once configured via dlg)
end if
Works good but requires one script for each column to sort. Maybe someone has a more elegant (generic) solution…
Solved. Debugging helped. It’s been necessary to change the original script: $sp to a global $$sp, simplify the inline statement for the second var declaration by putting it into single lines and explicitly add a sort statement for the table to be sorted with the fields sorter_asc and sorter_desc.
Great performance even with 10k recs.
10 years after this blog post was writen, and I’ve just implemented it in to a new solution!!
Thanks for this very helpful post!
Just in case anyone has an issue when sorting
One of my number fields is a percentage stored as a decimal (e.g. 0.25 for 25%) and when sorted, it would always have the zero records at the top, then the rest of the records would sort correctly below those. I modified the last line in both of the sorter calcs to multiply by 10000 instead of 100 and it seems to work better in my use case.
Just read the comment from November 2012, where someone else mentioned it.
Good to know that I came up with the same solution though😀