Server Compatible Dynamic Portal Sorting

Introduction

In this article we're going to show an approach to dynamically sort a portal. By dynamically we mean allowing the end user control over which field a portal is sorted by.

Traditionally this has always been difficult in FileMaker because portals are sorted using the standard sort dialog, which only allows you to hard-code one or more predetermined fields as the sort order - there simply is no ability to programatically change the field defined.

There have been a number of approaches to this issue over the years and all are certainly usable and do the job.  They do however suffer from a few drawbacks which we'll also discuss.

Cut to the chase, what is your solution?

We're going to do things a little different in this article. Usually we go through a detailed explanation of a technique before getting to the final implementation. We're going to flip this on it's head and instead tell you what the approach is up-front.  

The remainder of the article will then go into all that nitty gritty which we really think you will find interesting and informative.  It helps to understand why this particular approach has been taken so you can get an appreciation for the challenges involved in portal sorting.

So here is our approach

Firstly, we're going to sort a portal by each records internal ID. This can be obtained using the Get ( recordID ) function.  We then set into a calculation field which we'll call rowid.  

When we specify the sort field used, we're going to use the additional option to use a Custom order based on a Value list.

This value list lies at the core of our ability to sort dynamically. It will be a value list of rowid values for the records in the portal but the value list will present them in an order which matches the desired sort order we want.

If the value list is in the desired sort order and we're sorting the portal based on rowids being in this order, then our records will then also be sorted in this dynamic order.

SO, that's the theory anyway. Now lies all the tricky business of implementation. Time to come with us on a journey of learning!

Example File

Now is a great time to download the example file.  We have invested a lot of time again in providing an informative and easy to follow example file that not only explains the technique but lets you explore it for yourself.

Please download the example file here.

And now for the nitty gritty and interesting stuff…

In a nutshell this approach seems simple but it relies on quite a few interesting techniques to get it to work. it also draws upon historical techniques  which we have had to refine for this process. We're going to go through all of these in some detail. 

First, some history on Portal sorting

In order for a portal to be dynamically sorted, it has to allow each individual user control over how they choose to sort it. Simply sorting a portal by a predefined stored field will not allow this, because the stored value cannot change on a per-user basis.

For this reason, most dynamic portal sorting solutions involve the use of an unstored calculation field for sorting. The value of an unstored calculation can be different for each individual user because the result is not stored, and can dynamically change.

If a portal is sorted by an unstored calculation, and the user can programatically change the value of this calculation, then they can affect the portal sort. This is the exact technique Digital Fusion has employed for our dynamic portal sorting for a number of years.

There are some good articles on this topic.  Soliant Consulting have an implementation, as does MainSpring here.

Our approach of choice is a custom function provided by the developer Ugo Di Luca of DL Systems.  You can find the custom function here.

Ugo's custom function can be used to produce a result of any number of various fields in a given table, in either ascending or descending order, for the purposes of sorting.  He does this by converting a fields value from it's default format  (e.g. numeric, date, timestamp, text) into a text string representation capable of being sorted as a text field.

What problems does dynamic sorting face?

While Ugo's solution is ingenious and works, it does suffer from a limitation of unstored calculations.  An unstored calculation can only be one field type.  If an unstored calculation is thus used as the basis of a portal sort then any field it represents must be cast into the same format as the unstored calculation.  

Take a text calculation where we want to be able to dynamically change it's value to be that of other fields. These other fields could be numbers, dates, timestamps or times. Simply putting these types of values into a text field is not going to allow them to sort correctly.

The simplest example of this is the number 1 versus number 10. In number fields, 10 is greater than 1, but as text, 1 is greater than 10. This immediately breaks any sorting we would want to do on numbers.

To resolve this, Ugo has to convert all fields from their base format (e.g. number) into a text string representation that can still preserve sort ordering.

This adds quite an extra layer of overhead to this technique and is a drawback when sorting on lots of records.

The Server sorting issue

Furthermore, in order to dynamically specify which field to use in the unstored calculation, it relies on a user specific preference, and this is often handled through a global variable or global field. The value of an unstored calculation for one user may be entirely different for another user - keep this in mind.

In FileMaker Server 19, a new feature was introduced called Server Side Sorting.  Basically this allows FileMaker Server to carry out the sorting of records where previously this was done entirely on the client machine. This greatly improves sorting performance as previously all sorting was handled client side and required the transfer of all records being sorted from server to client.

BUT (and there is always a but) this feature itself suffers from some problems when it comes to sorting unstored calculations.

When server sorts an unstored calculation it does not expect the value to be different for different users. It actually calculates the same value for all users and ignores any user specific preference.

As a result, this sorting technique breaks when FileMaker Server carries out the sorting.

Unfortunately we have no control over this feature other than to entirely disable it. When enabled, FileMaker Server makes a determination as to whether it will sort records or whether it will defer this to the client. This decision is made on various heuristics to do with server load.

In practice when FileMaker Server 19 came out, we saw our dynamic portals only sort correctly some of the time - the times where server was not doing the sorting.

This led us to the conclusion that unstored calculation based approaches are simply not that reliable from FileMaker 19 onwards.

A "Stored" Solution: Value Lists

So if we can't use an unstored calculation, how on earth do we do dynamic sorting? Well let's take a look at the sort dialog again:

We have this option here to sort based on a custom order. Ha! That sounds kind of like what we're trying to achieve.  This is the option we investigated and looked to exploit.

So let's sort our records based on something unique such as the internal record ID.  The field itself we choose is somewhat arbitrary so long as it is unique, and this internal record ID works best.

The actual sorting is done in the value list because it is the order of the value list which will determine the order of the records. 

How does sorting based on Value Lists work?

Right, so all we need to do is build a value list of rowids which are in an order that we want our records to be sorted. Simple right?  NO you fool! Nothing is simple when it comes to value lists!

We have to first understand what options we have when it comes to sorting a value list.

With sorting, we really have no options for a custom order.  The order of a value list is always based upon sorting the primary field, OR by the secondary field if applicable. In the case of a text field this is always alphabetical which for us is useless - we don't want rowids sorted alphabetically, we want them in a predefined order of our choosing.

Which leads us to the next insightful breakthrough.....

The first challenge - Sorting a value list!

This:

There is an option to re-sort values based on a different language. Why would you generally want to do this? Well let's say you had values in a field that were German words. You don't want to sort these using the English alphabet which might be the file default, and so you instead choose from this drop-down the German language. So this is a language resort.

One of the available languages however is Unicode. Unique is a language which not only includes alphabetical characters, but numeric, and many other symbols. Each character is defined by an ASCII code number, and when sorting a value list by Unicode, it is actually being sorted on its underlying ASCII code.

Why is this of any relevance here? Well it all comes down to a very special ASCII character which has opened the door for custom value list sorting…

Enter the BOM

This character is called a Byte-Order Mark, or BOM for short. It is defined as a zero-width no break space. If you want to learn more you can read the wikipedia page here.

For us, the important characteristic about this code, is that it is invisible. We can include this character in a text string. It does not appear in the string, but it does influence the sorting of that string when sorted by Unicode.

The BOM character can be written in FileMaker using the Code function, i.e.: Code(65279)

So how does BOM sorting work?

Soliant Consulting first introduced this technique in this seminal article from 2012. I encourage you to read and learn about the technique here as they do a better job of explaining it than I ever can.

When a BOM is placed at the start of a string, it alters where that string appears in a Unicode sorted list. By appending more or less BOM's to the start of a string, you can alter it's position in the list when sorted by Unicode.

Here's an example where X is a BOM:

X3958

XX113

XXX924

XXXX2281

These values appear in this order when sorted by Unicode. The list is sorted according to the number of prepended BOMs.

This technique has been used with great success to allow record-based value lists to be ordered based on some sort order for each record. A calculation simply prepends a number of BOM items to the value based on where you want that value to appear in the value list.

Let's bring this back to our approach… 

We are trying to build a value list of rowids in a specific order, so we could use this BOM technique to achieve it. This is exactly what we did – initially…

The second challenge - BOMs bomb out at a point

So, it turns out that the BOM technique works great when the number of values in your value list is relatively low (under 100 values).

Once you hit approximately 100 values things start to go haywire and the values are no longer correctly ordered, so why is this the case?

In the above image we have a list of numbers which are prepended with BOM characters (shown in red).  This field was used as the basis of a value list, and the above values were obtained using the ValueListItems function. This function returns us the list of values as they appear in the value list.

Now, what you'll see here is 3 entries look drastically out of place.  These occur from line 110 onwards, however when this issue presents itself depends also on the length of the number.

Why are they so far out of the desired order?

Turns out the issue is to do with how Value Lists order items. They do so using a fields Value Index. In FileMaker, a value index is not the entire value.  This is the definition from FileMaker help:

A value index is created by taking each line of text (delimited by the carriage return character) and taking up to the first 100 primary character weights that all the characters in that line generate, according to the Unicode Collation Algorithm. See Choosing a language for indexing or sorting.

In short, once we max out the size of the value index for any given field value, things just don't order correctly.  The first characters in this technique are always BOM's so at some point the value index for all records just becomes a big set of BOM's (not the actual field value we want which is the number component).

In short - BOM is great for small value lists, but don't use it on larger ones!

Can we use something other than a BOM to the same effect?

So BOMs kind of bombed out (pun intended), but perhaps not all is lost. Can we salvage something from the lessons learned about BOMs and how they help to do sorting?

Our main issue with them is the length of the BOMs breaking our value indexes. What if we can represent a large number of BOMs by something else which is much shorter?

The solution to this came from the brilliant mind of Steve Senft-Herrer (you may know him as Steve_ssh). What if we simply used a mapping of a values position to letters, and prepended the letters instead?

To show you what we mean consider the below mapping:

0 -> a

1 -> b

2 -> c

...

9 -> i

We simply map a number to a letter.  To preserve correct positioning of a number, we always pad a number out with 0's until it is of a constant length. Let's pick 10 digits, so the number 1 becomes 0000000001.

Now consider the number 3,489,941.  This could be represented as 0003489941 , or mapped is aaacdhiida.

Putting this all together, the value list from our earlier example would now look something like this:

aaaaaaaaab3958

aaaaaaaaac113

aaaaaaaaad924

aaaaaaaaae2281

This can be sorted as Unicode or Alphabetical and works the same way, it's the constant length alphabetic string at the start which determines the actual sorting of the values.

Wait, is this really going to work?

Remember, we're trying to sort a portals records by rowid, using custom ordering based on a value list that looks like the one above. Is that actually going to work?

If I want rowid 924 for example to sort to the third position as above, is it going to work given that technically speaking 924 is not in the value list, it is instead represented as aaaaaaaaad924?

Strangely enough, yes it will!

So, as it turns out in yet another interesting quirk of Value Lists, if you sort by a number field and choose to custom order based on a value list, then FileMaker will interpret the values of that value list as being in the same format as the field you are sorting by - in other words as numbers.  FileMaker completely ignores any non-numeric character in the values and what you are left with is just the rowids which are numbers.

Why this is the case is a question for the Claris engineers.

Because this value list is being created purely for sorting purposes, and is not intended as a value list to be used for value selection, we really don't care about the fact it looks like gibberish - no one is ever going to see it. All we care about is the fact that it is a successful way for us to sort our records.

Time to build the value list

At this point we know it is possible to produce a list of values that can be used to sort records. That's only half the puzzle as we need to actually generate this value list.

How you do this is up to you but we found the best approach is to use a script to carry out the sorting and creation of the value list.

When a user chooses to sort a portals records, a script will run that will produce a found set of those records on a target layout (this can be done off-screen OR on server).

The script can then sort the found set of records by the order which the user desires.

Once sorted, a List Of summary field can be used to collate a list of values to be used in a value list.

We just need a calculation field on the table that can give us this value for each record.  The calculation looks like this:

Let ( [ 
_PADDING = "000000000" ; 
~row_id = Get ( RecordID ) ; 
~record_number = Get( RecordNumber ); 
~prefix = Right( _PADDING & ~record_number ; 10 ); 
~prefix_encoded = Substitute( ~prefix ; 
                           [ "0" ; "a" ] ; 
                           [ "1" ; "b" ] ;
                           [ "2" ; "c" ] ;
                           [ "3" ; "d" ] ; 
                           [ "4" ; "e" ] ; 
                           [ "5" ; "f" ] ; 
                           [ "6" ; "g" ] ; 
                           [ "7" ; "h" ] ; 
                           [ "8" ; "i" ] ; 
                           [ "9" ; "j" ]
) 
] ; 
~prefix_encoded & ~row_id
)

We begin by getting the record's position in the found set padded with zeroes. It's position in the found set is the same as it's required position in the value list. This is then mapped to our alphabetical representation. Finally this string is prepended to the internal record ID.

As mentioned earlier, we summarise this value across all records for our final list. This value is then set into a field which serves as the basis of the sorting value list.

It is important to note this step. In order for a value list to work it has to be indexed, meaning it cannot be based on an unstored field such as a global. We want this value list to be user-specific and so you need to think carefully about this.  The field you use has to be stored.  We prefer to put this field on a record that is unique to the user, such as their own user record in a user table. That way the value list can be based on the same field but from a different record.

Putting it all together

Well, it was a long journey but we got there in the end, let's now recap the overall technique knowing what we now know:

1. Create a field in the table to store the internal record id - rowid.
2. Sort a portal by the rowid, choosing a custom order based on a value list, itself sorted by Unicode.
3. The value list is set up based on a field we'll call ordered_values. 
4. The ordered_values are constructed from a found set of records that are sorted in the desired order.
5. Each record calculates a special string to be used in the value list. This is a combination of an alphabetical string representing a number, and the rowid.
6. To calculate the alphabetical string, take the position of the value in the list (it's record number), and map this number's digits to letters - being sure to pad with zeroes so that the length of the string is a constant length.

Clear as mud? Great! 

Some advantages and drawbacks to this approach

The technique itself seems solid. It can also be made to be more performant than others as each time the portal records are sorted, there is no need to do intensive unstored calculations or conversions of values from their default format into strings.

Furthermore it avoids the unstored calculation issue concerning server-side sorting in FileMaker 19 as it is not based on a calculation. The sorting part is merely sorting a found set of records using a script, and so can easily be handed off to the server to carry out using Perform Script on Server. This can make it very fast to sort large sets of records dynamically.

Now for the disadvantages....

When a portal is sorted by a given order, it remains sorted in that order because it's sort is based on a stored value list. In order for a portals sort to be refreshed, the value list itself must be refreshed meaning the sort script has to be re-run. The implications are that new records added into a portal are not going to be properly sorted until this is done.

Sorting must also be triggered more often, such as when a layout loads or a portal is first displayed. It does add overhead in this respect.

For this reason, the technique has specific cases where it is really useful. These are portals where the records do not change often. This is most portals. It works great for larger sets of records. For smaller sets I'd be looking at the BOM technique as another viable approach.

In conclusion

We hope this article has not only given you some food for thought in terms of alternative approaches to dynamic portal sorting, but that you also learned a thing or two about BOMs, value lists, sorting, indexing, and many more things along the way - this was always our intention!

Dynamic portal sorting is a fascinating area because it is not something Claris has ever offered us as developers. How nice would it be if the Sort Records dialog could be dynamically programmed that would solve all our problems and remove all the need for this stupid hackery!

Example File

Don't forget to check out the example file if you haven't already.  Download it here. 

As always, please feel free to leave a comment, we appreciate all feedback.