Level: Intermediate, Version: FM 8 or later

Thinking About Value Lists, part 2

In part 1 we explored a particular type of value list: the field-based two-column variety, based on all values, and set to show values from the second column only…

…and today’s article assumes familiarity with that material. This time around we’re going to look at some challenges and issues that can arise when using filtered (a.k.a. “conditional”) field-based value lists, and propose some solutions to those challenges.

I don’t claim that any of the following is particularly original or brilliant, but I do a fair amount of team programming, and these issues come up over and over again.

The value list (a.k.a. VL) issues we’re going to examine are:

  • Active vs. All
  • Portability
  • Find mode vs. Browse mode vs. “Edit mode”

…and it seems to me that these issues overlap in messy ways, as opposed to being clear cut and discrete, but sometimes life is like that. At any rate, in all of today’s imaginatively titled demo files…

…we have a table of employees, some of whom are active, and some of whom are not:

And here it is under the hood:

During data entry, we need to access a popup menu of currently active employees from three table contexts: purchase orders, invoices and employee evaluations, like so:

A minute ago I mentioned the concept of “portability”. Within a given file, a VL may be considered “portable” if it works everywhere, i.e., from any layout/table context. One of the nice things about VLs based on “all values” is that they are accessible everywhere.

So if we want a 2-column VL of all employees, the most natural thing in the world would be to define it like so.

And along the same lines, there’s nothing to stop us from defining two new fields in the employees table…

…which gives us this…

…and then creating a value list for active employees based on those two fields:

And you can see that approach in demo #1. I don’t find the idea of defining two new fields every time I need a conditional value list to be particularly appealing, but a) that’s how we used to have to do it circa 1995, and b) sometimes quick and dirty gets the job done.

Another reason I don’t like this approach is that it may not scale well if your conditional filtering requirements are more complex. At any rate, I include this method for the sake of completeness and as a nostalgic reminder of simpler times.

One final point perhaps worth making: there are no relationships in demo #1. Each table occurrence on the graph is an island unto itself. The 2-column VL takes care of “the magic” of showing the correct employee name on the P.O., Invoice and Evaluation layouts, although we’re only storing the foreign key (id_employee).

Let’s move on to demo #2, which implements a method I like a lot, and when I use the term “portable value list”, this (or some variation) is typically what I have in mind. Demo #2 is almost identical to demo #1…

…the main difference is in how the “active employees” VL is constructed. This time, there actually is a relationship on the graph…

Since the VL is equally viable from the standpoint of P.O.s, Invoices and Evaluations, I decided to base it on a “disinterested third party” table, in this case “dev” which happens to be a development table with zero records… just a place to park global fields and unstored calcs.

As you might expect, the foreign key (employees::flag_active) is a standard indexed number field. The primary key (dev::constant_unstored) is an unstored calc (= 1), but a global field or a calculated field with global storage would have also worked. But note that a standard number field, or a stored calculation would not work as a primary key in this scenario.

And here is the VL definition:

Now it turns out that both demos 1 and 2 suffer from a certain shortcoming that, till now, I have conveniently been ignoring, namely this: During data entry, we only want to see active employees on the popup menu, but when reviewing historic data, we want to see all employees.

Unfortunately, since the value list is defined to only show active employees, and since we’re relying on the value list (as opposed to dedicated relationships) to translate id_employee into the corresponding employee full name, here’s what we see:

Furthermore, in Find mode, we’d probably like to be able to search on all employees, not just active ones…

…which brings us to demo #3:

Here’s how the employee popup behaves during data entry…

…but in Find mode it displays all employees…

…and in Browse mode, with the popup at rest, all employee names can be seen.

The “secret” (in case it wasn’t already obvious) is to overlay two popup menus like so:

And I think that’s about enough for today.

9 thoughts on “Thinking About Value Lists, part 2”

  1. hi kevin,

    it is always a pleasure to read your blog!
    one idea which strokes me is that, if i don’t have the time right now to read and understand completely i’d like to do that later. later means when i’m away and sometimes w/o an internet connection.
    so, can you imagine to publish a pdf or the like of each article for reading on i-devices?


    1. Hi Egbert,

      Thanks for your kind words re: FileMaker Hacks, and I appreciate your suggestion.


  2. Kevin,
    Very interesting and informative article about value lists. Definitely helps give the user flexibility without destroying ay data. The only issue I see is that the list shows the names based on the ID numbers which just happens to correspond with the the names being alphabetical already. How do you get the Value list to appear not only for active employees but also alphabetical based on say last name?

    1. Hi Chris,

      Thanks for taking the time to post a comment.

      I’m not sure I correctly understand your question. All employee value lists in all the demos for this article are set to sort on the second field, which is a calculated concatenation of last name, first name.

      The fact that the records in the employees table happen to be in alphabetical order in their unsorted state is coincidental, and has no effect on the value list sort order.


  3. Hi Kevin,

    Thanks for the tip. It worked like a charm for me after trying several other techniques. I can’t believe how much time I spend on this issue : )

  4. I have been cracking my head over this for hours. Thank you for this post! Now I just have to be sure I fully understand it so I can repeat it in the future! :)

Leave a Reply

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