Virtual Lists – “Let’s start at the very beginning…”

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

GetValue ($$PEOPLELIST;823)

  • 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.

The Case function in a calculation

A recent post on the TechNet forum, plus a succession of typically helpful answers, provided a very useful tutorial in how best to use the Case function to calculate a marked up cost, with the markup being based on the value of Cost.

Here’s the original formula.  “Why won’t it work?” was the cry for help:

Case (
Cost < “5”  ;   Cost * “4”   ;
Cost > “5.01” and Cost < “15”  ; Cost * “3.5” ;
Cost > “14.99” and Cost < “25”  ;  Cost * “3”  ;
Cost > “24.99” and Cost < “40”  ;  Cost * “2.75” ;
Cost > “39.99” and Cost < “60”  ;  Cost * “2.5” ;
Cost > “59.99” and Cost < “100” ;  Cost * “2.25” ;
Cost > “99.99” and Cost < “200”  ; Cost * “2” ;
Cost > “199”; Cost * “1.6”)

Well, with the field “Cost” defined as a number, and the calculation result defined as a number, the first step is to remove the quotes, which makes Filemaker see the numeric values as numbers, rather than as text.

So, V2:

Case (
Cost < 5 ;   Cost *4   ;
Cost > 5.01 and Cost < 15  ; Cost * 3.5 ;
Cost > 14.99 and Cost < 25  ;  Cost *3  ;
Cost > 24.99 and Cost < 40  ;  Cost * 2.75 ;
Cost > 39.99 and Cost < 60  ;  Cost * 2.5 ;
Cost > 59.99 and Cost < 100 ;  Cost * 2.25 ;
Cost > 99.99 and Cost < 200  ; Cost * 2 ;
Cost > 199; Cost * 1.6)

This will now work, but it’s making much harder work of the calc than it needs to.  Filemaker only works through the Case statement until it meets a true condition, then it bails out – there’s therefore no need for all of these > and < evaluations.

So, enter V3:

Case  (
Cost  <  5  ; Cost * 4   ;
Cost  < 15 ; Cost * 3.5 ;
Cost  < 25 ; Cost * 3   ;
Cost  < 40 ; Cost * 2.75  ;
Cost  < 60 ; Cost * 2.5 ;
Cost  < 100 ; Cost * 2.25  ;
Cost  < 200 ; Cost * 2   ;
Cost * 1.6)

(Note the final  default value, 1.6, which applies to any value of Cost equal to or greater than 200 – no need for an evaluation there; if we get that far, that’s the value to use.)

The next efficiency is to use calculation variables, for the value of Cost, and the markup to be applied.

So, finally, V4:

Let (  [
$Cost = Cost ;
$Markup  = Case  (
$Cost <  5  ;  4 ;
$Cost < 15  ;  3.5 ;
$Cost < 25  ;  3 ;
$Cost < 40  ;  2.75 ;
$Cost < 60  ;  2.5 ;
$Cost < 100  ; 2.25 ;
$Cost < 200  ; 2 ;1.6)];
$Cost * $Markup)

But then came another interesting point, i.e. whether the Cost field itself is the right place for the calculation to happen.  In the case of a “net price” such as this, which may need to be overridden, the better way is to have a separate Markup field, defined as an auto-enter calc, with the initial value of the markup amount calculated as above.  The (new) Price field is then defined as a simple calc field, i.e.  Price = Markup * Cost.  On the Markup fields, the option “Do not replace existing value of field (if any)” should be ticked, to prevent historical and “override” data being replaced.

For a more comprehensive look at calc and auto-enter fields, see here.  Thanks, as ever, to the Technet contributors who combine to make it such a great resource.

Layout Tip – Tidy Pop-Ups On iOS

Pop-up menus in Filemaker Go are great, but have an annoying behaviour.  If, after selection from the menu, you tab automatically to a text field, the on-screen keyboard is displayed.  This is ugly.  You can, of course, remove the pop-up field from the tab order, but this has the effect of leaving the menu displayed after item selection – also ugly.  The solution is to have an OnObjectModify on the pop-up field, which just does a Commit Record.  This has the effect of removing the pop-up menu, which, combined with removing the field from the tab order, gives the calm, elegant behaviour that we seek!  (Thanks to Mike Mitchell on Filemaker Technet for pointing this out.)

How Best to Handle People’s “Attributes”?

In the process of re-writing “FOCUS” in FM12, I’m experimenting with different ways of recording people’s attributes.  I’ve realised that there’s logically no difference between a person’s gender, and the fact that s/he likes football – both of these can be regarded simply as attributes, i.e. “IsFemale” and “LikesFootball”.  In terms of reporting and counting, I think it will be much easier to treat all attributes equally, i.e. that a person either has them or doesn’t.

A while ago, I documented the way that I’ve  been handling simple “on/off” attributes, which I’ve called “tags” in the current version of the system.  Here’s that blog post – it seems to work pretty well, and the users have said that they like it.

However, because this deals only with “on/off” tags, the technique needs a bit of refinement – if I’m going to use it for things like gender, sexuality, ethnicity, etc., I need to build in the “switching off” of any currently held setting.  After all, if a person is already on the system as “female”, and we realise that we’ve recorded their gender wrongly, we can’t simply add the “IsMale” attribute – we also need to remove “IsFemale”.

So, on the Attribute record, in addition to holding the Attribute itself (“IsMale”, “Likes football”, etc.), I’m now also storing an indicator, “IsExclusive”, and an attribute type (“gender”, “sexualilty”, “tag”, etc.).  If the “IsExclusive” indicator is set to 1, it means that if a person has one attribute of this type, s/he can’t hold another.

I have to say that all this seems a bit over-complicated when the alternative is simply to have a “gender” field, possibly with a couple of Boolean calcs (IsMale and IsFemale), to be used for counting and reporting.  But there’s a real attraction in having all attributes in one place, and handled in the same way.

If you have any thoughts, opinions, or experience in this area, please comment!

Keeping Text Fields Tidy With Custom Functions

Having said in the previous post that I’m trying to use FileMaker’s own feature set wherever possible, there are some areas where it just ain’t good enough.

One such situation is in the formatting of text data entered by the user, especially name and address details.  For instance, if you want all names to held in “proper” case (e.g. “Jane Smythe”, rather than “JANE smythe”, “jane Smythe” or any other permutation), the “Proper” calculation function is available – you just need to make your field an auto-enter calc, with the calculation defined as “Proper (Self)“.

But this is very limited, and can actually create mistakes in data that has been correctly entered.  For example, if your user has carefully entered “John O’Shea”, she’ll be cheesed off when FileMaker changes it to “John O’shea”.  Likewise “Mary Macdonald”,  and “Bbc”.

For a more thorough treatment, I’ve had to look beyond FileMaker to a couple of Custom Functions, both found on Brian Dunning’s excellent site.   The CFs are:

  • Title, by Howard Schlossberg, which does a great job of putting capital letters where they should be, leaving them as caps where they should be, and making them lower case where appropriate.  It’s also quite adaptable, so you can add exceptions to be treated differently, etc.  It looks like Howard created it to format titles (of albums, books, etc.) properly, but it works just as well on names of people, organisations, etc.
  • SuperTrim, by Debi Fuchs, which removes leading and trailing spaces, tabs and carriage returns from the entered text.  Again, like FM’s native function (in this case, “Trim”), but better.

So, once the CFs are added to the application, it’s a straightforward job to define all appropriate text fields as auto-enter calcs with this calculation behind them:

SuperTrim (Title ( Self; “”))

On/Off (Boolean) Fields

I’m really enjoying my first forays into FM12 – many things seem much easier, and often more elegant.

One example is the handling of buttons to switch a Boolean indicator on or off (on attendance registers, access indicators, etc.).

For example, I have a field called “IsDisabled” in the People table.  It’s on (i.e. set to 1) if the person is disabled, off (0) if not.  (In the old days, this was a “yes/no” field, but this caused me all sorts of frustrations and limitations – Boolean is the way to go.)  To amend and display this field, this is what I do:

  • On the layout, rather than the field itself, I have a small button.  It’s 15×15 on the desktop version of the layout, 34×34 on the iOS versions, with nicely rounded corners, consistent with the excellent River (or River Touch) theme that I’m using.


  • The button just toggles the field, e.g. Set Field [PPL::IsDisabled; not PPL::IsDisabled]
    (Note that the “not” has the effect of reversing the current setting – 1 becomes 0, 0 becomes 1.)
  • The button is filled with a suitable colour – I use a solid green, to indicate “on”, with conditional formatting to show “no fill” if the field is not set.  So the formatting is:

cond_format_ind copy

Buttons in FM12 have several states, and these can be used to fine-tune the user experience.  I choose, for example, to use the solid green colour for the “Hover” state, as well as the “Normal” state – i.e. if it’s currently “off”, it will show green when the user hovers over it, to show what will happen if s/he selects it, but if it’s “on”, it will continue to show green on hover.  When making this decision I was led by Google Apps, and it does seems the most “intuitive” way to present it.

What I like about this technique is that it’s really simple, and uses FM’s own stuff.  Others may choose to use a graphic indicator (an arrowhead maybe?), and other non-FM tweaks, as I have done in the past, but after many hours spent fiddling with “home made” interface elements which sometimes end up looking wrong (or just different) on different screens etc., I’ve resolved to use native FM features in the re-write wherever possible.

“Tidiest Drop-Downs” – Part 2, Finishing Touches

In the previous post, I documented how I plan to use a combination of drop-downs and pop-ups to get the best of both worlds, in terms of user-friendliness.  I’ll call these “tidiest drop-downs”.

But there’s one problem with the technique, which is this: because the key field is the one that the user actually enters data into, it’s possible for the user also to enter invalid data.  The intention is to limit the selection to items that are in the value list, but if the user double-clicks in the field and starts typing, invalid data may be left in the field.  (Of course the user may, by chance, enter a valid key value – just as a chimpanzee, left to its own devices, may eventually type the Complete Works of Shakespeare.  But it’s unlikely, to say the least.  And I’m not for a minute implying that my users are in any way like chimps…)  So anyway, we need to use a triggered script to make sure that this doesn’t happen.

On the key field, I’ll add an “OnObjectModify” script.  This will run either when the user selects a key value from the drop-down list, or when s/he types in the field.  The pseudocode for the script is:

Check whether the field holds a value that exists in the value list.
If so, do nothing.
If not, display an error message and offer the drop-down list again.

A couple of pre-requisites for this script;

  1. It has to be as portable as possible.  (I don’t want to have to write a new iteration of the script for every drop-down in the system.)
  2. Once the script has detected invalid data in the field, I want to throw the user back into the field, complete with drop-down list displayed (without the user having to click into the field again).

So this is how it turned out in FileMaker:

valid_dd_scriptNote that the script uses two custom functions, both of which can be found on Brian Dunning’s site.

  1. “GSP” – which gets the specified parameter from the parameters passed to the script.
  2. “FilterList” – which checks for the presence of a value in a given list.  (It does a lot more than this if you want it to, but that’s what it does here.)

Note also that you need a “dummy” field to go to, either during or after validation.  I have the dummy field (a global) parked just off the displayed area of each layout in the system – it often comes in handy.

So, the first parameter is a comma-separated list of all the (key) values in the value list that is attached to the field. (They have to be comma-separated, rather than cr-separated, so that the list will be treated as one parameter.)

The second parameter is the OBJECT NAME (not field name) of the field being validated.  We need this in order to get back to the field if validation fails.  So, for the script to work, you must give the field an object name.

So the script checks whether the contents of the active field exists in the list of values.  If it does, fine – just go to the dummy field, and end validation.

If the value doesn’t exist, then something’s obviously wrong, so show the custom dialog, initialise the field, then go back to the field in question.  Note the need to go to the dummy field first – if you don’t (i.e. if you go straight to the field being validated), the drop-down list will not be displayed – you haven’t actually left that field yet, hence the need for the little round-trip.

Note also the need to use “Go to object” rather than “Go to field”.  Although we can pass the field name as a parameter to the script, we can’t use that information once we get here, because FileMaker haven’t yet come up with a “Go To Field By Name” script step.

And that’s it.  A nicely portable validation script to help keep the solution free of invalid data.