ExecuteSQL, Level: Advanced, Version: FM 12 or later

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.

9 thoughts on “More Fun with Value Lists, part 1”

  1. nice examples !

    What strikes me the most is the difference in timing between the evaluation of the script parameter attached to the onObjectEnter script trigger and the triggering of the actual script.

    The script attached to the “onObjectEnter” trigger is only triggered when we exit/close the popup menu, but this demo proves that the calculation in the script trigger is evaluated when the object is entered. Madness :)

  2. Nice job!

    But I’d suggest you to get back to Script Trigger approach for cross-platform compatibility. The script step Go to Object/Field does not popup menus on Mac OS. It requires second click on the arrow to popup the menu. John’s variation works well on Mac OS.

    1. Hi Alexander,

      You’re right that I did not test my file on the Mac. Thanks for keeping me honest.


  3. As usual, Kevin, a really useful article, which be of great benefit to me. Is there a ‘best’ way of getting new data into the ValueLists table? E.g. when George gets added to the database, presumably there has to be a separate update of ‘ValueLists’? Likewise with deletions and amendments? Or am I missing something?
    Thanks, as ever.

  4. Hi Dave, my expectation would be that this technique is for values that don’t exist elsewhere in your solution — as opposed to mirroring entries from another table. Not saying you couldn’t of course (in which case I’d use a script trigger and utility relationship to “push” the entries from the main table into the VL table), just that I probably wouldn’t use the technique this way.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.