Thinking About Value Lists, part 1

In July and August we explored several esoteric value list techniques. This time around, and over the next few postings, we’re going to step back from the cutting edge, identify some common value list challenges, and propose some solutions to those challenges. A few thoughts before we begin:

1. Some of the material in this series will be beginner-level; some will be either intermediate or advanced, depending on your point of view
2. Value lists are subtle and multifaceted; to get them to do what we want, we sometimes have to move beyond the obvious
3. As often happens in FileMaker, there are many ways to skin the cat
4. I plan to explore only a few of these ways
5. But will do so in microscopic detail

Now on to our first demo (Thinking About Value Lists, part 1 demo 1), which contains a table of employees with office sizes. Here it is in layout mode…

…and here it is in browse mode, and you’ll note that the office_size field is populated via a pop-up menu…

…which is tied to a value list based on entries in a Values table.

A Digression or Two…

Of course out in the real world, one probably wouldn’t dedicate a table called “Values” to a single-purpose value list. However, in this demo it helps make the point clear that the values come from records in a table created specifically to store value list values.

But, why use field values at all? Why not use a custom value list instead?

That’s a fair question, because custom value lists do have certain advantages, including:

1. they are easy to set up
2. they allow you to easily specify the order of list items, without having to resort to heroic measures or trickery

…but custom value lists also have some drawbacks, the biggest being that they are stored as schema rather than as data, so when you update a client’s database by importing their data into your new improved schema, you will lose any changes the client may have made to their custom value lists, unless you take special steps to prevent this from happening. E.g., you could manually update custom value lists in the new file, or you could attempt to use FMrobot, but unfortunately it has not been updated since FM 10. You could also employ a trick I picked up from Lynn Allen back in the 1990s, and use a separate file for custom value lists… perhaps that will be the subject of a future article.

At the risk of digressing from what was already a digression, you will note that accounts/passwords, and “next serial number” values are also stored in schema, but at least FileMaker offers script steps to facilitate the transfer of these items from the client’s source files into your shiny new schema (and for the sake of completeness, I should mention that custom functions live in schema also, and while there are no script steps to assist in their transfer, at least they can be copied and pasted).

Bottom line: there is no built-in or obvious way to automate the transfer of custom value list contents from one file to another.

And with that, we’re done digressing, and ready to move on to…

Challenge #1

Here are all the records in the Employees table:

Word has come down from corporate HQ that “Enormous” is not an approved office size designation; the proper term is “Gigantic”. So, you make the change in your Values table…

…but of course modifying a record in Values doesn’t magically update corresponding existing values in Employees, does it? You’ve still got “Enormous” where you should have “Gigantic”.

Perhaps it’s worth mentioning that if your system uses radio buttons instead of pop-up menus, at this point Office Size will appear to be empty for the first three rows, since “Enormous” is no longer in the Values table.

At any rate, you are going to need to fix it. So, you manually update the three records…

…and then you notice that the modification fields have updated to reflect this. While technically correct (the records did change after all), from a business perspective, it may not have been desirable for modification data to change under such trivial circumstances.

So, what can be done to prevent this from happening? Well, you could…

1. Implement Nick Orr’s amazing “selective modification” technique on both the timestamp and account modification fields, and then use a script to update the office size replacement, or…
2. Export the original data to another file, modify it in the other file, and then reimport it, telling FileMaker to not perform “auto-enter” operations when the dialog appears asking you if you wish to do so, or…
3. Manually turn off auto-entry on the timestamp and account modification fields, make the changes to office_size, then re-enable auto-entry…

Or you could have designed the system differently in the first place, to use a two-column value list, as per today’s second demo file (Thinking About Value Lists, part 1 demo 2).

In this demo, as you might expect, the ID field in Values is a standard auto-enter serial number, and the “office size” value list is defined like so:

As a reminder, in demo #1, there was an “office_size” field in the Employees table…

…but now, in demo #2, we’re going to instead store the ID of the value, rather than the value itself, so the field is “id_office_size”.

Since the value list is set to only display the second column, and since our field is formatted as a pop-up menu, our users will be none the wiser…

  …but let’s be clear that the visible text is an illusion, and what we’re really storing are foreign keys (which correspond to the primary keys in the Values table).

So, getting back to our challenge (change “Enormous” to “Gigantic”), this time when we edit the value in the Values table…

…we return to Employees to discover that nothing else needs to be done, and since no data has been modified in the Employees table, the modification fields are undisturbed.

Challenge #2

Another decree from on high: as a cost-saving measure, “Gigantic” (formerly known as “Enormous”) offices are being phased out… all employees are being downgraded one size, with those currently occupying “Small” offices to be moved into new “Tiny” ones. This is an across-the-board mandate that affects all employees, no exceptions.

So, in demo #1, we would first update all four records in the Values table like so:

…and then manually change office_size for every record in Employees, inadvertently updating all the modification fields in the process.

But in demo #2, guess what? We simply update the values in the Values table…

…and that’s all there is to it. It’s way less work, and the modification fields are untouched.

Closing Thoughts

In closing I want to point out that this “store the first column, but display the corresponding value from the second column” behavior works with pop-up menus, radio buttons and check boxes. It does not work with drop-down lists, which are happy to show you the second column while dropped, but reveal the ugly truth when they are at rest.

And one thing I particularly like about pop-up menus under this scenario is that if one of the records is somehow deleted from the Values table, it becomes immediately apparent in Employees.

In part 2 we’ll look at some of the nuances associated with filtered value lists.