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).
Hi Kevin
I really appreciate these FileMaker Hacks, especially the ones about ExecuteSQL. I recently discovered that the free (open source ) Base Elements plugin form Goya Ltd also includes ExecuteSQL for FMP11, including support for UPDATE INSERT and DELETE.
This seems to work nicely.
Anyway, one suggestion for ExecuteSQL that can be especially useful is for compiling a list of values to use in a Chart. As you know charts are way better in FMP12 than the were before, but I think it’s most convenient to use delimited lists as data sources, rather than a reference to a range of records.
And this is where ExecuteSQL comes in, because it allows you to retrieve data from all the distinct corners of your database without all the necessary TOC’s, and present everything in a neat dashboard like layout.
thanks for the good work!
Hans Erik
You’re very welcome. I agree that SQL and charting are a powerful combination. Thanks for taking the time to write.
— Kevin
Hi Kevin,
Thanks for your series of articles on ExecuteSQL. I was working with this particular example and found an issue that causes the multiline key to be empty. In my example I’m creating records via script into a join table and then wanting to populate the foreign id after the join record has been created.
Using your example if you have a record in the line_items table which has a value for <> field but not the <> field the <> field is empty.
I’m new to SQL myself and was wondering if you had any suggestions about how to work around this?
Hmmm looks like wordpress didn’t like my use of merge field style syntax for the field names. My middle sentence should read:
Using your example if you have a record in the line_items table which has a value for id_schedule field but not the id_employee field the dvl_key field is empty.
Having since discussed it with you offline, it appears that adding this highlighted line to dvl_key will do the trick:
Thank you for this post. Exactly what I was looking for. I’m new to FMPro and was completely at a loss as to how to pull this off.
Many thanks for this refreshing article. You have saved me tons of time and made my solution MUCH more efficient. At the end of the day it is so simple, but thinking up simple solutions is the hard part!