Faux Subsummaries via JSON + Virtual List

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.

2018-10-15_09-45-51

Demo file: Faux Subsummaries via JSON + Virtual List

2018-10-15_09-54-56.png

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.,

2018-09-30_19-47-15a

…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:

2018-09-30_19-47-15c

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.

2018-10-15_10-54-46

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.

2018-10-15_11-03-07.png

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.

2018-10-15_10-47-40.png

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”.

2018-10-15_10-59-32.png

And returning to the report layout, note that conditional formatting is used to italicize the “continued…” entries in repetition 1 of cell_text_r.

2018-10-15_14-34-39

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

2018-10-15_15-21-58.png

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 6x or 7x 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:

2018-10-15_15-38-50.png

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”).

2018-10-15_15-52-51.png

6 thoughts on “Faux Subsummaries via JSON + Virtual List

  1. Daniel Wood

    Awesome stuff Kevin, and pretty simple to implement too, nice work!

    Now for your next installment – how do achieve the above where row heights of individual records vary based on amount of text on each record lol :)

    Reply
    1. Kevin Frank Post author

      Hi Daniel,

      Thank you. With regard to variable row heights, I think it can be done… it would involve looping and building up the report one record at a time, popping in and out of preview mode, to constantly get a reality check re: what page a given record is on… somewhat similar to one of the methods used to generate a table of contents (except you would loop forward instead of backward — see https://filemakerhacks.com/2013/07/13/pdf-catalog-with-table-of-contents/ for an example of the latter).

      Also, to prevent “widow” faux subsummary entries at the bottom of a page, backtracking to clobber the most recently declared array entry, and then redeclaring it in the next “slot”.

      Hmmmmm…

      — Kevin

      Reply
  2. Daniel Wood

    Hi Kevin,

    Very awesome stuff! I’ve come up with a demo file of my own and a way to solve variable height body parts using parts of your methods from both articles plus a few extras, particularly where determining the first record on a page is concerned. I have sent you an email with more information as well as the demo file.

    Cheers!

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.