Virtual List Reporting, part 4

Introduction

Back in 2017 I wrote about a technique to enable users to a) produce multiple on-screen reports, and b) interact with those reports in browse mode. The article was called Virtual List Reporting, part 3, and while the approach it advocated works well enough under most circumstances, today I’d like to share some fresh ideas.

Note: as you might guess from the title of this article, implementing this technique in your solution, and/or understanding what’s going on under the hood, requires some knowledge of virtual list. If you are not familiar with virtual list, or need a refresher, you may find this article helpful: Virtual List Simplified.

Demo Files (require FM 18 or later)

• vlr-part-4-multifinds.zip
• vlr-part-4-fast-summaries.zip

Why A New Approach?

Several reasons, actually. First, the approach I used previously was formulated before I had any understanding of JSON. The “v3” approach involved declaring a separate $$variable[repetition] for each “cell” (intersection of row and column) in each report, so if you had four reports on screen, each with six columns and 3,000 rows, you were declaring 72,000 individual variables. Clearly this was less-than-optimal, even if the system did clean the variables up automatically as you closed each report window.

So instead of individual variables, I wanted to use some sort of JSON structure behind the scenes. More specifically, and this brings us to the second reason, I wanted to bring this approach into alignment with the methodology I laid out at the beginning of 2020 in Virtual List Simplified.

And, finally, I wanted to apply recent lessons learned about dynamic variable creation, as detailed in Set Variable By Name Re-Revisited.

So What’s Different This Time Around?

Instead of one $$var[rep] per cell, we now have one $$var[rep] per row, and that $$var[rep] contains a JSON array.

Last time I used the “multifind” technique exclusively. This time there are two demos: one using multifinds, and one using fast summaries. For more information see Fast Summaries Re-revisited.

As before, variable names are scoped to window names, but now the variable names are encoded as MD5 hashes, avoiding potential problems with forbidden characters or excessively long window names (these pitfalls are explored in Set Variable By Name Re-Revisited).

Before the $$var[reps] looked like this:

Now they look like this:

Also, previously, although the hyphen character is allowed in window names, it is a forbidden character in variable names, so the v3 demo replaced them with n-dashes. Now that we’re using MD5 hashes for variable names, that substitution is no longer necessary.

Running The Demos

To generate reports, click “State Summary” or “Customer Summary”, and a popover will appear offering relevant options.

If you click the “Debug mode” checkbox, a) the report window will appear on-screen during report generation vs. only appearing on-screen when the report is ready to view; and b) the virtual list table will appear on-screen as well with its window name mirroring the window name of the corresponding report (which makes sense since the $$var[reps] are scoped to window names).

This also means that (since the window name won’t correspond with any $$var[reps]) you will see a bunch of garbage if you simply click “Display Virtual List Table”.

I debated eliminating that button from the demos, but ultimately decided to leave it in as it can sometimes be useful to quickly display the table for development or exploration purposes.

As in the previous version, the “Customer Summary” report has clickable-sortable column labels.

And, as before, clicking on any row in either report…

…will take you to the corresponding set of Sales records in the main demo window.

And, once again, as before, closing the report window automatically clears the corresponding set of $$var[reps].

Nota bene: if you close the window in layout mode, the script will not trigger.

Multifinds vs. Fast Summaries 

In many cases, which approach you decide to go with comes down largely to personal preference. However, there are a few distinctions to be aware of.

First off, the multifinds are almost always “cleaner” in terms of avoiding or minimizing the need to add “helper fields” to your tables. For example, the sales table in the multifind demo has eight fields…

…but I needed to add the two highlighted fields below to the fast summary version of the demo. I added “st” to serve as a break field for the GetSummary function (break fields must be local, i.e., live in the current table, and for performance reasons should be stored). Also the fast summary technique requires a summary “count” field, so I added that as well.

So why bother with fast summaries? Well one reason is that configured thus (with a stored break field), fast summaries tend to outperform multifinds. Another reason is that fast summaries do not require you to know in advance what you are searching for… give them any found set, and it will be grouped and parsed.

However, the other side of that particular coin is that, unless you employ some clever trickery, fast summaries cannot report on what is not there, whereas multifinds are great at reporting on both what is and what isn’t there. Let’s compare by looking at September 2021 sales, and specifying “By # of Sales”, in both the Multifind and the Fast Summary versions of the demo.

The multifind demo returns 50 results, including three states with no sales.

Meanwhile the fast summary demo plows through all sales for September 2021, and reports on exactly what it found, but not what it didn’t find (i.e., the three states that had no sales).

Final Thoughts 

Today’s article shows one application of scoping variables to a particular window. This technique has other uses, some of which may appear in a future article. It would be easier of course if FileMaker gave us access to internal window IDs, but as long as you guarantee your window names are unique (for a given file), this approach provides a valid work around.