Using the Virtual List Technique – Part 2

This is the third post in the “Stumbling into FileMaker: Finding Your Footing” series.


Other posts in this series:

You’ll recall that in the previous post, I discussed some reporting problems that had me stumped for awhile. The Virtual List technique became the solution.  Here is what I did to use this method to solve my reporting problems.

Hunter/Gatherer of Data

Aha! I had stumbled on a solution. So I began to write scripts. I named these scripts “GatherData_” with the prefix because that’s what they were doing–gathering data from all across the galaxy of the solution.

Here’s what my script does:

  1. It goes to the students table and finds the appropriate students.
  2. It loops through each student, grabbing the relevant data. Sometimes this is done using ExecuteSQL. Other times it is simply grabbing data from the table or related data.
  3. All the data gets put into its own variable. For this, I have $Name, $StudentInfo, $Parent1, $Parent2. I’ve also put labels at the front of the data in some cases.

The $StudentInfo variable gets set using this calculation:

Let ([
_Houses = ExecuteSQL("
SELECT EmployeeName FROM ASI__Assignments WHERE ID_House= ? AND ID_SchoolYear = ? AND Role = ?";" ";" & "; STU_ACD__AcademicYears_Current::ID_House ; STU_ACD__AcademicYears_Current::ID_SchoolYear ;"Houseparent");

_Classes = ExecuteSQL("
SELECT EmployeeName FROM ASI__Assignments WHERE ID_Class = ? and ID_SchoolYear = ? AND Role = ? or Role = ?";"";" & "; STU_ACD_ENR__Enrollment::ID_Class ; STU_ACD_ENR__Enrollment::ID_SchoolYear ; "Teacher" ; "Co-Teacher") 

];

Substitute ( 

List (
"Admit Date: " & STU__Students::DateStart ; 
"Age / DOB: " & STU_PEP__People::Date_Birth &" / " & STU_PEP__People::Age;
"State / School District: " & STU_PEP__People::State &" / " & STU_SCD__SchoolDistricts::DistrictName ; 

"Class / Teacher: " &  STU_ACD_ENR__Enrollment::ClassName &  " / " &  _Classes ; 

"House / HouseParents: " &STU_ACD_HOU__Houses::HouseName &" / " & _Houses 

 ) ; "¶"; ",")

At the bottom of the loop, I combined all that I had already gathered with these new pieces of data. Here is the code that I wrote:

$All = List ($All ; $Name &"|" &$StudentInfo &"|" & $Parent1  &"|" &$Parent2 )

This is listing the value of $All from the previous loop with the new information for the current record.

When the loop was done, when the loop had gathered the data for each student in the list, I had a full variable called $All of all the data: Student name and all the assigned adults. It contained, in this order, student name, teacher, speech therapist, and physical therapist. It was a list that looked something like this (for two rows):

Brown, Jeremy|Admit Date: 9/8/2015,DOB / Age: 8/9/2006 / 9,State / School District: NE / GI Public Schools ,Class / Teacher: Class 3 / Wim Decorte & Dean Wiseman, HouseParents: Woodlodge / Mislav Kos||”

Tasi, Victor |Admit Date: 7/8/2013,DOB / Age: 8/9/2006 / 9,State / School District: MN / Applevalley ,Class / Teacher: Class 5/ Fred, HouseParents: Meadowsweet / Mike Duncan||”

Examination

In each block of text, I am using the pipe “|” as a separator between the columns in my virtual list (see the calculation for the RowData field). The comma in the blocks will be converted to a return character. See the calculation for Column1.

Also notice in the first row the teacher is actually two people: “Wim & Dean”. I collected that back up in the let statement of the $StudentInfo variable using ExecuteSQL.

Finally, do you see at the end of each block is two pipes: “||”. That means some piece of data did not get found in the system. That’s okay. There must be no parent data, BUT the columns are set because the pipes delimit each column whether or not there was gathered data.

Side Note: My Stumbles

Be careful with the delimiters; pay attention. ExecuteSQL’s default column delimeter is the return character when returning more than one record. That wouldn’t work in this case. Jeremy Brown has two teachers in this class, so ExecuteSQL will return “Wim ¶Dean”. The ¶ is used elsewhere, so I couldn’t have it be the default.

Instead I used “ & “. So when returning the teacher’s names, it returns “Wim & Dean” for Jeremy. For Victor it will return only one teacher: “Fred”.

Once the data was gathered, I went to the Virtual List layout created earlier and plopped the contents of the $All variable into my VirtualList::StartData field. This global field then gets parsed into the correct rows for each record:

VirtualList:: RowData = Substitute (GetValue (StartData ; ID) ; "|"; "¶" )

And finally, each column of that record gets the appropriate row of the RowData field, as defined by the “¶”.

The magic of the Virtual List is that by placing the contents into a certain spot (in my case the global field, VirtualList::AllData) all the data spreads out into the ordained columns perfectly. I get the result I was looking for in the exact places it needed to be:

Screenshot of the Virtual List in Layout mode
Virtual List in Layout Mode

“Brown, Jeremy” goes into column 1. The Student info goes into column 2, parent 1 goes into column 3 and so on.

This really solved the problem I was trying to deal with: How to build a report with data spread all over the database into different tables.

Virtual lists are a great tool to allow a developer to keep his/her normalized database intact and still be able to build and display reports gracefully. These reports can be totals, or they can simply be displays of related data, all together into one report.

A note about speed: Since this report is generated on the fly, it does take some time to gather all the data together. It isn’t a report that is just already there when a user goes to the layout. So I had to keep that in mind. For this issue, of course, I’ll turn to Perform Script On Server and use that to actually gather the data to return for use in the Virtual List. I did incorporate a progress bar into this script. I used the amazing one found at:

For my clients, this progress bar became a great relief as the data was being generated.

Finding My Footing

Even though I knew about the technique, it took a real-world problem for me to stumble upon its usefulness and the concept behind it. Now I’m able to use this technique pretty quickly. I believe I’ll use this technique and these “gather” scripts as a template for other reports. I encourage you to give it a try. Stumble into it as I did (I made plenty of mistakes along the way) and recover and add it to your bag of tricks!


View the other posts in the “Stumbling into FileMaker: Finding Your Footing” series:

3 thoughts on “Using the Virtual List Technique – Part 2”

  1. Strangely enough I have just completed my first cross-tab reporting using the same methodology after also being inspired by Bob Bower’s DevCon Video. You may enjoy the current Discussion on the FileMaker Community; ‘SQL vs native filemaker’ which touches on the speed improvement of using repeating variables instead of getValue(myList;N) which is slower, the longer the virtual list. I must investigate further…

    1. HI. Thanks for reading!
      I’m glad you were inspired by that DevCon video. Its a great one.

      I’ve skimmed that article on the FM Community and will take a look at it in depth.

      I bet you’ll start using this method quite a bit; the concept is universal and easy to adapt for other reports.

  2. Hi,

    Thank you for the article, I was in fact looking to use executeSql for my reports.

    A sample filemaker file with this feature implemented would be of immense help.

Comments are closed.

Are You Using FileMaker to Its Full Potential?

Claris FileMaker 2023 logo
Scroll to Top