Virtual List in 3 Easy Steps

The Virtual List technique has been around for a few years (created by Bruce Robertson ca. 2009). Over time and with changes to FileMaker, implementation of this technique has evolved. Knowing how to set up and use virtual lists will help with creating certain reports, especially if you draw the values from ExecuteSQL statements in FileMaker Pro 12.

Articles (see Lisette Wilson’s Taming the Virtual List, Part I and Part II) and examples are fairly easy to find. So why write another one? Many examples and articles include use-specific cases to illustrate the virtual list. I’m going to attempt to boil this down to three simple steps. Once you draft the basics of this process into a FileMaker file, you can use this as a template for more complex reports. You can re-use the draft as a template, copying the table and script from one solution to another. This technique requires one dedicated reporting table, plus a script to populate some global variables. The fields in the reporting table are set up as calculation fields that get their values from the list within the global variable.

To illustrate the process, I added a table to the Contacts Start Solution as a sample database, which you can download here.

Step 1: The Reporting Table

When you visualize the report itself, it usually consists of rows and columns. Each row is a record in the table, and each column corresponds to a field. At minimum, you need two fields. One field is a hidden id field; it’s a crucial component of the report, but is not displayed. The second field appears on the report, and will contain data generated by the script. This simple two column example below could have been created as a SubSummary report. However, the power of the virtual list is that it allows you to combine data from many tables, something not easily within reach by a SubSummary report. In my demo file I have three fields, one for the id and two for the actual column data (state and count).

Data from contacts table pulled into the virtual reporting table

Step 2: The Fields

Of the two fields mentioned in the table, the id field governs what shows up in the data field or column. This field is set up as a number, starting at one, with each subsequent record incrementing by one. Most reports will have less than 100 rows, so a general rule of thumb is to just create 100 records (however, if your report demands more rows, just create more records). If you create a table view with the report layout, the id field should show 100 rows, with values from 1 through 100.

The second field is the first column of the report. Reports can have multiple columns, so new fields are created as needed, using the same principle. To keep it generic, in my report template I simply named the fields “column1” and “column2” and set them up as text fields. If you need to format data as numeric values, i.e. $43,000.00, this can be done with text formatting functions. The column field is set up as an unstored calculation: GetValue ( $$COLUMN1 ; id ). This will make more sense after Step 3, the script that creates the $$COLUMN1 global variable. Before the $$COLUMN1 variable exists, the “column1” field has no value. Once the variable is created, the field calculates from the content of $$COLUMN1, which is a list, and extracts the value that corresponds to the id number for that row. This is the magical part of the virtual list technique.

Assume that $$COLUMN1 contains these values:

  • Banana
  • Apple
  • Orange
  • Pear
  • Grape

This list has five values. For the first five records, as indicated by the values 1 through 5 in the id field, the GetValue() function will pull the corresponding fruit into the “column1” field.

Step 3: Filling In The Rows

This is the most flexible part of the process. It can range from complex loops within loops to a simple progression of steps to fill each row and column. First, to test if the reporting table works, you can create a simple looping script:

Set Variable[ $Limit ; Value: 100 ]
Exit Loop If [ Let( $i = $i + 1 ; $i < $Limit ) ]
Set Variable[ $$COLUMN1 ; Value: If( isempty($$COLUMN1) ; $i ; $$COLUMN1 & ¶ & $i ) ]
End Loop
Refresh Window [Flush cached join results]

This loop will fill in the first column values with numbers, by virtue of the GetValue function.

To fill in your report with real values, change the “Set Variable” line to something real. In the ContactsVL database, I wanted a count of people living in each state. I created a variable with all the states, and looped through this list. I then ran an SQL query inside the loop for each state.

Set Variable [$$COLUMN1 ; Value: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ]
Set Variable [ $Result ; Value: ExecuteSQL( “select count(*) from contacts where \”Work State\” = ‘” & $State & “‘” ); “” ; “” ) ]
Set Variable [ $$COLUMN2 ; Value: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ]

In another example, rather than a list you can loop through, you have an individual requirement for the report, with text for report sub-heads. Here it’s just a matter of stepping through each row and setting the variable $$COLUMNx with the result. By adding values to a growing list, you then can parse this list into the rows of the reporting table. And for the subheads you can use text formatting functions to add bold and increase the font size to make these stand out.


Once you have set up a reporting table as in steps one and two, this technique can be used for many different kinds of reports. With a simple starter reporting table and a script or two ready in a template, you can quickly expand this concept to specific solutions.


  1. says

    Never saw such user friendly version of a virtual list, def need to take advantage of it. Thanks. Is there a simple way to import from csv in a virtual list? Can it be done from google doc’s spreadsheet? Totally a noob to these type of list so any information would be much appreciated, thanks!

  2. says

    Thanks, Ryan. You probably would need an intermediate table into which you import the csv, then follow up by looping through the records and setting your virtual list global variables. I would envision this as 1) import 2) select using SQL into a variable 3) loop through this variable and set the global variables into the list. With the variables/list being session specific, the import and creation might need to be separate, with the import storing the data for all users to see, then the virtual list creation as needed.


  3. Tony says

    I have one table wherein each record has 300 fields which are viewed on a form. I would like to create another table where I would convert the 300 fields to 300 records, each with one field and viewed as a list. Strictly for printing purposes.
    Similar to creating a pivot table in Excel. Can this be done in Filemaker 9?

    I thought your virtual list may be close to what I need.

  4. says

    Hi Tony,

    A virtual list is usually more suited for one record with n number of fields. You then view only the fields in that record that contain a value in your list to see your report. In your situation, it seems that you need a table with multiple records – 300 – with one field, which seems the opposite of the virtual list’s one record many fields.

    You still could create a “temporary” table to store your 300 records, clear out any existing values prior to running your report by looping through and blanking out the one field. Then you can use the design functions to get all the 300 field names you need for the report, and loop through these to set your 300 records with their values. You could use a key/value pair with the field name and the field value. Looping in FileMaker is fairly fast, and blanking out just one field should take less time than creating new records.


  5. says

    I’m getting my head around the virtual list concept for value lists. Do you have a technique which allows the editing of values, like fmp does; “Allow Editing of Value List”, on its own internal value lists?

  6. Rich says

    Great article. Thanks! Just a typo, though:

    In Step 3, you have:

    Exit Loop If [ Let( $i = $i + 1 ; $i $Limit ) ]

    …so you have the greater than/less than sign reversed.

  7. says

    Excellent article. Thank you!
    Can one summarize the columns in virtual lists? Reliably?
    What I need to replicate is basically ColdFusion’s “query of queries,” where I would set the virtual table and query against it in several ways.

    I have a relatively large (100k +) table of all the properties sold in our local ML by a large group of realtors.
    We need to summarize then sort by ranking:
    1. how many sold by that realtor over a time period
    2. average sales price
    3. how many sales as buyer’s agents
    4. how many sales as seller’s agents
    4. how many by property type (condos, single family, etc.)
    5. sort the above by largest to smallest within their peeer group and company

    Thanks in advance for any help.

    • Kirk Bowman says

      Scott – Yes, this is possible. We have done something similar for one of our customers. You will need to create the total, average, etc. in the FileMaker script using variables. You cannot use the native summary or aggregate functions. Another option would be to use the ExecuteSQL function.

      • says

        The only thing that comes simply to mind in FileMaker is to loop through each of 4200 realtor’s records, and run an executeSQL or aggregate function on each person’s relative sales performance. Hitting the database with this many queries on this many parent records seems like it would be a performance nightmare. Right?

        If so, how would one “hold the 64000 record table in memory as a variable” and query against that to get summaries, averages, etc. I assume that doing it all in RAM then setting fields would be a much faster loop.

Leave a Reply