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…

• Thinking About Value Lists, part 2 demo 1
• Thinking About Value Lists, part 2 demo 2
• Thinking About Value Lists, part 2 demo 3

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