More Fun with Value Lists, part 1

One of the things I love about FileMaker is how many different ways there are to skin the proverbial cat. Following our recent look at “Magic Value Lists,” I received thought-provoking FileMaker 12 demos from John Ahn and Dr. Osamu Noda.

I’m going to save Dr. Noda’s files for next time, but today we have two variations on a demo which resulted from a discussion with Nick Chapin on FileMaker TechNet. Actually, the first variation is from John (lib_valueLists_john_ahn_mod), and the second is an alternate approach that I came up with. If you’re a TechNet member (registration is free), you can read the original discussion here. At any rate, the objectives were:

1. Store the entries for all value lists in a single table (thereby allowing authorized users to construct and maintain value lists using name-value pairs, without having access to FileMaker’s “Manage Value Lists”)
2. Use UUIDs
3. Use popup menus
4. Have the value lists work properly in Find mode
5. Value lists should be “portable” (i.e., accessible from any table context)

Let’s start with John’s version.

This is the value list table:

Here’s the main screen in browse mode…

…and here it is in layout mode…

If we unstack the overlaid objects we will note that…

a) the same field appears twice in each row, first formatted as a popup menu, and then as an edit box (all four popups are attached to the same value list):

…b) there is a script trigger attached to each popup,which invokes an ExecuteSQL statement like so (the final argument, i.e., the value list “name”, will vary depending on which popup is clicked):

…and c) the overlaid merge variables function to show the selected list item. The reason they are necessary is that any given popup menu is only valid while it is being accessed. The value list is created “on the fly” at runtime, but the merge variables are kept up to date thanks to this bit of conditional formatting cleverness:

John’s demo makes use of a utility table to construct the value list…

Here’s the Relationships Graph (note the cartesian join between the two utility TO’s):

And, finally, here’s the value list definition:

And it certainly gets the job done, providing much food for thought on the way.

I wondered whether the Utility table could be eliminated, and if so, if it would actually produce a simplified demo, or would merely result in shifting the complexity from one schema domain to another. Whether the trade offs were worth it is open to debate, but for what it’s worth, this (lib_valueLists_kevin_frank_mod) is a variation on the above approach.

The following relies on a behavior that is specific to the Windows platform. If you deploy on the Macintosh this technique is not for you.

The main differences between John’s version and mine are:

a) The utility table has been eliminated

b) I renamed some of the fields in the ValueLists table (to make them easier for me to understand)…

c) I added an unstored calculated text field, VariableKeyUnstored, to the ValueLists table.

d) The Relationships Graph looks like this:

e) The value list is defined like so:

f) There are buttons, rather than script triggers, attached to the popup menus. Here’s the button attached to the first popup:

g) And finally, here is the script:

Well, today we’ve seen two methods whereby one value list can do the work of many. Next time, we’re going to see zero do the work of one, when we examine Dr. Noda’s value list-less approach to value lists.