[I want to start out by expressing major thanks to Jason DeLooze, who graciously read an early draft of this article, and with some outside-the-box brainstorming (a specialty of his), transformed my sluggish approach into a blazing speed demon.]
Many of the relationships we define in FileMaker are predictable and mundane. A field in table A is linked to a field in table B via an equijoin (=) operator. We do it every day and we’ve done it countless times. Of course we often define more complex relationships as well: ones with multiple predicates and/or more esoteric operators than the venerable equijoin.
Even in this (FM 11) era of filtered portals, we solve many design and business logic problems relationally. However, once in a while a challenge crops up that seems like it should be solvable using the FileMaker relational model, but that frustrates our initial attempts to do so. For example, let’s look at three tables from a sales database:
On our Products list, we’d like to be able to set a date range in the header, and then see cumulative sales for each product during that period.
The problem is, the date field lives in the Invoices table, but the product id lives in the Line Items table, so we can’t simply build a filtered relationship between Products and Line Items. However, there are various ways to achieve the desired result, and today we’re going to look at a method that leverages the relational model, using a 3-step approach.
Step 1: For each product, assemble a list of all related fk_invoice values in line_items. This return delimited list is known as a multiline key, or MLK.
Step 2: For each product, assemble a second MLK, consisting of pk_invoice values from Invoices corresponding to both the date range and the MLK from step 1.
Step 3: For each product, sum the relevant sales values in line_items, filtered by the MLK from step 2 in conjunction with pk_product.
Easier said than done, right? Let’s take a closer look. (Incidentally, this might be a good time to open the demo file.) Before implementing any of the above steps our Relationships Graph looks like this:
To accomplish step 1, in Products let’s define mlk_invoices, a calculation with a text result:
List ( line_items::fk_invoice )
For each product, this will return a stack of all the invoice numbers where this product appears (regardless of whether they fall inside or outside our date range). E.g., for the product Cline – Zinfandel Big Break 6/750, mlk_invoices returns:
115241
116277
116377
(I chose this example for brevity; most products appear on more than three invoices, and therefore will return more than three values.)
Step 2 involves defining a second MLK, but before we can do so, we need to add a new TO (table occurrence) based on the Invoices table. Let’s call it product_invoices_in_range, and link it to Products this way:
Now we can define our second MLK in Products. Let’s call it mlk_invoices_filtered, and again we want it to be a calculation with a text result:
List ( product_invoices_in_range::pk_invoice )
Now we’ve got a proper filtered list of invoice numbers per product that fall within the specified date range. For example, returning to Cline – Zinfandel Big Break 6/750, if we set the date range to…
…we can see that mlk_invoices_filtered returns two values…
116277
116377
…because that product appeared on two invoices during that period.
At this point, here’s what our Relationships Graph looks like.
We’re ready to move on to step 3, defining the calculation that will display product sales for the specified date range. But before we can do that, we need to create one more TO, based on the line_items table. We’re going to call it line_items_summary, and connect it to Products like so:
And now in Products we can define sales_filtered, a calculation with a number result:
Sum ( line_items_summary::sales )
Surely we’re done, right? Almost. Due to FileMaker “helpfully” caching join results, it turns out that we need to give it a little kick in the pants whenever we modify the date range. To do this, let’s attach a script trigger, “trigger: update”, to both of our global date fields:
This script trigger consists of a single script step: Refresh Window [Flush cached join results], and while it’s not exactly subtle, it gets the job done. Now when either of the date fields are modified, the sales totals will correctly update.
At this point, experienced FileMaker developers may be shaking their heads knowingly and thinking, “That’s all very well if your date range encompasses a small number of records… but what if you have a wide date range, or lots of records, or are accessing the solution across a LAN, or worse, across a WAN?”
These are of course valid concerns, and it’s possible that this is not a good fit for your solution, but the technique turns out to be surprisingly zippy; and as the saying goes, “Don’t knock it till you’ve tried it.”
Well, that’s it for today. Future postings will be devoted to further explorations of this fascinating subject.
Demo file: filtered-relations-01