Level: Intermediate, Version: FM 13 or later

Unique Records Revisited… again, part 1

Several recent postings on this blog have offered variations on a theme: using the new-in-13 summary list field type as the basis for a multiline relational key. The net effect is to produce a relationship that is found-set-aware. (If you aren’t familiar with summary lists, you can read about them here.)

Last week we looked at basing a value list on one of these “summary list relationships”, to facilitate counting unique values within the current found set…

4-2-2014 8-02-41 AM

…and today we have a demo file, FM-13-Count-Unique-v1, that extends the concept to sub-summary reporting.

4-1-2014 10-57-14 PM

The demo is a simple flat file (flat table, actually) of sales, salespeople and zones. The challenge is to produce a sales report by zone for any arbitrary found set, showing number of salespeople and total sales per zone, as well as grand totals for both.

4-1-2014 11-00-13 PM

Total sales per zone is easily derived via a summary field, but FileMaker does not provide an obvious mechanism to count unique entries within a found set. The operative term here is “obvious”, because there are a number of less-than-obvious ways accomplish this, and we’ve explored several of them in the past, including…

But today we’re going to look at a new approach.

The basic idea is to create a couple of found-set-aware value lists — the first for salespeople-per-zone (within the found set), and the second for all salespeople in the found set, and then use the ValueCount function to count the salespeople in each value list.

Here are specific steps to make this happen:

1. Define the three highlighted fields in the sales table.

4-1-2014 11-28-13 PM

2. Add these two table occurrences to the graph, related like so:

4-1-2014 11-41-24 PM

3. Define this value list:

4-1-2014 11-32-01 PM

4. Define another value list:

4-1-2014 11-33-43 PM

5. Define a calculated field, c_count_salespeople:

4-2-2014 10-04-56 AM

6. Define another calculated field, c_count_zone_salespeople:

4-2-2014 10-07-58 AM

7. Configure the report layout like so:

4-2-2014 10-09-51 AM

8. And finally, create a script to run your report. Make sure it sorts by zone, and includes a “Refresh Window (Flush cached join results)” step.

4-2-2014 10-22-16 AM

So much for the step by step instructions. But what’s actually going on?

In this found set of 13 records, two zones are present: South with three unique salespeople, and West with two.

4-3-2014 10-33-07 AM

Let’s concentrate on the South zone. If we open the data viewer, we can see that for South, c_zone_id_list returns a stack of four IDs corresponding to that group of entries.

4-3-2014 10-41-54 AM

As we saw above, the definition of c_zone_id_list is…

4-3-2014 11-55-45 AM

Reminder: GetSummary requires that the found set be sorted by the specified “break field” (i.e., zone) — otherwise it will return nothing, as per this (unsorted) example:

4-3-2014 11-58-53 AM

The multiline key (c_zone_id_list)  is related to the ID field…

4-3-2014 10-44-39 AM

…and since our value list sees only related values…

4-3-2014 11-01-35 AM

…we can use the ValueListItems function to get the list of unique names for whatever zone is active…

4-3-2014 11-07-31 AM

…and the ValueCount function to count them.

4-3-2014 11-11-32 AM

And here it is on the report.

4-3-2014 11-23-14 AM

What about the total salesperson count? It’s derived via this multiline key…

4-3-2014 11-29-00 AM

…in a very similar manner to what we’ve just seen.

Leave a Reply

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