Today we’re going to take another look at the challenge we encountered in part 1 of this series. The challenge is: given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?
One thing is certain: assuming a normalized data model (with the date in the Invoices table), some sort of trick will be required. In part 1, we leveraged the FileMaker relational model to solve this problem, by adding additional table occurrences to the Relationships Graph, as well as some calculated fields in the products table.
This time around we don’t need to work as hard, because we’re going to to take advantage of a feature introduced in FM 11: filtered portals.
If you aren’t familiar with this feature, it allows filtering criteria to be defined via the calculation engine. Prior to FileMaker11, the only way to filter a portal was to tweak the Relationships Graph by adding some new table occurrences and defining some new fields to be used as predicates.
Fortunately, things have changed, and not a moment too soon. Incidentally, now would be a good time to open this article’s accompanying demo file, if you’d like to follow along.
Here’s what our Relationships Graph looks like, and we don’t need to change a thing here.
Before we actually do any portal filtering, however, we need to define a summary field in the line_items table, as per the highlighted entry below.
If you’re wondering what a summary field in the line_items table has to do with filtered totals in the products table, the answer is, “Quite a lot, actually.” It turns out that if we place the summary field from line_items onto a layout attached to products, we will see the sum of all sales for that product. That’s right, placing a related summary field onto a layout, causes it to behave as if it were a calculated aggregate.
Let’s give it a try. Here it is in layout mode:
…and here’s how it looks in browse mode:
But our challenge is to see totals for a specific date range, as opposed to all the sales we’ve ever had for a given product. And that’s where filtered portals can help. So let’s place a single-row portal behind line_items::s_sales, configured as follows:
We’re almost done, but as I wrote in part 1, we need to send a little wake up call to FileMaker when we change our date range, so let’s attach OnObjectSave (or since this is 11, OnObjectValidate) triggers to both the global date fields in the layout header, and instruct the trigger to perform a “Refresh Window [Flush cached join results]” step.
And here’s how it looks in browse mode:
For anyone who may be wondering why I used table view in part 1, but switched to a list view for the current article, it’s because FileMaker refuses to render portals inside table rows. You can place them there in layout mode, but they will be ignored in browse mode.
Believe it or not, we’re done for today, but stay tuned for further adventures in the wild realms of Filtered Relations.
Demo file: filtered-relations-02
There’s a great article that recommends avoiding Flush Cached Join Results because that slows down performance, especially over a WAN connection. Instead, add another criteria to your relationship that is a global field with a Cartesian Join (the ‘x’ operator) to any field (probably easiest to use related table’s primary key field). That doesn’t change the related set of records. However, when you change the value in the global field (perhaps set to current timestamp, or whatever), the relationship will refresh, without flushing the cache. The Ditch Those Flush Caches – Use Cartesian Join Instead” article is on the FileMaker Weetbicks blog by Digital Fusion. Their site is currently under repair in the wake of the earthquake in Christchurch, New Zealand (where their offices are), but will hopefully be back up soon as they recover.
Hi Dan,
I’m familiar with that approach, and tested it when I was building the demo files, but it did not feel any faster when the files were hosted locally.
However, just now I tested Filtered Relations 01 and Filtered Relations 02 across a WAN connection.
In the case of 01, it doesn’t feel any faster. But in the case of 02, it definitely does feel faster.
— Kevin
As a follow up to my previous comment…
I did some more WAN testing, and to put that “02 is faster” into perspective, the filtered portal technique is a dog across a WAN no matter how you slice it.
In general, I’m not a big fan of playing tricks with the Relationships Graph (e.g., adding an additional predicate to trick FileMaker into doing something it should be able to do anyhow). But of course if you find that trick to be useful, then by all means you should use it.
If I get into a situation where it delivers a significant performance boost, I imagine I’ll use it too, but only after investigating alternatives.