Welcome back for our second look at the new summary list field type in FileMaker 13. Today we have three demo files…
- Demo 1: FM 13 Summary List – MLK, v2
- Demo 2: FM 13 Summary List – MLK, v3
- Demo 3: FM 13 Summary List – SQL IN Clause
…which in addition to summary lists, utilize a couple other new-in-13 features: popovers and conditional invisibility. As in part 1, the core technique uses a summary list to facilitate relational found-set awareness (in demos 1 & 2). We’ll also apply a modified version of the technique to SQL queries (in demo 3).
Demo 1: FM 13 Summary List – MLK, v2 (reminder: we saw v1 in part 1)
The challenge here is to filter records in the found set to just those beginning with a specified letter, and display them in a portal. As in part 1, we have a calculated text field echoing the summary list field because it’s not possible to use a summary field as a relational predicate.
The graph looks like this, and the basic idea is that we’re going to have two levels of filtering: 1) the found set will be filtered relationally…
…and 2) the first letter filter will be applied at the portal level, like so:
There are actually two copies of the portal in the popover… the obvious visible one, and a second tiny invisible one — both having the same filter criteria.
Also, the “refresh window (flush)” script we saw in part 1 makes two appearances in this popover: 1) as an OnObject Modify trigger attached to the radio button set (zz_g_temp), and 2) as an OnObjectEnter trigger attached to the popover itself.
Demo 2: FM 13 Summary List – MLK, v3
Here we have a sample invoicing system, very much like the one we saw in part 1, with the basic relational structure set up like this:
Each module (“customers”, “invoices” and “products”) has a popover that displays related records for the current found set in a pair of portals. From the current found set in Customers we can see and navigate to related products and invoices…
From the current found set in Invoices, we can see and navigate to related products and customers…
And from the current found set in Products we can see and navigate to related customers and invoices.
Thanks to the new “hide object when” feature in FM 13, we can easily modify what the user sees under specific circumstances.
Here’s what’s going on behind the scenes:
As per the previous demo, an OnObjectEnter trigger runs a “refresh window (flush)” script when the popover is displayed. And note that none of the portals in this demo use portal filtering… all filtering, and much of the navigation, is done at the relational level, which explains why the graph is bit of a freak show (the “cfs”, “ifs” and “pfs” prefixes indicate customer, invoice and product found sets).
Demo 3: FM 13 Summary List – SQL IN Clause
So far we’ve been using the summary list field as the basis for a found-set-aware relationship, but it can also facilitate found set awareness for SQL queries. Our final demo is quite similar to demo 1…
…except the heavy lifting is done via ExecuteSQL rather than relationally. Reminder: the concept of a “found set” does not exist in SQL, but if we have a comma-delimited list of the primary keys for records in the current found set, we can use the IN operator to limit our query to those records.
Let’s start with a simple example and construct the IN clause manually. Here we have a found set of just four records…
…and we’d like our SQL query to locate wines within the found set costing $25 or less. Manually constructed, the query looks like this:
Since the primary key field (ID) is a text field, each value needs to be wrapped in single quotes (this would not be true if the primary key field were a number field — see FileMaker 11 Internal SQL Changes, part 2 for more info). Here’s the same query, except now we’re going to base the IN clause on the summary list field.
Returning to our demo file, the objective is to return a subset of wines whose names begin with a specified letter from within the current found set…
…and here’s the calculation definition:
Unlike the approaches we saw in demos 1 and 2, this one does not require any triggered refreshes and can reference the summary list directly, as opposed to requiring a calculated text field. But bear in mind that native FileMaker approaches tend to outperform FM/SQL approaches when record counts are large, or across a LAN or WAN.