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.

54 thoughts on “Portal Sorting, part 2

  1. Tony

    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!

    Reply
  2. Daniel

    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.

    Reply
    1. Kevin Frank Post author

      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

      Reply
  3. Bruno

    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

    Reply
    1. Kevin Frank Post author

      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

      Reply
  4. Bob

    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.

    Reply
  5. RJ

    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.

    Reply
  6. James Peragine

    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?

    Reply
    1. Kevin Frank Post author

      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.

      Reply
  7. Mariano A. Cao

    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

    Reply
  8. Kevin Frank Post author

    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

    Reply
    1. Kevin Frank Post author

      P. S. The technique can be *made* separation-model friendly by using global fields instead of $$variables.

      Reply
    2. Mariano A. Cao

      Thanks Kevin,
      After trying different things, I tried working with a global field and now it works like charm!
      Best regards
      Mariano

      Reply
  9. Jacob

    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.

    Reply
    1. Kevin Frank Post author

      Globals instead of $$vars are a good way to go — especially if you use the Separation Model.

      Reply
  10. Brian Paquin

    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…

    Reply
    1. Kevin Frank Post author

      Hi Brian,

      Is this a single file solution? And are your calculation fields properly defined?

      (As per previous comments in this thread.)

      Regards,
      Kevin

      Reply
      1. Kevin Frank Post author

        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.

        Reply
  11. Brian Paquin

    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…

    Reply
    1. Kevin Frank Post author

      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.

      Reply
  12. Brian Paquin

    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

    Reply
  13. Giovanni

    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.

    Reply
  14. Stephen

    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.

    Reply
    1. Kevin Frank Post author

      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.

      Reply
  15. Ben M

    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!

    Reply
    1. Kevin Frank Post author

      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

      Reply
  16. Ben

    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’).

    Reply
    1. Ben M

      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]

      Reply
  17. Samuel Toombs

    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

    Reply
    1. Kevin Frank Post author

      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

      Reply
      1. Samuel Toombs

        Thanks for getting back to me Kevin. Yes these calc results are text. That was also my first thought.

        Samuel

        Reply
  18. Kevin Frank Post author

    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.

    Reply
    1. Samuel Toombs

      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

      Reply
  19. Matt M

    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.

    Reply
  20. Laura

    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.

    Reply
      1. Laura

        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!

        Reply
  21. Fred

    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?

    Reply
  22. fredography

    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?

    Reply
    1. Kevin Frank Post author

      You manually specify GetFieldName ( tableOccurrence::fieldName ) as the script parameter as per the second screen shot in the article.

      Reply
  23. Shona

    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.

    Reply
  24. Dan P

    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

    Reply
    1. Kevin Frank Post author

      Hi Dan,

      As I wrote to Ben a couple years ago…

      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

      Reply
  25. Tanja

    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

    Reply
    1. Kevin Frank Post author

      Hi Tanja,

      I’m guessing that you need to use separate variables for each portal.

      Regards,
      Kevin

      Reply

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