Virtual Lists – “Let’s start at the very beginning…”Posted: November 12, 2013
“Virtual List” in Filemaker is not really a technique, but a concept. And it’s one that, although tricky to understand (at least for me!), is actually a very simple one, albeit with scope for complexity that can be rather scary. So I’m creating a series of steps, starting with this, the most simple of implementations, and working upwards in terms of complexity.
The requirement for this first example is simple: we have a table of People, and we want to produce a listing of each person’s first name. Obviously this first example does not even need a Virtual List – we could simply have a layout showing the values directly from the table – but diving straight in with a more complex example runs the risk of clouding the simplicity of the concept. (When I was trying to get my head around this concept, the complexity of available examples was a problem.)
The components required for any use of Virtual List are:
- A global variable, e.g. $$PEOPLELIST, which will contain all the values for the list, in a return-delimited list. The return-delimiting of the list means that each value in the variable will have a “value number”, and any value can be referenced using the GetValue function, e.g.
- A Virtual List table, pre-populated with the largest number of (blank) records you will ever need – i.e. the largest number of values that will be held in $$PEOPLELIST. So, for example, if you’re going to use Virtual List for a report based on People, and you have currently 8,000 People records, you should probably create 10,000 blank record in your VL table. Each record must have a sequential id, starting from 1. There must be no gaps, and records must not be deleted, i.e. there will always be 10,000 records, numbered 1 through 10000. (There is another field in the Value List record, “name”, about which more later.) Obviously, we use a script to populate the table in the first place, like this:
- A script to create and populate the variable. This will result, for example, in a return-delimited value in $$PEOPLELIST for each record in the People table. Note that, if the field you’re concerned with has blank values in any records, a blank space needs to be written to the variable. (We’ve seen that each value in the variable has a “value number”, and this will, in time, equate to the serial number of the record in the table. Hence the need for there to be no gaps.)
So far, so very simple. All we’ve done is write a variable with a bunch of people’s names – no big deal. But what’s been going on in the Virtual List table while we’ve been doing this?
Well, the vital component to the whole Virtual List concept is a field in the Virtual List table, which I’m calling “name”. This is an unstored calc field, defined as:
GetValue ( $$PEOPLELIST ; id )
So, into this field we automatically get the contents of the relevant value in the global variable. The relevant value is obviously the nth value, where “n” is the id of the record, which in turn is the number of the value in the variable. So if the 823rd person in the original file has a first name of George, because we’ve set the 823rd value in the variable to that name, we automatically have “George” in the name field in the Virtual List table. Note that the records in the Virtual List table don’t need to be written or updated – the unstored calc is automatically refreshed when the global variable on which it depends is updated.
We can now use our Virtual List table as the source for, perhaps a report, listing people’s names.
At this stage, this may seem a) obvious, and b) pointless – but stick with it! In future posts, we’ll be looking at how this can be really useful. For now, consider the possibilities if, instead of just one global variable, we have several. We may, for example, want to build a reporting table including related values (from other tables) for each person. That’s when it starts to get really useful.