|
|
|
|
Subsummary Unique Count
|
Level: Advanced
Version: FileMaker 19
Category: Reporting
Tuesday, August 24, 2021 |
|
|
|
|
|
|
|
|
|
|
|
|
Over the years, clients have asked me for a variety of reporting features that aren't easily created with standard subsummary and summary tools. A lot of them, including this technique, use the GetSummary function. I remember having such a hard time wrapping my head around the GetSummary logic when I was working in technical support. Once, I got over the hump, GetSummary became one of my best friends, helping me overcome all kinds of FileMaker battles. In this article, I'll demonstrate how to provide a unique count of subsummary breaks.
What is a Subsummary?
This is a pretty simple technique but if you've never created a subsummary report, you'll need start with the basics. Luckily, I've written an article on the subject titled Subsummary Sandwich. This article also covers the basics of the GetSummary function which will be crucial in creating the unique count.
The Target
Here's a simple, two level subsummary report output of a table containing colors and fruits, with the first level being Categories and the second level Types.
Category A (3)
Type 1
Green
Yellow
Purple
Type 2
Brown
Orange
Type 3
Blue
Pink
Category B (2)
Type 1
Mango
Apple
Type 2
Pineapple
Orange
Grape
The numbers in parenthesis next to each Category, count the Subsummary types within each category. While this can be accomplished using a script that loops through all the records in the found set, this is time consuming and could cause record locking. Not to mention the additional time required if you want to modify the found set by omitting even a single record. I want something calculation based that updates as the found set is changed without worrying about running a script.
FYI: Get(UUID) and Get(UUIDNumber) are valid methods for uniquely identifying a record but won't work in this scenario since the minimum value needs to be determined (see below).
Start by creating the Subsummary report you see above. While your Subsummary report may differ, once you understand the basic concepts behind this technique, you will be able to easily modify it to meet your own specific needs. Just to be clear, the Subsummary report above has a Subsummary part sorted by a field called Category, another Subsummary part sorted by a field called Type and a Body part.
And, a screen shot of the example layout with the parts hidden so you can see the placement of the fields.
The table attached to your report layout should already have a primary key with an auto-enter serial number. If you don't, you will need to create a primary key and reserialize all the existing records using the Replace Field Contents feature. I'm going to call this field "serial". Make sure the serial number is a number and not a text field or you won't be able to create the summary field in the next section.
The next field you will need is a summary field that determines the minimum of the serial number field. Call the field "serial_min". Then, create the following calculation field and title it "unique":
Case(serial = GetSummary(serial_min; type); 1; 0)
The final field you will need is a summary field called "total_unique" which totals the unique_mark field. This is the field you will place in the Subsummary sorted by Category part. Here's a recap of all the fields in this solution:
The idea behind the GetSummary function is to allow you to figure out the smallest serial number in a particular grouping rather than the entire found set. In other words, the GetSummary function allows you to assign a value of "1" to only the smallest serial number in the current Subsummary by type. While a summary field is context sensitive to the part containing it, summary fields are no longer context sensitive inside a calculation field unless the GetSummary function is employed.
I Still Don't Get It!
What helped me to understand the GetSummary function was placing the fields on the layout that would normally be hidden. In the screen shot below, you'll see three columns of red for each of the fields: "serial", "serial_min" and "unique".
I'd start with the last column where the serial number is stored. Notice how it displays the first field value of the "category" and "type" (not the minimum) when displayed in their respective subsummary parts. Whereas, the body part simply displays the serial number from the current record. This is what I mean by context sensitivity. In other words, it's the same field all the way down the column but when it's stored in different parts, it displays different results. Understanding why the result appears as is is key to understanding this technique.
Jump over to the middle column of red values and now you are looking at the summary field "serial_min". Summary fields are also context sensitive to the part where they are contained so you will see the minimum (not the first) serial value for the grouping. In other words, it displays the minimum serial number for the "category" grouping and a different mimimum for the "type" grouping. Same field, different results depending on what part is displaying the field.
Last but not least, the "unique" calculation field in the first column of red is sort of like a marker. It places a one (1) on the minimum record in each "type" grouping. That's because of the GetSummary function which is breaking by the "type" field. All you have to do now is total up those ones with a serial number and you have a unique count of the "type" groupings for each "category".
This is Gonna be SLOW!!!
That's exactly what another developer told me when I suggested this technique. Luckily, the developer tested and was surprised to find out it wasn't slow. In fact, it was faster to display the report than with a looping script, as described at the beginning of this article.
Other approaches could use relationships to determine which records have the minimum serial number. I remember seeing this technique used way back in my technical support days and boy is it slow. Relationships are one of the slowest features in FileMaker so try to avoid stretching them too far to do reporting. It's likely going to back fire on you. I've seen so many reports using relationships instead of subsummary parts to make an spreadsheet like appearance. Don't go there unless you test it thoroughly over a network and with lots of records.
Other Techniques
I've used this basic technique before and even published a separate article titled Conditional Subsummaries. It uses Get(RecordNumber) instead of a serial number field so feel free to substitute the serial number with Get(RecordNumber) if you don't want to add a serial number field to your table.
Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com
Example File (available to patrons at $5.00 per month): Download here if you are a patron
This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!
|
|
