We all know FileMaker is a great platform to store, organize, and find data, be it grandma's recipes or the inventory of a multinational corporation. Most avid FileMaker users can figure out how to enter, delete, search, and even create lists of stuff with little to no help. The one thing that we often struggle with is reporting on all this data. We know that FileMaker is super powerful in this regard, having seen examples of its reporting prowess everywhere, from the Internet to personal experiences using solutions designed by others. But how to do it exactly often eludes us.
More often than not, we initially store our data in a spreadsheet. We then transfer the data from the spreadsheet to FileMaker in the hopes of expanding functionality. A spreadsheet, at its core, is a different beast. A spreadsheet is a static grid with a very intuitive but sometimes limiting method of summarizing data. For example, we can easily total the data to the left, right, top, or bottom of our “Sum” cell in a spreadsheet. This rigid grid makes it easy to count our data because of its inflexibility. This rigidity is also why most of us ultimately move most, if not all, of our data-driven workflows into FileMaker. FileMaker allows us to structure the interface and data the way we want instead of being forced to use some arbitrary grid. But, we soon find out that FileMaker's ability to organize the data the way we want comes at a price; this price is the complexity of reporting.
Most modern spreadsheets offer some on-the-fly reporting capabilities that allow us to organize and sort our lists and then summarize the data accordingly. FileMaker offers similar functions, but usability is less intuitive because the data in FileMaker isn’t typically arranged in a static grid. FileMaker summarizes data using sub-summary layout parts and summary field types. Now we just said that these data tools that FileMaker utilizes to allow us to report on our data were less intuitive, and that is true. Still, once we unlock their concepts, they make total sense and enable us to do much more than a spreadsheet could ever dream of doing.
Summary Field
First, we'll start with the summary type field. When we create a field in the Manage Database dialog, we can choose its type. For example, we would specify a number if we need to store number-based data, a container for file-based data, or text for text-based data. We can even replicate those spreadsheet calculation cells using FileMaker’s Calculation field type. Having said that, we're going to focus on the Summary field type for reporting purposes. A summary field does what it implies; it summarizes data from another field. When we create one, we get a few choices, like counting the data from another field, averaging, listing, or summarizing it. We're not going to go into too much depth here, as that would be a separate blog post. But suffice it to say it is a simple but powerful field type, especially when matched with the next part of the scheme, the sub-summary layout part.
Sub-Summary Parts
To complete our reporting puzzle, we need to head to Layout mode to review sub-summary parts. Layouts are generally made up of headers, footers, and body parts. These parts are loosely connected to the old page layout world of the late 1980s but work well for visually constructing the elements needed to create user interfaces (UI) for displaying, interacting, and reporting data. We usually use the UI-ish parts like body, footer, and header, but we need to start exploring the sub-summary part for dynamic reporting. The sub-summary part is probably the most unintuitive but powerful aspect of this whole thing. In addition, using it with the sorting and summary fields, we can create almost any vertical report.
When we first add a sub-summary part to our layout, FileMaker will ask us what field is the “breaking” field. The platform will use this field during reporting to differentiate each sub-summary part. For example, let’s say we want to report on the prices of different types of bikes within an inventory management application - road bikes, mountain bikes, touring bikes, etc. We would specify the Category field as our break field so that a sub-summary would appear below each group of categories in a report.
Now that we have this part on our layout and have assigned it a break field, we need to determine what fields to place on it and consider the different behaviors summary fields exhibit versus other field types. Since sub-summary parts don’t represent an individual record but groups of records, placing most field types on them may result in some unexpected behavior. Basically, it will show only the first data records for the given field we set, which seems strange initially but quickly makes sense when we internalize their group nature. In fact, this first record display behavior becomes useful when we wish to label the sub-summary part. Taking the above bike example, instead of repeating the category info for each record in the body part of the layout, we can simply move that field out into the sub-summary part, and magically it displays as we would expect.
The real magic of the sub-summary part becomes apparent when we start throwing summary fields into it. Unlike other field types like number, text, and calculations, a summary field is dynamic based on the found set, sort order, and the type of layout part on which it resides. The summary field knows what each sub-summary part is summarizing and dynamically totals them accordingly.
Sub-summary reports are handy tools. Once we have a few simple concepts stored in our brains, we can leverage more advanced aspects of the construct, like the leading and trailing sub-summaries and grand summaries. As we mentioned earlier, these aspects of FileMaker might not just be the easiest to absorb, but once we take the time to get our head around the basics, there are virtually no vertical database-driven reports that we can’t create.
Find more FileMaker tips and tricks, like mastering FileMaker auto-enter calculation tools, leveraging container fields, and moving data from repeating fields. Join our mailing list to keep up with the latest low-code development techniques.
This article is also published on FileMakerProGurus.com.