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).
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 ]
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.
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!
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
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.
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
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?
Hi Paul,
There’s an interesting technique developed by Jeremy Bante that uses Virtual Lists to manage value lists. See his module over at Modular FileMaker, called Virtual Value List.
—
Anders
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.
That didn’t upload well. :S Let’s try again:
“Exit Loop If [ Let( $i = $i + 1 ; $i $Limit ) ]”
Guess not. I guess the app is treating the text and signs as part of a tag.
Rich – Thanks for your feedback.
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.
Scott
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.
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.
Thank you for the great article.
I have translated the article into Japanese and put it on my blog.
You can read it at:
http://notonlyfilemaker.com/2015/04/virtual-list-in-three-easy-steps/
Thank you very much for this perfect explanation of the VL technique which allowed me to reopen an old project that needed multi column reports. Seems that VL would be the solution, BUT, is it possible that the virtual lists behave differently with FMP 9 and FMP 10?
While using exactly the same script in FMPA 10 (running on OSX) returns exactly the lists I expected, with FMP 9 (running on Windows) the lists contain an error.
Example: every list item is a concatenation of three field values put together during the loop.
The correct list created by FMPA 10 (OSX) goes:
value1 & “: ” & value2 & ” – ” & value3
value1 & “: ” & value2 & ” – ” & value3
value1 & “: ” & value2 & ” – ” & value3
(…)
The wrong list created by FMP 9 (Win7):
value3 // WHAT’S THIS ???
value1 & “: ” & value2 & ” – ” & value3
value1 & “: ” & value2 & ” – ” & value3
value1 & “: ” & value2 & ” – ” & value3
(…)
Finally, the script to define the $$variable is this:
List ($$column01; If (
IsEmpty ( Cal_TGS::event ) ;
” ” ;
TextStyleAdd ( Cal_TGS::eventOra; Bold) & “: ” & Cal_TGS::event & ” – ” & Cal_TGS::eventDescription
)
)
Any information about incompatibilities of virtual lists and FileMaker 9 ?
Greetings from Milano
” 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” I love it.
Great resource thank you!
Anders,
Would this be useful for creating a “spot broadcast order”. My FM Solution is a Production Order system for taking spot underwriting orders that have a contracted start date and end date. Some orders run a month, some for several months, out to even 1 year. I need to have a grid that is grouped 1 week per line, (each line representing the M-Sunday dates of the contract run dates) with a column for each day of the week Monday-Sunday and a weekly Sub-Total, and within each field a number field. The rep can then enter the number of spots that he/she is ordering for each day…and the weekly sub-totals will add to a grand total of spots ordered. It’s dynamic because of the variations in run dates from order to order…so I’m kinda stuck. Any advice would be appreciated.
Hi. We need a Single Report with Multiple Child Table Records .
We have 1 Master table around 5 child tables . We made relation with all child table using primary key “Project ID”.
We have around 500 Project Records. Now , we want to display individual project information with their related child table records.
We want to display data in report like
Master Table – Project >>
Child Table 1 – Set Of Records
____________________________________________
Child Table 2 – Set Of Records
______________________________________________
Child Table 3 – Set Of Records
______________________________________________
Child Table 4 – Set Of Records
______________________________________________
Child Table 5 – Set Of Records
Can we achieve this by using virtual list technique . Please advice.
Your article reads:
“This loop will fill in the first column values with numbers, by virtue of the GetValue function.”
Yet, what precedes this statement is the the loop code that doesn’t make use of GetValue.
What am I missing?