The Support Group Blog

If you’ve played with FileMaker Pro 11’s filtered portals at all, you may have discovered its biggest stumbling block:  aggregate functions and related data.  All of your calculations summarizing related records no longer reflect what appears in the filtered portal.  Various solutions have been proposed using script triggers and merge variables, but they can require some foresight and maintenance.  Fortunately, there’s an even easier solution for the majority of cases where you need to total up related records.

Let’s examine the “problem” (because it turns out defining the problem requires more work than the solution itself).  Way back in FileMaker Pro 7, when multiple tables per file were introduced, we also got the ability to define complex relationships with multiple “predicates” (sets of match fields) and comparative operators.  This allowed us, for example, to create relationships from Customers to Orders that could be filtered by a date range.  The basic relationship

could be transformed into:

showing all orders for a customer for a given date range.

Once you’ve selected a customer and filtered the orders to a particular subset by date, you probably want to know “How much did this customer buy during this time?”  It’s a very simple calculation to create.  All you need is the previously defined relationship and the Sum aggregate function:

Sum ( OrdersByDate::OrderTotal )

As you change the values in DateRangeStart and DateRangeEnd, new orders would be found through the relationship and added up.  Place that aggregate calculation field on the layout beneath your portal to OrdersByDate, and you have a nice, neat interface to show you historical information about your customers.

Now, it turned out that you usually needed to keep the original, simple relationship defined as well, so you’d end up with both occurrences of the Orders table, Orders and OrdersByDate.  And this led to bloat: scores of table occurrences defined for the practice of filtering the results in a portal.  It’s not that it didn’t work… it’s just that it wasn’t simple.

Fast forward to today and FileMaker Pro 11.  Instead of defining different table occurrences and relationships for each method of filtering related records, you can simplify to just a handful of basic relationships, and then use calculations defined at the portal to effect the filter.  Essentially, filtering has been removed from the data layer defined in Manage Database and shifted to the presentation layer created with layouts.  In the above example, we can eliminate the OrdersByDate table occurrence (and the relationship to it from Customers), and replace the portals on our layout to use the basic relationship to Orders with the additional filter calculation:

Orders::OrderDate ≥ DateRangeStart and Orders::OrderDate ≤ DateRangeEnd

This gives us the same result, with a cleaner, more easily managed relationship graph.

Except… what happens to that calculation to add up the related orders?  There’s no longer a OrdersByDate table, so at best we edit the calculation formula to:

Sum ( Orders::OrderTotal )

But this isn’t the same thing.  Since calculation fields are defined in the data layer of our system, and have no “knowledge” of any filtering being performed in the presentation layer, the sum of related orders will always show the total for all of a customer’s orders, regardless of date.  We’ve suddenly lost some vital information.

A simple shift in perception will allow us to get our order totals by date.  We just need to approach this as if it were a summary report, using a summary field defined in Orders, instead of a calculation field defined in Customers using an aggregate function with related Orders data.  Just as if we were producing a summary report printed from the Orders table, we define a summary field (“OrderTotal Total”?) with the Total of OrderTotal options.  Now, back on the Customers layout, we can place the related summary field in our filtered portal (or a similar portal with the same filter formula).

Our summary field, when viewed on an Orders layout, adds up the OrderTotal for all found records.  The same field, when viewed in a portal, adds up all records that would appear in the portal (though you may not be able to see them all, since the portal may not have a vertical scroll bar).  If the portal is filtered, we get the total of the filtered records.

That’s all there is to it: a summary field in the filtered portal.