Virtual Lists and ExecuteSQL

But first, a side note

The past three years I’ve attended DevCon, I’ve devoted all my time to interviewing well known FileMaker people and FileMaker vendors.  And I’ve enjoyed the experience immensely.  But for DevCon 2018, I made a change:  I collected business cards of people I wanted to interview, and I am scheduling them throughout the coming year.  Look for HOnza Koudelka, of 24U Software, in October.

I have been doing FaceBook live, but continued problems with audio reproduction leaves me no choice but to go back to doing recordings from Zoom and posting them later to FaceBook, Youtube, and here.  So be on the lookout as these interviews come down the pike.

Finally, Virtual Lists and ExecuteSQL

One of the many sessions I attended was Martha Zink’s Virtual List and ExecuteSQL.   I thought I had a pretty good understanding of both, but Martha taught me a lot.   Here’s her description of the session:

If you’re ready to take your reporting to the next level, you’ll want to explore the virtual list technique. From complex reports to pivot tables, a virtual list gives you the flexibility to make your own rules on what the report can do. While you can use a List Of summary field or a looping script to gather your data for a report, you also have the power of the ExecuteSQL function to gather, and even summarize, your data. Combining these two techniques provides you with a way of creating unique and flexible reports.

The session moved fast and covered a lot.  I wanted to wait until the video of the session came out, but decided I could add it later.  And you will be able to follow the slides while working with the four free FileMaker downloadable files (Note:  Since FileMaker switched to a new community website, most videos and links are broken.  This link downloads a zip file with all of the downloads from that presentation).

ExecuteSQL

When I started using virtual lists, I found the data I needed and looped through the found set to make a list.  ExecuteSQL eliminates that part but providing the list (and sums, etc), which is much faster.  Add the ability to do this using Perform Script on Server (PSOS) and the speed increases dramatically.  And with FileMaker’s list parsing capabilities, populating the virtual table is virtually automatic.

Do yourself a favor and study this technique.  In Martha’s words, it will add a hammer to your toolbox.

Source: ADV03 – Flexible Reporting with Virtual Lists a… | FileMaker Community

Football is like life – it requires perseverance, self–denial, hard work, sacrifice, dedication, and respect for authority.

~Vince Lombardi