In part 1, we looked at a new way to count unique entries in a sub-summary report based on an arbitrary found set of sales data. To avoid redundant explanation, this article will assume the reader is familiar with part 1, but in a nutshell…
1. we used a pair of FM 13 summary list fields
2. to create found-set-aware multiline keys
3. which we related to special purpose table occurrences
4. which provided the basis for related value lists of salespeople
5. the ValueListItems function allowed us to grab lists of unique salespeople
6. and the ValueCount function allowed us to count them
The demo in part 1 was based on a single “flat table” of data. Today we’re going to unflatten that table, and see how the technique can be applied to a properly normalized system, i.e., with separate tables for Sales, People and Zones, related like so…
…and you can follow along in today’s demo file, FM-13-Count-Unique-v2, if you wish.
As before, the goal 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.
Here’s the zone table.
And here’s the person table.
Apart from the relational architecture, what’s different about today’s demo? Well, the main thing is that we don’t need to use value lists. Since we have a person table, and since we have found-set-aware relationships, we just need to count the related entries in that table.
The other thing I’ll mention is that the zone table is two relational steps away from the sales table, i.e., zone is a “grandparent” of sales…
…and this is significant, because the technique relies on the GetSummary function, and we need to have the “zone” field available in the sales table to use as the break field (GetSummary break fields must be local, i.e., in the current table. If zone were only one relational step away we could use the zone foreign key, but we don’t have that option here).
So, we’re going to “pipeline” the zone into sales via the simple expedient of a calc field.
We’re also going to add these three highlighted fields.
Next we’ll throw a couple new TOs, based on the person table, onto the graph and link them up like so (“fs” stands for found set):
And let’s add a summary count field to the person table:
We’re going to use that s_count twice; once in the subsummary (via the “fs_person_by_zone” relationship)…
…and once in the trailing grand summary (via the “fs_person” relationship):
Also note that the report is sub-summarized by c_zone…
…and there are actually two reasons the report needs to be sorted by c_zone:
- to get the subsummary to display
- for c_zone_id_person_list to resolve properly (as per my comments re: GetSummary above)
The “zone report” script is identical to part 1’s…
…except, of course, now it sorts on c_zone.