Update 30 September 2021: while this article is worth reading for background information, I now recommend using the approach utilized here: Virtual List Reporting, part 4
Last year I posted a couple articles on the topic of virtual list reporting (part 1 and part 2), a.k.a. VLR. Today we’re going to look at some additional things you can do with VLR, and to avoid a lot of repetition, this article will assume the reader is familiar with the material covered in part 1, wherein benefits are extolled and fundamentals explained.
Demo file: VLR-part-3.zip (3.4 Mb compressed)
Did you notice in parts 1 and 2 that we were limited to producing a single report at a time? Yes, we could sequentially output them and concatenate them into a PDF, but we couldn’t display multiple reports on-screen simultaneously, nor could we interact with them in browse mode.
This time around we’re going to look at a method to produce multiple independent reports concurrently, each in its own window, based on a single virtual list table. This will entail generating a large number of dynamically instantiated $$vars[withReps], so we will also explore a way to automatically clear them out (without the overhead of having to keep track of them during instantiation).
Disclaimer/Warning: before running multiple Customer reports, open your Data Viewer and activate the Watch tab. You don’t want to display the Current tab with 5K or more variables in memory, unless you’ve got a lot of… time… on… your… hands.
Here are nine State reports displayed simultaneously. Let’s see… 9 reports × 50 states × 6 columns = 2,700 variables. But, as mentioned, each report’s variables will auto-evaporate when you close its window.
Some additional points of interest: in the Customer Summary report we’ve got clickable/sortable column headings that function independently on a per-window basis…
…and in both reports, clicking on a row…
…takes you to the corresponding records in the Sales table.
As you may recall, a primary VLR objective is to keep the data tables and relationships graph as lean as possible, i.e., not polluting them with unnecessary helper fields or relationships, and doing as much of the heavy lifting as possible at the script and layout levels, and in the virtual list table itself. So, as you might expect, the tables and graph in today’s demo file are very basic.
Schema Overview
Tables
Relationships Graph (expanded to show all fields)
Virtual List fields
Virtual List Reporting in a Nutshell
This material was covered in detail in part 1, but to briefly recap… we’re using the Multi-Find technique to summarize values from Sales, and pushing that data into $$variables, or, more accurately, $$variables[withReps], which are then parsed by an unstored repeating calculation in the virtual list table (two repeating calcs, actually — one for text and one for numbers)… with table columns corresponding to individual named $$vars, and table rows corresponding to $$var[repNumbers].
If you’re curious what these $$vars might look like, here are a few, prudently displayed on the Watch tab.
Previously we used $vars, but today we’re going with $$vars because we want them to persist after reporting scripts have finished running, and so we can easily interact with the reports in browse mode.
Also, except for the $var/$$var difference, today’s report generation scripts aren’t substantially different from those we saw in part 1 and part 2, so I’m going to gloss over them except to point out a few points of specific interest.
Report 1: State Summary
Note: Sales values are updated at demo startup, so some of the numbers you see in your copy of the demo will not match the ones shown here.
Okay, let’s generate a couple reports: choose Top Ten state summaries for 2014 and 2015, set Months to “All”, check the “Also display virtual list table” box, and click “By Sales ($)”.
Here are the reports…
…and here are corresponding views of the virtual list table. Note that, as confirmed by the ID column below, we are looking at the same records in rows 1-5 in each window. How is it possible for them to show different data in the “cell_num_r” columns?
The answer is that this technique is bound to the window name: the $$var[reps] generated by the reporting script are window-name specific, and these two fields in the virtual list table are window-name aware (they are identical except for result type).
Report 2: Customer Summary
This time we’re summarizing sales data by customer, rather than by state, and the column headers are clickable/sortable.
This approach is based on “Method B” as outlined in Easy Sorting of List Views, part 2, but with directional indicators updated to use Hide Object instead of Conditional Formatting, and the “sort_column” calculations now include the window name as part of their definition.
“Safe” variable names
Have you ever noticed that certain characters and/or terms are not allowed when you create a variable via the “Set Variable” script step?
What happens if you instead instantiate the variable via Let (either directly, or indirectly via a custom function)… can you then get away with including forbidden characters and/or terms? No you cannot. If you attempt to do so, you don’t see the above dialog of course, but the operation fails nonetheless.
If you’ve examined the reporting scripts, you’ve seen some examples of dynamic variable instantiation in action, e.g.,
…with $reportTitle corresponding to the window title of the report, and while most of the forbidden characters and terms are easily avoided, I didn’t want to do without hyphens, so an expedient work around was to substitute n dashes for hyphens when assembling $reportTitle, e.g.,
Clicking on a Report Row
As mentioned earlier, you can click on a report row…
…to navigate to the underlying set of records in the Sales table in the main window.
Here’s the navigation script, which derives its instructions from, you guessed it, the report window name.
Autoclearing the $$var[reps]
As mentioned at the outset, we can clear all the $$var[reps] corresponding to a given window without having to keep track of them at the time of creation. The two report layouts have this script trigger, attached like so:
And you probably won’t be surprised to hear that the window name once again plays a prominent role in the proceedings. Here’s the script:
Explanation
- Use the FieldNames function to build a list of field names (and, more importantly, repetitions) that are on the current layout
- Use CustomList + UniqueValues (or a custom function if FM version < 16) to produce a list of $$variable names corresponding to the window name and the unique repetitions on the layout, e.g.,
- Loop through that list, clearing all necessary reps for each $$variable using a custom function, ClearRepeatingVariable (which made its debut in Virtual List Charts, part 1).
Does it work? Hmm… let’s see. Before closing the report window:
After clicking the close box:
(Approximate elapsed time: 1/4th of a second.)
Reminder: script triggers cannot fire in Layout mode, so if you want your report window to auto-clear its $$var[reps], make sure to close it in Browse, Find or Preview mode.
Closing Thoughts
Virtual List Reporting is remarkably flexible, and once again, we’ve barely scratched the surface re: what can be accomplished. But I think this is enough for one day. The 2017 FileMaker Developers Conference starts in less than 48 hours and I look forward to seeing some of you there.