Level: Intermediate, Summary List, Version: FM 13 or later

Summary List Fields in FM 13, part 2

Welcome back for our second look at the new summary list field type in FileMaker 13. Today we have three demo files…

…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)

1-19-2014 9-41-31 PM

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.

1-19-2014 10-30-33 PM

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…

1-19-2014 10-31-10 PM

…and 2) the first letter filter will be applied at the portal level, like so:

1-19-2014 10-49-37 PM

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.

1-19-2014 11-47-45 PM

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

1-20-2014 9-40-41 PM

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:

1-19-2014 12-38-56 AM

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…

1-20-2014 10-01-43 PM

From the current found set in Invoices, we can see and navigate to related products and customers…
1-20-2014 10-03-52 PM

And from the current found set in Products we can see and navigate to related customers and invoices.

1-20-2014 10-09-30 PM

Thanks to the new “hide object when” feature in FM 13, we can easily modify what the user sees under specific circumstances.

1-20-2014 10-13-27 PM

Here’s what’s going on behind the scenes:

1-20-2014 10-25-07 PM

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).

1-20-2014 10-41-51 PM

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…

1-21-2014 11-46-13 PM

…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…

1-22-2014 12-34-52 AM

…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:

1-22-2014 12-18-40 AM

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.

1-22-2014 12-43-12 AM

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…

1-22-2014 12-49-57 AM

…and here’s the calculation definition:

1-22-2014 12-57-48 AM

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.