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…
…and today we have a demo file, FM-13-Count-Unique-v1, that extends the concept to sub-summary reporting.
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.
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…
- Identifying Unique Records
- Ranking Entries in a Summary Report
- Unique Records Revisited, part 1
- Unique Records Revisited, part 2
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.
2. Add these two table occurrences to the graph, related like so:
3. Define this value list:
4. Define another value list:
5. Define a calculated field, c_count_salespeople:
6. Define another calculated field, c_count_zone_salespeople:
7. Configure the report layout like so:
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.
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.
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.
As we saw above, the definition of c_zone_id_list is…
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:
The multiline key (c_zone_id_list) is related to the ID field…
…and since our value list sees only related values…
…we can use the ValueListItems function to get the list of unique names for whatever zone is active…
…and the ValueCount function to count them.
And here it is on the report.
What about the total salesperson count? It’s derived via this multiline key…
…in a very similar manner to what we’ve just seen.