This is the second post in the “Stumbling into FileMaker: Finding Your Footing” series.
Read the other post in this series: What to do with the Web Viewer
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.)
There are lots of places to read about Virtual Lists and learn about it. What I’d like to do today is explain how I finally understood its value and used it to solve many reporting needs. I’d like to share with you some pitfalls I encountered along the way and the steps I took to resolve those problems.
Scenario
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.
Problem(s)
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.
Stressing
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…
A Solution
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.
View the other posts in the “Stumbling into FileMaker: Finding Your Footing” series:
Suggestion: When I create the virtual list, I do a FIND on the data, and a Get (FoundCount) returns the number of records I need for the report. A couple more steps, but dynamically allocates the number of required records, rather than an arbitrary number.
Hi Kirk.
You’re right. That’s a good alternative. I wonder if it could significantly slow down a report if you have to create 200 rows each time.
Blank records are cheap in terms of space and time and caching, so it probably doesn’t hurt a system to have those rows already in there.
I tested it, and yes. it SIGNIFICANTLY slows it down. Creating the blank records up front is the only way to go.
Thank you for this. I love the process diagram, it makes the logic very easy to follow.
You’re welcome Chris.
I’m a teacher by training so I tend to want to spell things out clearly (at least I always hope I do). I use this picture to remind me of the process as well. 🙂
Hi Jeremy,
I use this technique for virtual lists as well as for dynamic value lists.
I Set the ID field as an stored calculation field with the formula Get ( RecordNumber ) instead of Numerical field, auto-increment serial. I do so in case a virtual list’s table record is accidentally deleted.
I would like to know your opinion on this aproach.
Kind regards,
Miguel
Hi Miguel.
Thanks for reading.
I think your idea is just fine to use. In my cases, the user has no possibility of deleting the virtual list rows. The rows are either in a report that is immediately printed or is in a portal. In all instances, the user does not have the opportunity to interact with the records; its merely read-only. Since Virtual lists are based on unstored calcs already, I wish to minimize the number of other un-stored calcs.
Hi Jeremy,
I do so just to avoid resetting the ID counter before creating records after having copied the virtual list table to a new solution file.
Thank you for your reply.
Kind regards,
Miguel Angel
Hello and thank you very much for Virtual List technique. I made a small modification
I name my columns “COLUMN01″…”COLUMN99″ and I have this calculation in each field.
So I dont need to change the calculation, but only the last two digits of FieldName for it to work
GetValue ( Substitute ( __ROW_DATA;”|” ;”┬╢”) ;Right ( GetFieldName ( Self ); 2))
I do so, but with a slight modification:
GetValue ( Substitute ( __ROW_DATA ; “|” ; “┬╢” ) ; Filter ( GetFieldName ( Self ) ; “0123456789” ) )
So you do not have to care about the number length of the field’s name.
Kind regards,
Another approach is to loop in the script to add or delete records from the table until it matches the number needed. I also use Miguel’s Get ( RecordNumber ) approach. So much simpler.
Howdy.
I’ve chosen to keep a virtual list table with a set number of records simply because adding a loop to create records there is just another thing to do in the script. Also if you use virtual lists in such things as portals, showing related summary data on the fly, it would cause further delay to have to create the new records every time.
As a colleague says: fields are cheap and records are cheap. If you have a table sitting around with empty records, that doesn’t add much to your file, and it is ready at a moment’s notice.
But adding records to the virtual list table does work. Since there’s many ways to solve the same problems in FileMaker, it is left to us to decide. I’ve chosen to keep the table at the ready.
I have a question. I work at a University with graduate students. I have created a database to hold all of the information on my students with a separate table containing faculty info. I started using filemaker 16 only three weeks ago and have been learning as I go. This week, I’m at the point where I must learn about relationships. I am trying to show the relationship between students and faculty. Faculty can be related to students as advisor/coadvisor or committee member. I used a portal on my master layout to define when faculty are advisors/coadvisors to students. I used a join table called studentsfaculty and included four fields, facultyid, studentid, advisor and coadvisor. In the portal I created a value field to get names from the faculty table. Very cool. Worked like a dream. It is sortable and reportable. Now I’m trying to figure out how to include committees…..each student has at least two committee members and they would come from the same value list. I’m stuck on whether I need another join table, use the same studentfaculty table or whether I just need a field in the portal called committee and continue the same portal; whether this will still be sortable. I am currently using a checkbox in the portal to distinguish advisor/coadvisor status. Could i just add another checkbox called committee and call it a day? Id like to be able to report on a faculty member and see how many students they advise and how many they are on committees for. At the same time, I want to be able to see each student and their advisors and committee members. I hope this makes sense. Like I said, I’m a very new newbie.
Thanks for any help…
Hello Mariette, Sorry for the late reply. Let me study this and get back to you.Feel free to email me as well at jbrown@soliantconsulting.com
Pingback: Native FileMaker: More than Meets the Eye - Geist Interactive
Pingback: A FileMaker Summary Table: Exploring the Idea of Summarizing Data