Today we’re going to take another look at a challenge we discussed last time (in Conditional Summary Report Header)… namely how to cajole FileMaker into displaying a subsummary, or a reasonable facsimile thereof, at the top of a report page when items in the group begin on an earlier page.
Demo file: Faux Subsummaries via JSON + Virtual List
As mentioned, the problem we’re trying to work around is the lack of a subsummary at the top of a given report page, when a vendor’s products begin on a preceding page, e.g.,
…and my proposed fix last time involved leaving just enough space in the header at the top of the page to sneak in the missing info when necessary, like so:
But as Shawn Krueger commented, that approach was less-than-optimal for a number of reasons, including a) having to set aside vertical real estate at the top of every page whether it would be used or not (since headers can’t shrink), and b) the user expectation that subsummaries will appear directly above the records they pertain to, as opposed to being squirreled away up in the header.
Well “virtual list to the rescue” because both of these issues have been addressed in today’s demo. (If you need a refresher on virtual list, I recommend starting here and following links as necessary. And if you need a refresher on JSON, check out Thinking About JSON, part 1 , part 2 and part 3.)
Today’s Approach in a Nutshell
- Sort product found set by vendor name and product name
- Loop through these records
- Generate a 2-dimensional JSON array
- Render that array via virtual list
More Information
As per the “faux” in today’s article title, the apparent subsummaries are going to be simulated. Our report layout consists of just two parts: header and body.
Assuming US letter paper size, portrait orientation, and 35 point margins all around, each report page (except, most likely, the final one) will display exactly 48 body rows, regardless of what those rows contain… or, occasionally, don’t contain (see “Some Considerations” below).
Here’s a portion of the 2D JSON array that will be produced during report generation… and thank you Paul Jansen for suggesting the “row 0” trick — it means the serial ID field in the virtual list table can begin w/ 1 instead of 0, and that the first row of the virtual list table will correspond to the first array row containing actual data that we wish to display on the report.
Here’s an example of what the virtual list table parses from the array, and note that since info in the array is already properly ordered, we won’t need to sort the report prior to displaying it.
And here are field defs for the virtual list table… note that cell_text_r and cell_num_r have identical definitions except that the the result type for the former is “text” and for the latter is “number”.
And returning to the report layout, note that conditional formatting is used to italicize the “continued…” entries in repetition 1 of cell_text_r.
Some Considerations
- We will generate a vendor entry (i.e., an apparent subsummary) whenever id_vendor for the current product record is not the same as its predecessor.
- We never want a vendor entry to appear on the final row of a given page… to prevent this from happening, we will create a [null] array entry at that position, and push the vendor entry to the top of the next page.
- At the risk of stating the obvious, we only need a “continued…” entry when there isn’t already a legitimate vendor entry on the first row of a given page.
Method A vs. Method B
Both methods generate a 2D JSON array, and render it via the virtual list report layout. If you begin from the same found set in products, the array and report will be identical using either method.
Where the two methods differ is in how they assemble the array and the time it takes to accomplish this. Method A uses JSONSetElement to insert each new row into the array. Method B uses Russell Watson’s brilliant “insert calculated result” trick (https://community.filemaker.com/thread/189587) to assemble the array, and is 5 to 10 times faster than Method A.
Final Observations
If the standard paper size in your country is something other than “US Letter” (for example “A4”) you will need to adjust the $rowsPerPage variable on line 15 of the report generation script:
Knowing the total number of rows per page makes it easy to calculate the total number of pages (which will appear at the top right of each report page, in the form “Page x of y”).
