Virtual List in Three 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 One: 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 Two: 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 Three: 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 ]
Loop
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.

#Column1
Set Variable [$$COLUMN1 ; Value: If( IsEmpty( $$COLUMN1 ) ; $State ; $$COLUMN1 & ¶ & $State ) ]
#Column2
Set Variable [ $Result ; Value: ExecuteSQL( "select count(*) from contacts where \"Work State\" = '" & $State & "'" ); "" ; "" ) ]
Set Variable [ $$COLUMN2 ; Value: If( IsEmpty( $$COLUMN1 ) ; $Result ; $$COLUMN1 & ¶ & $Result ) ]
#End

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.

Conclusion
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.

About Anders Monsen

Anders Monsen is the Architect of Integration for MightyData. Anders is a certified FileMaker Developer 9-12, Authorized Trainer 9-12 and FileMaker Developer Conference speaker. He's a runner and triathlete, and spends his other free time driving his two kids to various practices and games.
This entry was posted in FileMaker and tagged , , . 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 Virtual List in Three Easy Steps

  1. Ryan Flynn 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. 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.

    Regards,
    Anders

  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. 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.

    Regards,
    Anders

  5. Paul Fenley 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?

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>