Virtual List on Steroids

[Note 1: as always a huge thank you to Bruce Robertson, for inventing virtual list, and for many other contributions to the FM community over the years.]

[Note 2: the following assumes the reader is familiar with basic concepts presented in Virtual List Reporting, part 1.]

This is a follow up article to document new material from my recent FM-DiSC presentation: Virtual List on Steroids. The presentation was intended for developers who are (at least) somewhat familiar with virtual list, but for those not actively using virtual list, I decided to begin with a demonstration of “the problem” (or one of the problems at any rate) that virtual list was invented to solve.

Demo file #1: Step One – Admitting There’s A Problem

This is based on “Report 1” in Virtual List Reporting, Part 1, where we have a simple sales table…

…containing these fields…

…and we have been asked to produce a report that looks like this:

While there are of course various ways to skin the proverbial cat, back in the “dark ages” (i.e., before virtual list) we most likely would have added these highlighted fields…

…more than doubling the field count in this table, and effectively “polluting” our schema, simply to produce one basic report (shown here in layout mode):

As I say, the purpose of this demo is merely to illustrate “the problem”, and I stopped here, but at the risk of stating the obvious, no “helper fields” were added to produce this same report in Virtual List Reporting, Part 1… it’s Report 1 and you can see for yourself.

Meanwhile, life marches on, and subsequent reporting requirements might include adding more columns as per the next screen shot, which, going the traditional (non-virtual-list) route would entail adding 7 more calculated fields as well as 7 additional summary fields…

…and why stop there? You could be asked to add rolling 36-month averages for each of the above columns (i.e., more schema tampering)… etc., etc., etc…

…well, you get the idea. And if these challenges look familiar that’s because they were solved without touching table schema in Virtual List Reporting, part 1 (Reports 4 and 5).

Comparison of the Two Approaches

Demo file #2: VLR, Part 2 for FM-DiSC

The original version of this file (from Virtual List Reporting, part 2) contains ten reports, but here I’ve added an 11th report to illustrate some ideas I felt had been glossed over previously.

A field for GDP (gross domestic product) has been added to the States table…

…and a new table, Countries, has been added as well.

Have you ever heard someone say, “if California were a country its economy would be ranked number (whatever) in the list of largest countries by GDP”? Well here’s an opportunity to test that assertion, because you’ve been tasked with creating a “Top 20 Economies” report, interleaving data from the Countries and States tables as appropriate. And of course this sort of thing is a piece of cake for virtual list since it doesn’t care where the data comes from.

Here it is in layout mode…

And here’s the report:

A few points worth mentioning: we use ExecuteSQL to grab the top 20 values from each table, and the TextColor function to make the state entries red. (For readability, static code has been used… in the real world I would employ robust coding practices to prevent accidental breakage due to field and/or TO renaming.)

Note: GDP data is stored in billions, but we want to display it in trillions, hence the ” / 1000″ in the two SQL statements.

At this point $rawList contains 40 entries…

…and from here we…

1. render all 40 entries via virtual list
2. sort descending by GDP
3. omit all records where record number is greater than 20
   (note that we are talking about record number here, not ID)
4. display the report

Okay, time to declare a victory, right? Not quite… now we’ve been asked to sort the report by the country or state, like so:

This poses two new challenges:

1. How to capture and display the ranking… in Report 11 we used the {{RecordNumber}} symbol, but that won’t reflect the rank if we change the sort order
2. How to sort the report on a field repetition higher than 1

…which bring us to Report 12.

Here it is in layout mode (only the rank column has changed):

This report essentially picks up where Report 11 left off, so we’re only concerned with the second half of the script. Here’s the relevant portion ($col_1 and $col_2 vars have already been populated):

The basic idea is that having already sorted the virtual list table in descending GDP order, we can “walk” the records in the found set, pushing the ranking for each row into a new $col_3[rep] as per steps 42-46.

And the trick to sorting on cell_text_r[2] is documented above in lines 48-52 (for more information see Sorting on a Field Rep > 1).

Key takeaways from Reports 11 and 12:

• our data can come from multiple, unrelated tables
• we can do some of the heavy lifting in the virtual list table (sorting, tweaking the found set, populating additional variables) after the data has been parsed… as opposed to doing all the work prior to parsing the data.
• it is possible to sort on a field rep > 1

Incidentally, the GDP data was obtained from Wolfram Alpha, and I was pleasantly surprised to discover that they allow you to easily copy data in delimited form to use elsewhere as follows:

Demo file #3: Multicolumn Virtual List in Browse Mode

This is based on a demo and article I did back in 2014: Summary List + Virtual List.

The challenge was to display the current found set of customers in three columns in browse mode.

Here’s what’s going on under the hood.

You can read the original article for full details; here I’ll just point out that…

A. the “3-ness” (expectation that three columns are all we’ll ever want) is hard-wired into the field definitions, and…

B. the field names make it clear that we only plan to display customer data using this technique

So much for the original demo. In the updated version, we still want to support our three-column customer view, but in addition we have images (in an “artwork” table) linked to some of our customers…

…and currently our users can only see them as tiny thumbnails in a portal.

But this is about to change. We have a request to display a customer’s artwork in a separate “lightbox” window in either four-column…

…or five column views.

This gives us an opportunity to revisit the virtual list table and set things up in a more extensible manner. First let’s allow the first “word” of the layout name to dictate the number of columns.

Now we can re-define the foreign key fields so they configure themselves based on the current layout name + field name (self-awareness re: the latter allows the same definition to be used for all ten “col_id” fields).

And finally let’s beef up the relevant TOG (table occurrence group) like so:

Note: we’re only using the first five “col_id” fields at this time… the second five are in anticipation of future needs.

Demo file #4: Date Filtration, v2

This demo extends a technique we first explored in Modal Popovers + Magic Date Value Lists in 2016. In the original demo, a list of dates is constructed on the fly, rendered via virtual list, and used as the basis for a “magic” value list, which, being based on a date field, displays values in ascending date order.

Now, in the current demo, the challenge is to take that same list of dynamically-generated dates, and have our value list present them in descending order.

And as you may have guessed, this is accomplished via Byte Order Mark (a.k.a. “BOM”) trickery… specifically, like so:

In the virtual_value_list table, define a new field, array_date_desc…

Note that a) the default language is set to Unicode, and b) this is an unstored calculated text field… it has to be text because you can’t prepend BOMs in a calculated date field.

Next define a new value list, “mvl date sorted”…

..and attach it to the pop-up menu here:

If a two-column value list seems like overkill, bear in mind that we want to capture a pure date (i.e., the unseen column 1), but the sorting magic happens via the visible 2nd column which exists for display purposes only.

---

Here are links to all resources that were referenced during the presentation.

Virtual List Articles

• Long Documents in FileMaker 11
• Conditional Subsummary Report in Browse Mode
• Virtual List Reporting, Part 1
• Virtual List Reporting, Part 2
• Virtual List Reporting, Part 3
• Modal Popovers + Magic Date Value Lists
• Virtual List Table of Contents
• Virtual List Charts, Part 1
• Virtual List Charts, Part 2

Related Articles

• Fast Summaries
• Multi-Find (Fast Summaries Revisited)
• Custom Paper Size Trick (PDF Trickery)
• Byte Order Marks (It’s Sorta A Value List Thing — thank you Jonathan Fletcher)

Custom Functions

• SetVarByName
• ValuePosition
• AntiFilterValues (thank you Bruce Robertson)
• CustomList (thank you Agnès Barouh)

Other

• WolframAlpha