Taming the Virtual List, Part I

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.

This entry was posted in FileMaker. Bookmark the permalink. Follow any comments here with the RSS feed for this post. Post a comment or leave a trackback: Trackback URL.

6 Responses to Taming the Virtual List, Part I

  1. Thanks for writing about Virtual Lists in FileMaker. I’ve heard of this technique but haven’t found an explanation of how to build it. I look forward to the example you will give in part 2 of this article.

  2. Lisette Wilson says:

    Daniel,

    Thanks for your interest. You may also want to check out the sample file provided by SeedCode in the DevCon 2011 Year in Review download.

  3. Thanks Lisette. I was unaware of the YIR resource.

  4. Lisette Wilson says:

    You’re welcome, Daniel.

  5. abhaya Kumar Behera says:

    Hi
    can you suggest me how can i see its implementaion of the demo of seedcodecalenderDemo.fmp12?
    Is it possible to see the databse for this demo? If yes then please send the admin access username and password to me on this emailID

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>