Experienced FileMaker developers will likely already be familiar with the following portal sorting trick, but I’ve worked with enough developers recently who aren’t (two, to be precise), that I figure it’s worth sharing here.
Problem: how do you sort a portal on a field that doesn’t live in the table the portal is based on? Take, for example, a simple sales database with this structure:
We want to view sales history for a particular product in a portal, like so:
These items are apparently in ascending date order, but that’s just because the users have been good about entering orders in chronological sequence. In reality, the above portal is not sorted at all. But before we get to sorting, a few general observations:
- The layout is based on
products - The portal is based on
line_items - The date column lives in
invoices - The customer column lives in
customers - The three rightmost columns live in
line_items
Now it turns out that the current sort order, or lack thereof, is not what we want. We care more about the most recent sales than we do about the earliest ones, so we’d like the portal to sort by date from most recent to earliest (i.e., in descending order). So let’s go into layout mode, double-click on the portal, and…
…where’s the date field? It doesn’t live in line_items, does it? It lives in invoices, but there’s no obvious mechanism to specify a field from a different table occurrence. Here’s the trick:
- temporarily change the underlying table occurrence for the portal from line_items to invoices
- set the sort criteria to date (descending)
- change the portal’s underlying table occurrence back to line_items
Once we’ve performed all three steps and have returned to browse mode, we can see that our portal is indeed sorted in descending date order.
But now we realize that our sort order should actually be by 1) customer (ascending), and then 2) date (descending).
We can accomplish this with a variation on our previous trick.
- temporarily change the underlying table occurrence for the portal from line_items to customers
- add combo_name (the display name for the customer) to the sort order
- drag combo_name to the top of the sort order (i.e., above date)
- change the portal’s underlying table occurrence back to line_items
Here’s what the sort order now looks like from within Portal Setup:
Finally, we return to browse mode, locate a product that has repeat customers, and we see that the portal is sorting as per our new criteria.
You can grab a copy of this article’s demo file, portal-sorting-part-1, if you are so inclined. And as the “part 1” implies, we’re not done looking at portal sorting yet. It seems to me that it would be nice to be able to dynamically sort the portal by clicking on the column headings, with a little indicator appearing to indicate ascending or descending. And that will be the subject of part 2.