A few months ago I mentioned “dwindling value lists” in passing, and said I would do a proper article on them at some point in the future. Well, then FM 12 was released and I went on an ExecuteSQL binge, so I’m just now getting around to honoring my promise.
Dwindling Value Lists (DVLs) are value lists that shrink up, by removing individual list items as they are selected. They can come in very handy when you need to schedule resources and want to prevent double booking. This example comes from one of today’s demo files, Dwindling Value Lists, and shows how a DVL might be used to schedule employees for a work shift.
Essentially, a DVL is a filtered value list that updates in real time (or something very close to real time), and this is done by building a multiline key of selected values, and then filtering the VL to only show remaining eligible values.
This is a somewhat unusual demo, because it actually explains what’s going on behind the scenes, not leaving me a whole lot to write about in today’s posting. For example, here is the relational basis for the dwindle action.
Here’s the definition of dvl_key — it’s an unstored calculated text field, and the zero is necessary to get the value list to initially populate when the portal is empty:
To ensure the value list always shows eligible choices, we use a script trigger to give FileMaker a little kick in the pants.
And incidentally, you are welcome to convert this demo to FM 12, and it will work identically there as well.
But of course if one is going use FM 12, then one is naturally tempted to use ExecuteSQL (you didn’t really think you were going to make it through this article without me mentioning ExecuteSQL, did you?), and this demo (DVLs via ESQL) shows an approach one might take.
In a way, it’s the reverse of what we did in the previous demo, because this time we accentuate the positive, building a multiline key of eligible ids, as opposed to ineligible ones, which means the relationship between schedule::dvl_key and employee::id can be a standard equijoin.
Here’s the definition for dvl_key, and you may note that it contains a “subquery” (a second SELECT statement). In plain English: “Build a multiline key of employee IDs that have not already been entered into the portal for the current schedule.”
Well that about wraps it up for today, but we’re going to end with something a bit whimsical… so far we’ve only looked at dwindling value lists with regards to drop-down lists. I can’t think of any serious use for the following, but as a proof-of-concept, or if you enjoy a childish prank (I know I sometimes do), you might want to download and play with Shootout at the Checkbox Corral (requires FM 9 or later).