FileMaker and the Virtual List
The virtual list technique has been around quite a while, and is very useful for displaying information from multiple tables in one cross tab report. Jeremy Brown of Soliant wrote a two part series recently on how he applied the virtual list to a problem he faced:
FileMaker is a great platform. With its user interface capabilities and by following the rules of database normalization, a developer can create a well-designed system pretty quickly.However, reporting is not so much fun. This is not, of course, a fault of FileMaker. It is simply that often, properly normalized data is harder to report on. Trying to combine normalized data into a workable report can be pretty tough. And in FileMaker, using unstored calculations to aid in reporting could possibly degrade performance.The Virtual List technique, developed by Bruce Robertson, has been around for quite awhile. I myself have used it here and there. At my first Developer Conference in 2012, I attended a session on Advanced Reporting Techniques, and the Virtual List was the star of the session. (I’ll be honest, I didn’t understand much of what was said at the time, but after reviewing the notes and video quite a few times, I finally understood.)
What I found interesting was the updated techniques Jeremy used to implement the virtual list: Let Statements, ExecuteSQL, and, to speed it up (virtual list reports are notoriously slow), the Perform Script on Server (PSoS) script step. I like that last one–I just transitioned a client to FileMaker Server from a FileMaker Client host (with 8 users, and their performance issues were a major reason for the change), and I am planning to change the virtual report to run using PSoS in the near future.
Read both posts–it’s worth it for the next time you need a virtual report.
Source: Using the Virtual List Technique Part 1
Source: Using the Virtual List Technique Part 2