Taming the Virtual List – Part 1

Have you ever wanted to create a report in FileMaker, but found it just doesn’t work that way? Perhaps you need to combine disparate data from several tables. Or export data where several records are combined into a single row. Or show totals both “across” and “down”, with a pivot table. Or format an invoice so that a set number of rows appear on each page, with a header and summary totals on every page. With the Virtual List Technique, you can achieve all of these and much more.

What is the Virtual List Technique?

It’s a powerful tool cooked up by Bruce Robertson of Concise Design. Put simply, you use a looping script to build a value list in a global variable, and each value or row becomes a field value in a record in a utility table.

It’s somewhat similar to using a dedicated reporting table, where you create records and set fields, but in this case, the values are short-lived. The Virtual List Technique has several advantages.

• No need to create and destroy records, or track records for this particular report.
• Multi-user friendly. Since data is stored in global variables, it’s unique to each user. A dedicated reporting table would require overhead to ensure each user viewed only the records for that report.
• Speed. Global variables live in RAM on the local machine, not on the server hard drive across the network.

It’s not just for reporting. SeedCode Complete utilizes this technique in the calendar displays and in “selectors” where the user is presented with a pop-up window to select a record in another table. fmSearchResults uses it to produce search results across multiple tables.

So, what do you need?

At a minimum:

• A script (possibly a loop, but the List function is your friend) that builds a return separated value list in a global variable [$$array].
• A utility table with as many records as rows in your reports. (100 records have sufficed for my needs thus far, but use as many as you need.)
  • A number field filled sequentially (1 to 100) [Row]
  • An unstored calculation field to extract values from the global variable
    [ GetValue ( $$array ; Row ) ]
  • A layout with context of the utility table showing the unstored calculation field, or a portal pointing toward that table.

This versatile technique can be expanded by using multiple global variables and unstored calculation fields, repeating global variables, conditional formatting, and even images stored as a reference. Add this foundational skill to your FileMaker toolkit, and you may never answer a reporting request with, “FileMaker doesn’t work that way,” again. In Part 2, I’ll walk through a specific example of how this technique solved a MightyData customer request for a sales order formatted with a set number of rows on each page, and header and summary totals on every page.