FileMaker Portal Sorting That Doesn’t Suck™

I am working on a FileMaker project that uses a “home” layout featuring a portal. The portal’s “parent” is a global text field containing a list of ID numbers. It’s related to an ID field in the child table. Nothing too unusual.

Portal SetupThen I started looking at sorting the portal. One nice thing about a list or table view is that it’s easy to give your users the ability to change the sort order on the fly. A portal’s sort order, though, is “baked in.” There’s really only one way to sort a portal: via the fields you specify in the portal’s Sort options in layout mode.

What if you want to click a column heading to sort? If you’ve used other dynamic portal-sorting techniques, you know they can be somewhat cumbersome. My thought was: I already have a list of child records (which I loop through in a script to populate my global text field). Why don’t I just sort these records before I loop through them? My ID list will then be in the order that I want.

Not so fast — the order of the IDs in the global field is ignored by the relationship. The records still display in an unsorted portal in the order they were created. Likewise, the global ID list can’t be used by the portal’s sort options.

I decided to try an experiment: what I needed was a field in the child table that calculates its position in the list of IDs. Then I could just sort the portal by that number.

Now, FileMaker lets us count the values in a list, filter values, and determine the position of a character. What it doesn’t provide is a function to tell you on what line in a list a certain value appears. That is what this calculation does:

Let( [
   idList = ¶ & $$gIDs & ¶ ;     // substitute your global var or field
   id = ¶ & ID & ¶ ;             // substitute your ID field
   end = Position ( idList ; id ; 1; 1 ) ;
   cutList = Left ( idList ; end ) ;
   result = ValueCount ( cutList )
       ];
   result )

For example, let’s say our ID is “XYZ,” and it’s 4th in a list of 10 items. We first chop off all the text that follows XYZ (items 5-10). This leaves us with items 1-4 (“cutList” above), and XYZ becomes the last value in the list. Now its position in the list is the same as the number of values in the list: 4.

All that’s left to do is sort the portal by this field. It works!

To recap, you will need:

  1. A script that gathers the child IDs into a global variable or global text field, and does a sort.
  2. A calculated field in your child table that tells you where its ID is in the global list of IDs.
  3. A portal that is sorted by the calc field.

The great thing about this technique is that all the logic and control is script-driven, which makes it easy to set up and to make changes down the road. You will not be incurring technical debt.

This technique can be extended in all kinds of fun ways with conditional formatting, script triggers, etc. You can even allow your users to do a custom sort. Yes, a custom-sorted portal! See the example file for a basic implementation.

I would say that this technique lends itself best to an interface that is driven by a single-record/global ID list. I.e., we gather IDs as in step 1 above, not just to sort, but also to determine which records are displayed, using a relationship from a global ID field to the ID field in the child records. Otherwise, you’d need to trigger the sort as you navigate from record to record, and possibly do additional housekeeping to keep track of each record’s sort order. Multiple windows might also present challenges.

A few last points to keep in mind:

1. The calculation above should be set to Unstored.

2. If you use a global field (rather than a variable) to store the IDs, make sure its type is Text — a number field can’t store a list.

3. It seems, based on my limited testing, that when the calculated sort field is driven by a global field, the sort script does not require a window refresh step. When the calc references a global variable, it does require a refresh.

4. My friend and fellow FileMaker developer Matt Navarre pointed out that we don’t necessarily need to know the value number of the ID in the list — although you may want it for other purposes — it’s sufficient to simply know the order, which can be accomplished with the position function alone. Instead of a neat 1, 2, 3, 4, the result will be something like 1, 6, 11, 16, but gives us the same sort order:

Let ( [
   idList = ¶ & $$gIDs & ¶ ;     // substitute your global var or field
   id = ¶ & ID & ¶ ;             // substitute your ID field
   result = Position ( idList ; id ; 1; 1 )
       ];
   result )

Download the sample file.
(Edit: 5/25/11 -added Freeze Window script step)

I look forward to your comments.

If you found this interesting, be sure to read the followup article.

Better FileMaker Portal Sorting

How do you offer your FileMaker users the ability to sort portals?

One method that’s been around a long time is to create a calculated field in the “child” table (the table whose records appear in the portal). This calculation is used as the portal’s sort field. The formula can become somewhat complex if it needs to account for a mix of data types, e.g. fields for date, text, number, etc. And if you want to sort by multiple fields, it gets even messier.

Another method has gained traction since the advent of tab objects (in FileMaker 8), and that is to create multiple copies of your portal. Each copy is placed on its own tab and given its own sort order. The addition of object names (in 8.5) and zero-width tabs (in 9.0) further refined this technique.

The problem with both of these methods is maintenance. In the first method, the calculation has to be revisited any time you change the fields in the portal. And in the second method, making changes to multiple copies of a portal is just a pain.

The method I’m going to show you does use a calculated field, but it’s relatively easy to grok and you never have to change it. Sound interesting? Stay tuned!

When FileMaker Server fails to start

Today FileMaker Server 11 Advanced failed to start on my iMac running OS X 10.5 (it’s my developer server). I could launch the admin console and click the start button, but nothing would happen. The FMS command line was no help either.

Fortunately I have an archive of email from the fmpexperts mailing list. A year-old post from Stephen Wonfor (thanks, Stephen!) provided the answer, which was contained in this link:
Terminal Tips: Rebuild your Launch Services database to clean up the Open With menu

I downloaded the free utility Maintenance, ran it, and chose the rebuild LaunchServices option. Problem solved!