Skip to main content

Virtual List Basics

The concept

The basic trick behind virtual lists is the wonderful GetValue() function. GetValue() takes two parameters:
  • A list of return-delimited values
  • A number specifying which value in the list to get

For example say you have a field in a single record called “List of Values” and it contains the following:

When that record is selected, GetValue ( MYTABLE::List of Values ; 4 ) will return “Doorknob”.

The brilliant idea is to replace the list of values stored in a field with a list in a global variable.

The basic implementation, part one

Create a table called VIRTUALLIST. In it, define these two fields:
  • VALUE NUMBER: a number field
  • Value_calc: calc field returning text value, = “GetValue ( $$VALUES; VALUENUMBER )”. Make sure that this value is an unstored calculation.

Go to the layout for the VIRTUALLIST table and create some records. Later you can create hundreds or thousands, but right now just ten will do. Use ReplaceFieldContents to populate the VALUE NUMBER field with consecutive values: 1, 2, 3, etc. up to the number of records you’ve got.

Finally, write a little script with just one step:

Set Variable (
   $$VALUES ; 
   “Beans¶Catsup¶Hamburger¶Hot Dog¶Lettuce¶Mayo¶Fries”

And then run the script. If you view the variable in the Data Viewer you should see

   Hot Dog

Et voilĂ ! You’ve created a virtual list. I'm not talking about the list inside the global variable. I'm talking about the records in the VIRTUALLIST table on the layout you're looking at. You might have to refresh your screen to see the results. (Switch into layout mode and back into browse mode.) But once you’ve kick started the calculation, you’ll see what’s happened. If you’ve got at least seven records created and numbered 1, 2, 3, 4, 5, 6, 7, the calc field “Value_Calc” now shows a different element of a healthy lunch, taken from the values in the global variable. For example, the record whose VALUE NUMBER = 5 will display “Lettuce” (one of the more optional elements of a good lunch, in my opinion).

Basic implementation, part 2

Now so far we’ve done only half of the technique. The other half is to show those values in a portal instead of looking right at the virtual list table.

Why does this matter? In a sense, it doesn’t. In fact, you could use the virtual list on its own layout to create some complex printed reports. But the virtual list displayed in a portal can be a uniquely useful alternative to a regular list.

Here’s how you set this up. Create a second table called VLGLOBALS with just 1 field: MAXVALUES_global. This should be a global number field.

Go to the relationships graph in the Define Database dialog and create a relationship between VLGLOBALS and VIRTUALLIST, like this:


Translated into English, the VLGLOBALS table will be linked to the Virtual List table when MAXVALUES is greater than or equal to the value number field in the virtual list.

Save your way out of the relationships graph and go to the layout based on VLGLOBALS. Lay down a portal based on the relationship you just created. Define the portal to show at least seven values and put just one field in the row: VIRTUALLIST::Value_calc.

CLARIFICATION: In the screenshot immediately above, you'll see that my relationship graph is using the anchor-buoy system. You might also notice that my calc field is named "VALUE•" rather than "Value_calc." So on the layout based on the globals table, my portal will actually show the field "vlglobals.VIRTUALLIST::Value•". If you use anchor-buoy, then of course you'll put the field from the related buoy table in the row of your portal.

Create a record in this layout. Make sure the field MAXVALUES_global is visible and put “10” into it. As soon as you do that, you should see the portal populate with your seven values from the virtual list.

Now change the value in MAXVALUES to 5. The last two rows in the portal (Mayo and Fries) will disappear.

Do you see what I see?
No, you don’t. Of course in normal lists, users can have their own found sets.  But virtual list technique relies entirely on global variables and global fields, so here too each user’s list may be different from every other user’s.

What I’m skipping

Basically that’s it. You’ve now got a virtual list working. Of course, I’m skipping over a few things. Okay, I’m skipping over a two-day seminar’s worth of stuff. But what I’m skipping is sort of incidental to the basic idea explained above. Nevertheless, I do want to mention a couple things here quickly.

You can, indeed, you probably should script the creation of the records in the virtual list table and you can autopopulate the values. There are three gotchas to worry about. First, make sure that table has at least as many records as you’ll need for any given list. If the list has 87 values but the table only has 50 records, the portal ain’t gonna display values 51–87. Second: make sure that there are no gaps in the virtual list’s VALUE NUMBER field! Don’t ever delete a record there or script some other way to audit the continuity of the numbering. If there’s no record whose VALUE NUMBER = 17, you’ll never see value number 17 in your portal! And third: be careful that none of the values you gather themselves containing return characters. The global variable is supposed to contain a return-delimited list. You may think that you're adding value twenty-two to the list, but if the source field that's supposed to be value 22 contains three paragraphs, it's going to end up becoming values 22, 23 and 24. If you need to gather values that might contain returns, you'll want to replace the returns with temp characters when you put 'em into the global variable, and then turn the temp characters back into returns in the calc formula for the field in the VIRTUALLIST table. [See addenda below for another and perhaps simpler way to finesse this problem entirely.]

And of course you absolutely should use a script to gather values in a found set and store ‘em in the global variable. Indeed, as I’ve just shown, setting up the basic virtual list is actually pretty easy. The hard part is in the scripting that gathers values into one or more global variables.

For example, let’s assume you have a third table CUSTOMERS and you want to create a virtual list showing customer names. You’d go to a CUSTOMERS layout, find the customers you want, then run this script:

Go to record [ first ]
   Set variable [ $$VALUES ; CUSTOMERS::Name ]
      Go to record [ next ; exit after last ]
      Set variable [ $$VALUES ; $$VALUES & “¶” & CUSTOMERS::Name ]
   End Loop
   Set field [ VLGLOBALS::MAXVALUES_global ; ValueCount ( $$VALUES ) ]
Go to layout [ VLGLOBALS ] // where the portal is displayed

And hey, presto, Bob’s your uncle!

I’m skipping one other thing: why virtual list is uniquely useful. But that requires its own section.

What’s really neat about virtual list

If you just wanted to show a found set of customer names in a portal, there are easier ways to do it! Use a global field as the left hand key of a relationship, put the record IDs into the global and you’re done. (You can gather the IDs in a looping script or by using the list summary field or even with SQL.) Fast and easy, quick and dirty, home before dinner.

But the problem with that technique is that the right-hand side of the relationship is always records in a single table. So your portal will always be a flat list. It’ll show customers okay, like this:

Bob Bobbit
Cathy Callow
Dave Davison

But what if you want to see something like this:

Bob Bobbit

Cathy Callow
   • March 2015
   • October 2015
   • February 2017
Dave Davison

Example of use of virtual list from our criminal defense app Acquittal. This is a sort of menu, showing the misdemeanor box for a client (my dog Lulu, as it happens). Clicking on the > disclosure triangle causes the two cases in the box to be displayed. Each row in this little list is clickable and will take the user to the appropriate child record or grandchild record. It's a bit like an outline, in that it allows you to drill down into related tables without leaving your current context, which can be confusing to users.

Or something even more complex, like this:

   Home: 12345 Dandelion Way, Dallas TX 75218
   Work: PO Box 50346, Dallas TX 75218
   iPhone: 214.555.3535

That last example is from a database where people are in one table, addresses in another table, and phones in a third table. Larry has one inactive address and two old phone numbers that we don’t want to display here so the script that gathers the values for the virtual list simply skipped them. In this virtual listing example (as in the screen shot from Acquittal, above), each line is a separate record. So I can click on Larry Lawrence’s name to see his person record; or I can click on his iPhone number and go right to that record in a different table. And since the name value is actually in a different field from the other values, it can be formatted differently: bold, or all caps or a colored fill.

This is where the virtual list technique shines. Remember the portal that points to your virtual list is showing values from the VIRTUALLIST table — not from CUSTOMERS or INVOICES or anything else. It’s up to you how you populate the records in the virtual list table, or rather, how you populate the global variable $$VALUES. The script that populates that variable could involve loops within loops. Send it a specific customer ID (say, Cathy Callow’s) and when it’s looping through the customers list and the ID matches the script parameter, you jump over to Ms Callow’s related invoices and gather them up; then go back to customers list and finish up. The script could even jump to a completely unrelated table and gather up new values from an unrelated table.

And the virtual list table doesn’t have to have just one Value_calc field. It can have two, or three, or five. They don’t even have to be named Value1_calc, Value2_calc etc. If you find it easier, you can name ‘em Name_calc, Address_calc, Phone_calc. Of course each of those will be referring to a different global variable ($$NAMES, $$ADDRESSES, $$PHONES), but that’s legal. Use the hide property to hide or show the right field from the virtual list in each row of your portal. Of course, keeping your virtual list design generic or abstract will make it more easily portable. But if you do start to make more complicated virtual lists, you may find it easier to understand what’s happening in a moderately complex script if things are named fairly obviously. At least that works better for me.

Using a similar approach, you can display virtual lists in your portal with what appear to be subsummary parts. You can even create a calendar view of dated records using the virtual list technique. A neat thing about using a virtual list to create a calendar is that you don’t have to create dummy records for dates that don’t have events. You just create a virtual list value for the date and move on.

A final plus about virtual lists: Since everything is done in memory, they're very fast. Nothing gets written to a record or a field at any time.

Addenda (one day later)

My presentation to the North Texas FileMaker developers group today was a lot of fun — with lots of good comments, as always. I want to mention one in particular. After I mentioned one of the gotchas with virtual lists (viz., returns in individual values!), Heather McCue offered a way to avoid this problem: use a repeating global and put values into different reps instead of a single-rep global with return delimited values. I gave it a quick try as soon as I got home and it's brilliant.

Note, if you do this, you won't be using GetValue() any more to grab the values in the global variable. And unfortunately you can't use GetRepetition() either because, at least as of FileMaker 15, GetRepetition() only works when you aim it at a field. You can't use it to get a rep from a repeating variable. So the calc field in your VIRTUALLIST table needs to use a formula that looks something like this:

Evaluate ( "$$VALUES" & "[" & GetAsText ( VALUENUMBER ) & "]" )

Which isn't all that hard to do and works perfectly. Now my entire presentation of virtual lists above was based on the miracle of the GetValue() function and it turns that function might not be necessary after all. But everything else works as I said mutatis mutandis ("after you make the necessary adjustments").

The other addendum is simply to give credit where due. It's my understanding that the virtual list idea originally the brainchild of Bruce Robertson, someone I've been learning from for fifteen or twenty years on various FileMaker lists.


Popular posts from this blog

Setting up OAUTH with Google in FileMaker 16

Setting up OAuth with Google in FileMaker 16Posted by William Porter Intended audience: Intermediate to Advanced FileMaker developers Date of publication: 2017-June-06
Updated: 2018-June-06
One of the many exciting features in FileMaker 16 (released May 2017) is OAuth or Open Authentication. Open Authentication allows users to connect to a FileMaker database after authenticating with an external (non-FileMaker) account. At the present time, FileMaker supports OAuth through Google, Amazon and Microsoft.
If you're a developer there are two main questions to answer. First, should I do this? And second, how do I do it? I'll answer the first question later. It's important.
But the other question--How do I setup OAuth?--is answered in the attached document. I wrote this tutorial with the help of my friend and colleague Taylor Sharpe of Taylor Made Services, also here in Dallas. We provide step-by-step instructions on how to get your users authenticating into your FileMaker databas…

Getting out of fullscreen mode in FileMaker Pro

In version 2 of Acquittal, our criminal defense case management app, we're doing some nifty stuff with windows, and that means we're finding out that even in FileMaker Pro 15, we still don't have quite all the tools we'd like for managing windows.  One problem is how to trap for the possibility that the user has switched into fullscreen mode. The other problem is how to get out of it.

Is this window in fullscreen mode?
This one's not too hard. This calc formula seems to do the trick:

Get ( WindowHeight ) = Get ( ScreenHeight ) and
Get ( WindowWidth ) = Get ( ScreenWidth)

That will return true if the window is in fullscreen mode, false if it's not.

Why does this matter? Because there are certain things that you can't do with a window if it's in fullscreen mode. In Acquittal, for example, there are times when we want to generate a second "sidecar" window, then display the main and sidecar windows side-by-side. Can't do it if the main window …