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.
That is a good trick, but in my experience portals that are sorted by “foreign” fields can cause a noticeable performance hit. (Could that be why we only see “native” fields in the portal sorting dialog?)
Hi Tom, first off, thanks for commenting. Of course this is just my opinion, but I doubt that’s the reason. If “possible performance hit under certain conditions” was the guiding principle at FMI, 80% of the features we rely on would be missing from the product.
As per the Disclaimers page, and as with many postings on this blog, the technique may not be ideal under all circumstances. If sorting on a related field causes performance problems, one could always store a redundant copy of the sort data in the line_items table, and then take on the obligation to make sure the redundant data gets updated if anything changes in the original… I touched briefly on this with regard to portal filtering in this posting: Filtered Relations, part 4.
This raises an interesting related question. Why change the portal’s underlying table occurrence back to what it originally was? For what I’ve been doing, I can’t seem to find a situation where the underlying table occurrence has any effect on things. Specifically, I’ve been focusing on creating a new record in the portal and having it automatically populate the foreign key (or more generally any match field), and my observation is that the portal’s underlying table occurrence has no effect.
Is there some other area where it would make a difference? Thanks for any insights.
To me, it makes more sense to change it back, because it makes it clearer what’s really going on. I didn’t really “get” portals when they were introduced in FM 3 until I understood that every row in a portal represents a record in a table. The rows in this article’s portal represent related line_item records, so that’s what the underlying table occurrence should be as far as I’m concerned.
If there’s a benefit to be gained by leaving the portal pointed at the grand child instead of the child table, then I’m all for it, but in this case it would just confuse me, and any other developer who has to work on the files in the future.
Thanks, Kevin.
I think you’ve answered my question by saying that having the “underlying table occurrence” appear as a label on the portal in layout mode is useful to the developer, but will not affect the behavior of the portal if it gets changed.
But since I’m now at the point where you were in FM3 (experimenting to learn how portals work) maybe you won’t mind if I ask you to elaborate on your comment that “every row in a portal represents a record in a table”. My description would be that every column in a portal represents a field from some related table, but they can all be from different related tables (and in fact none of them have to be from the underlying table occurrence as far as the portal’s function goes, although I think it would be a mistake to set it up that way). So it seems that you could have a case where the fields are mostly from different tables, and then it might not be that easy to decide which one would be most informative to select as the underlying table occurrence that will appear in the label in layout mode.
Am I on the right track with the “mental model” I’m developing of this? Thanks.
The underlying table occurrence (TO) is very important in all cases. However, you can sometimes get away with pointing a portal at a TO that is farther “downstream” than just a child… e.g., in my demo file, Invoices is the grandchild and Customers is the great grandchild, from the perspective of the parent TO (Products). But as the saying goes, just because you *can* do something doesn’t mean you should.
The fact that you will see *something* in a portal when pointing the portal at a variety of different TOs (as long as they’re properly linked relationally to your home TO — the one your layout is based on) doesn’t mean you’re seeing everything you should be seeing.
E.g., in the demo, go to this product: Paul Hobbs – Cabernet Sauvignon Beckstoffer To Kalon 6/750, and note that there are 9 entries in the portal (assuming the portal is based on Line_Items). Now change the underlying TO to Customers, and you only see three entries, because you’re seeing the three related customers, rather than the 9 related individual sales represented by the Line_Item entries.
Great tip once again, Kevin. I am going to try this. Customers solution is WAN hosted however, so I will report back regarding performance issues in this environment