This is the second post in the “Stumbling into FileMaker: Finding Your Footing” series.
Other posts in this series:
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.)
I built a database for a client that holds student information. In an effort to conform to the normalization rules, I created many narrow tables (narrow tables are tables with fewer fields). Instead of having fields labeled “Parent1”, “Parent2”, “Parent3” in the students table, I have a contacts table. This holds one record per contact, and it relates back to the students table.
All over this solution are similar narrow tables. One table holds the adults that are assigned to the student in a join table called “Assignments”. This pairs the ID_Student with the ID_Employee. So if a student has a speech therapist, that record will be in this table, and will link the two people.
The problem came when one of the report requirements was that we needed a list of students and all the adults (teachers, therapists, etc) that have been assigned to them going across the row. Basically, this is a cross-tab report, and extremely difficult to do in FileMaker. — Oh boy!
Another report required a lot of data about a student on one row, albeit a very wide row. I was to include parent contact information, teachers, classrooms, therapists, any accommodations that the student has, all on one document. And, as you might recognize, all of this data came from different tables. — Yikes!!
There were many more reports that were similar in nature: data from all over the database. I suppose I could have picked one table and based a layout on it, relating all the other tables back to it, but that seemed like extra work to build a layout with portals showing the related data (in some cases one field of one record). There’s also the consideration that portals should almost never be used for reporting.
I wracked my brain quite a bit about this solution. I kept putting off developing these data-from-multiple-table reports until I could figure out what to do…
The Virtual List technique finally came to me as I was doing something else. Does that ever happen to you? You are doing something else and the solution to a problem comes to mind? For me its often when I wake up. I have a solution to the problem even before I find my iPhone at the side of my bed.
The only uses I had seen and had used of the technique was to sum up data and put that sum in the row. For another project, as an example, I had to find the sum total of each product code in the catalog across all warehouses and show the total next to the product code. This was a great use of the technique.
It dawned on me that virtual lists are all about combining data into one report. This could be the total of a product OR it could be simply data spread all over the database. A virtual list could simply be collecting all the needed related data and spitting it out onto one report.
About Virtual Lists
To summarize a Virtual List: it is a table with any number of fields. The fields are set to pull data from a list into the appropriate columns.
I set up my table with these fields:
- ID field: Numerical field, auto-increment serial.
- StartData: global text
- RowData: a calculation field that parses the StartData field into rows.
Substitute (GetValue (StartData ; ID) ; "|"; "¶" )
- Column1: Unstored calculation, text field. This field gets the first row of the RowData field
Substitute (GetValue( RowData ; 1 ) ; "," ; "¶")
And so on, for as many columns as I needed. In this table, I created 200 blank records. Once the data from my script populates the StartData field, the RowData fields will parse the data into appropriate rows. Finally, each record field will get the appropriate data from the RowData field. Here’s a diagram showing the process:
Other notes about a Virtual List:
- You have to have enough records to cover any found set possible. For this table I created 200 records, more than enough for the 115 students.
- The serial number must start at 1. We want each row to take its proper data.
- You can use this same virtual list (and same gathering method) for any report. In fact, I use this “Generic Virtual List” table for ten other reports. Each report has its own layout, and thus its own placement and formatting of the fields.
In the next post we’ll take a look at how this technique is used to solve my reporting problems.