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:

• What to do with the Web Viewer
• Using the Virtual List Technique – Part 1

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

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:

“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:

• FileMaker Pro 13 Tip-n-Trick: V3 FileMaker Pro Progress Bar

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:

• What to do with the Web Viewer
• Using the Virtual List Technique – Part 1