Filtered Relations, part 3

Today we’re going to take a third look at the challenge we encountered in part 1 of this series: Given a simple database consisting of Products, Invoices and Invoice Line Items, how can we show total sales per product filtered by date range?

Reminder: what makes this problem challenging is the fact that the date field lives in the Invoices table, but the product foreign key (fk_product) lives in the Line Items table.

To briefly recap, we solved this problem in part 1 at the cost of adding a couple fields and table occurrences, i.e., by leveraging the relational model. And in part 2 we solved this problem by displaying a related summary field inside a one-row filtered portal.

Wouldn’t it be nice if we could simply tell products to display date-filtered sales per product without having to resort to tricks? Well, that might be asking a bit too much, but at least we can come up with a different trick this time around: we’re going to use a SQL plug-in to tap into FileMaker’s internal SQL engine, which will allow us to solve the problem at the calculation level.

In this article, we’re going to use the doSQL plug-in from MyFMButler (if you don’t own it, you can download a trial version), but the SQL code itself should work with any SQL plug-in.

SQL can appear daunting when you first encounter it, but with trial and error you get the hang of it, and pretty soon don’t want to live without it. For a FileMaker developer, one of the amazing things about SQL is that it can perform complex relational operations that are completely independent from the Relationships Graph. SQL doesn’t care about the Relationships Graph at all, except that when you point it at a “table”, you must specify a table occurrence name, not a table name.  To illustrate this point, the demo file for this article contains no relationships at all. Each table appears once on the graph as an isolated occurrence, an island unto itself.

Before we get to the main event, let’s “warm up” by using SQL to solve a very simple problem. In the Invoices table, we want our calculated total field to sum related line items for each invoice.

The natural thing to do, if you’re thinking like a FileMaker developer, is to define the total field as Sum ( line_items::sales ). But that won’t work in our current example. We’ve intentionally omitted all relations from the Relationships Graph to force us to think like SQL developers.

Before writing a SQL statement it’s often helpful to state in plain English what you wish to do. If we could wave a magic wand, we would tell our total field:

sum the sales values in the line_items table
where the foreign key in line_items
equals the current invoice primary key

Now that we know what we want to accomplish, we can compose a SQL statement to transform our dream into reality. Let’s start by writing a statement to total the sales for a single invoice.

SELECT SUM ( sales )
FROM line_items
WHERE fk_invoice = '110829'

Even if you’ve never seen a SQL statement before, assuming you understand basic relational database design, the above makes sense, doesn’t it?

Note: all primary and foreign key fields in this example are text fields, despite the fact that they contain numeric data. This means that any data referenced in connection with these fields must be surrounded by single quotes. If the field type were number rather than text, we could dispense with the single quotes — and, starting in FM 11 (which has a new internal SQL engine), we would *have* to dispense with the single quotes.

We can test the expression in the data viewer, and confirm that our syntax is correct — if it weren’t, we’d see a question mark (when you begin working with SQL, you will see “?” far more often than you would like to — but don’t let it discourage you; it’s just part of the learning curve).

Since we have to use a plug-in if we want to tap into FileMaker’s internal SQL engine (a.k.a. FQL), the SQL statement we wrote above is wrapped inside a plug-in call. Also, since this statement is expressed as a text string, it’s surrounded by double quotes.

That’s not bad for a start, but summing related sales for one invoice wasn’t what we had in mind was it? In just a minute, we’ll modify that statement so we can use it to calculate related sales for each of our invoices. But first I want digress for a moment to complain about discuss single quotes, a.k.a. apostrophes.

They can be difficult to see when they appear adjacent to double quotes — I liken them to an annoying cloud of gnats —  so to avoid confusion, I wrote a custom function which I call simply “Q”:

In a nutshell, this CF supplies the surrounding single quotation marks so we don’t have to type them or think about them, and ironically, the above image illustrates the problem perfectly. A single quote surrounded by double quotes is extremely difficult to parse visually. But once you’ve written the CF, you may never have to deal with this problem ever again (until you edit another developer’s SQL code at any rate).

If you’re wondering about the Substitute function in the CF, its job is to escape any embedded single quotes (or apostrophes) in your data, by prefacing the single quote with another. That’s right… the first one escapes the second.

Another benefit of using Q is  it allows us to easily point to data in a field in our database. Here’s the actual definition of the total field. Note that instead of a hard-coded invoice number, we’re now referring to the value in pk_invoice for each Invoice record. And if you’re following along in the demo file, you can verify that it works.

So, having written our “warm up” SQL statement, we’re now ready to head back to the Products table and construct a more complex calculation that will allow us to total sales for each product within a specified date range.

But first, a few observations:

1. We can assign aliases within SQL statements to improve readability.
2. FileMaker dates are expressed differently than SQL dates. The calc definition you’re about to see uses a custom function, DateToSQL, to transform a FileMaker date into a form that SQL will accept.
3. The following code joins the Line Items and Invoices tables. This join only exists for the brief moment it takes to calculate the result. That’s how joins work in SQL.

And here, at last, is the calculation definition for sales_filtered in the Products table:

Since the statement refers to multiple tables, we need to specify which table a given field belongs to, by assigning aliases to the Line Items and the Invoices tables, and then prepending those aliases (“LI” and “I”) to the field names, separated by a period.

Interestingly, the approaches employed in parts 1 and 2 of this series required us to run a Refresh Window command whenever the date range changed, and a script trigger was employed for this purpose. Here in this demo file, no “wake up call” is required, so ironically, the approach that is the least FileMaker-like, turns out in some ways to be superior.

This article has barely scratched the surface of what’s possible using FileMaker’s internal SQL engine. I will have more to say on this subject in future postings. And speaking of future postings, part 4 in this series (yes, there really will be a part 4) will showcase one more way to solve this filtering challenge.