Log in
Hot & Trending on FMT

Kevin Frank's FileMaker Virtual List Reporting, part 1

Featured Kevin Frank's FileMaker Virtual List Reporting, part 1

Welcome to the first installment of a two-part series on producing reports using the virtual list technique, or more properly, collection of techniques. Download the Demo file: VLR-part-1.zip

demo

Invented and popularized by Bruce Robertson, virtual lists are incredibly flexible, and have made a number of appearances here in the past, including…

The Basic Idea

In case you aren’t already familiar with virtual lists, in a nutshell, you create a special utility table in your solution, and pre-populate it with “more records than you’ll ever need” (there are 10,000 in today’s demo). The records in this table will derive their data “virtually”, by reading it from some sort of array… typically, one or more variables.

This Implementation

There are various ways to implement virtual list reporting, a.k.a. VLR, and today we’re going to concentrate on one particular approach that has worked well for me recently. Specifically, we’re going to use the Multi-Find technique to summarize values from Sales Data and Web Visits, and push that data into $variables, or, more accurately, $variables[withReps], which will be parsed by an unstored repeating calculation in the virtual list table… with table columns corresponding to individual named $vars, and table rows corresponding to $var[repNumbers].

If you’re already familiar with the virtual list technique the preceding paragraphs probably make at least some degree of sense; if you aren’t and/or they don’t, don’t worry, things should become clearer as we explore the examples.

Why Bother?

It’s a fair question, because on the surface VLR may appear to simply be a more convoluted way to accomplish things that you already know how to do via standard FileMaker reporting techniques. And for basic reporting tasks, I would agree.

But as requirements become more challenging, VLR enables you to generate reports (reports that would normally require the creation of multiple helper calcs and/or utility relationships) without touching table schema in your main solution or making any modifications to the Relationships Graph.

Once the Virtual List table has been defined, the heavy lifting will be done primarily at the scripting level… and of course at the layout level, but that would be true of traditional FileMaker reporting as well — in fact VLR layouts are typically simpler than corresponding standard FM reporting layouts.

Bottom line: willingness to invest in a bit more complexity up front can help things run more smoothly down the road. Let’s take a look at the structure of the demo, and then we’ll explore the report examples.

Note: Sales values are updated at demo startup, so the numbers you see for the current year in your copy of the demo will not match the ones shown here.

The Demo

The demo consists of two primary data tables (Sales and Web Visits), the Virtual List table, and a number of supporting tables.

2016-04-24_15-43-41

Note that the primary data tables are almost completely pure, i.e., devoid of reporting “helper” fields, apart from a single summary field in Sales…

2016-04-24_16-10-59

…and one in Web Visits.

2016-04-24_16-16-24

The Virtual List table is a study in minimalism as well.

2016-04-24_16-40-50

There is a table view, which can come in handy for troubleshooting…

2016-04-24_205247

…and, finally, here’s the Relationships Graph, which is also extremely basic.

2016-04-25_101356.png

Report 1

We’re going to begin with a very simple cross-tab report. Starting from here…

2016-04-24_16-49-05

…how do we end up here?

2016-04-25_9-39-45

As mentioned above, the bulk of the heavy lifting is performed by the reporting script, which begins with some basic prep, and the initialization of a number of $variables…

2016-04-24_183849

…including $listY and $listT, which end up being populated like so (note that the first row of $listT is intentionally empty):

2016-04-24_185208

Next the script loops through the two lists and populates $col_x[rep] variables, where x represents columns 1 through 5, and [rep] corresponds to each iteration of the outer loop, i.e., the numbers 1 through 8 (since there are eight values in $listY — assuming you’re running the demo in the year 2016).

2016-04-24_190001

At this point the script has loaded up a bunch of $var[reps]…

2016-04-25_094156

…which will be parsed by the Virtual List table into an unstored repeating calc field, cell_num_r:

2016-04-24_21-17-27

Cell_num_r extracts data from the aforementioned $var[reps] by translating calc reps into $var column numbers, with the ID value of each table row corresponding to a $var repetition number. The column translation works because Get(CalculationRepetitionNumber) enables each rep of the calc to know its own rep number.

If you’re wondering about the “[1]” after the ID in the calc def, it ensures that the calc reps correctly refer to the non-repeating ID field. As an alternative, I could have wrapped ID in an Extend function, but I prefer array notation (i.e., “[x]”).

2016-04-25_9-56-29

So, to take a specific example, rep 2 of cell_num_r in row eight will first assemble a $var[rep] named “$col_2[8]” and then will Evaluate that var, returning 1072493.34.

The remainder of the script is devoted to displaying the report.

2016-04-24_220555

Here is the report in layout mode, with reps 1-5 of cell_num_r individually placed from left to right, and reps 2-5 formatted for readability to display zero decimal places.

2016-04-24_221246

And, as we saw at the outset, here is the end result.

2016-04-25_9-39-45

Note that the reporting script ends with a Pause step to facilitate $var exploration via the Data Viewer. You will need to unpause before moving on to the next example.

2016-04-25_093555

Okay, that was a fair amount of explanation, but it also took care of a lot of foundation-level material. Moving forward, we will focus on specific points of interest in reports 2 – 6.

Report 2

Here we have a year/month cross-tab Sales report…

2016-04-25_114600

…and here it is in layout mode. We are going to use the same layout for report #3 as well, so there are a couple of merge variables in place of hard-coded text labels. In the body we have reps 1 through 14 of cell_num_r…

2016-04-26_043346

…and the totals in the trailing grand summary come from a corresponding 150-rep summary field, defined like so (we don’t need to specify the number of reps for the summary field; they are inherited automatically from cell_num_r):

2016-04-25_14-39-08

The script for Report 1 was intentionally written to be as transparent as possible, e.g., it employs no custom functions and uses only static variable declarations. In the remainder of today’s reporting scripts we are going to make things more efficient by utilizing a pair of custom functions.

First, CustomList allows us to replace this…

2016-04-25_145034

…with this:

2016-04-25_145236

CustomList also facilitates building the month number list.

2016-04-25_150911

(These are the most simplistic examples of what this amazing CF can do… even if you normally avoid CFs on general principle, I recommend making an exception for this one.)

Second, SetVarByName facilitates the creation and population of $col_3 through $col_14, i.e., months 1 – 12. Here the CF merely eliminates a bit of tedium; in reports 4, 5 and 6 it will play a more substantial role.

2016-04-25_14-48-48

Finally, note the use of the custom paper size trick to gain an extra horizontal inch for the report layout.

2016-04-25_152306

Report 3

As mentioned, this report uses the same layout as report #2, although this time we’re summarizing Web Visits instead of Sales.

2016-04-25_204349

This illustrates one of the advantages of VLR, because, having already built report #2, I was able to bring report #3 online in (literally) five minutes by duplicating the script, modifying the lines highlighted in yellow, and adding the lines highlighted in green.

2016-04-25_205759

Report 4

This is an expanded version of report #1, with sales order counts and percentages.

2016-04-26_034030

SetVarByName gets more of a workout here.

2016-04-27_015359

Report 5

Here we’re taking report #4 one step further, by adding a trailing grand summary with three-year averages looking backward from today’s date.

2016-04-26_035627

Once again, thanks to VLR, an additional block of script code accomplishes this, and we don’t need to touch the Relationships Graph or table schema.

2016-04-26_14-53-27

Note that in line 63 above we set $cNum to 22. One of the tricks VLR keeps up its sleeve is to work some of the magic in higher-numbered column $vars. The report layout displays reps 1-12 of cell_num_r in the body, and then reps 22-32 of the same field (not a summary field) in the trailing grand summary.

2016-04-26_150554

If you display the virtual list table before generating the report and scroll it righward like so, you will see numbers like these appear in row 8 during report generation.

2016-04-26_043345

As mentioned earlier, what you see in your copy of the VLR demo won’t match what you see here because sales data is auto-updated on demo startup; also if you’re running the demo in the year 2016 the row will be 8 — otherwise it will be higher. (If it’s lower, please clue me in on your time travel secret.)

Report 6

Our final report today is a further refinement on reports 4 & 5, with two new columns at the far right showing the number of web visits and percent of those visits that resulted in a purchase.

2016-04-26_035810

Unlike a standard FileMaker report, which would typically be based on a data table such as Sales, the virtual list table doesn’t care where the data comes from, and you are free to combine data from unrelated tables. Have your script populate the $vars, and the virtual list table will do the rest.

Conclusion

At the risk of stating the obvious, nothing here is carved in stone. If you’ve made it this far, you are probably thinking of ways you can adapt or improve on some of these ideas for use in your solutions. Maybe you’d rather use $$vars than $vars, or would prefer to generate a single 2-dimensional delimited $array (or $$array), and parse the data from that. Those approaches have been explored in some of my earlier virtual list articles, and there are pros and cons associated with each.

At any rate, today we’ve made the tiniest scratch on the surface of what can be accomplished with Virtual List Reporting. In part 2 we’ll dig a little deeper.

Kevin Frank

FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.

Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”

Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.

To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:

Website: www.filemakerhacks.com