Disclaimer: the techniques shown in this article are provided on an as-is basis. Use with appropriate caution and at your own risk.
Acknowledgements: today’s article would not have been written without the inspiration provided by Bruce Robertson, John Ahn (FM 12 ExecuteSQL Unconference Session), Andries Heylen (Magic Value Lists), Marcelo Piñeyro (How to Extend FileMaker Value List Sort Capabilities) and Jonathan Fletcher (It’s Sorta A Value List Thing).
Introduction
Last year, in Custom Field-Based Value Lists, we looked at a technique to enable single-column field-based value lists to display their contents in entry order, as opposed to standard index-based alphanumeric order. For example using the contents of this field (from a single-record table) as the basis for a value list…
…we came up with a way to sort our value list (a.k.a. “VL”) items like so…
…as opposed to what you would get with a standard field-based VL, i.e., this:
I ended the article by saying…
Today’s article looked at value lists based on return-separated values in a single field. In an upcoming article we’ll explore implementing a similar technique for sorted two-column value lists based on entries in a dedicated value lists table where each list item lives in its own record.
…and now, nineteen months later, am ready to pick up where we left off last time, and to avoid repetition, will assume the reader is at least somewhat familiar with the issues and techniques covered in that article (Custom Field-Based Value Lists).
Note: if you aren’t clear on why a 2-column value list might be preferable to its single-column counterpart (or to a custom value list for that matter), check out Thinking About Value Lists, part 1.
Demo Files
- 2-column value lists, v1.zip — independent value lists
- 2-column value lists, v2.zip — dependent (cascading) value lists
Demo 1
The aim of this demo is to generate VLs that are both 2-column and custom-sorted — i.e., something that FileMaker does not natively support, and therefore (as we saw last time) requires a work around to accomplish.
As is often the case with 2-column VLs, the first column will be hidden. In reality we are going to store the ID from the first column, but the user will only see values from the second column.
Note: In this demo the VLs are “independent” in the sense that choices made in one do not affect entries in any of the others.
We begin with a “code” table with one entry per VL…
…and a dedicated table for our VL items. If you’re curious about that horizontal separator line (VL0022), it’s a string of eleven Unicode “box drawing light horizontal” (U+2500) characters, which can be generated in FileMaker via Char ( 9472 ) .
Each entry in the valuelist table is assigned to a particular VL via the code, and the “rank” field allows the user to specify the sort order. Here’s the relevant portion of the relationships graph.
To achieve the custom sort, we use a dedicated “display” field for the second column, which prepends byte order marks (BOMs) to the item, as was discussed in detail last time.
Next, in the filter table we have three unstored “key” fields, each corresponding to one of our VL codes…
…and which are related to the valuelist table like so:
Note that it does not matter what table these “key” fields live in… as long as the fields are unstored (or global), the resulting VLs will be “portable”, i.e., usable from any layout context in the current file.
Here are the VL definitions.
All three are configured similarly:
Note: This auto-enter calculation ensures that the user cannot input the separator line in the Country value list as a data element. (We can’t prevent them from choosing the entry, but we can immediately nuke it if they do.)
To sum up, the value lists in Demo 1 are based on entries in a dedicated “valuelists” table. Each VL requires its own relationship keyed to the appropriate code, and the sorting occurs in the valuelists table.
Demo 2
In Demo 1 the focus was on a technique for custom sorting 2-column value lists. In this demo, we’ll use a modified version of that technique for the Country value list, and explore some other interesting value list tricks as well.
I use the term “cascading”, because values in lists 2, 3 and 4 are determined by the entry in the preceding field. For example…
This time our “code” table contains these entries, corresponding to VLs 1, 2 and 5.
The Country and Region VLs are both derived from entries in the valuelist table, which this time does not have a calculated “display” field (since the sorting will be handled elsewhere)…
…with regions linked to countries via id_parent.
After choosing a country and a region, the user can pick a winery…
…which lives in its own dedicated table…
…and then, you guessed it, once a winery has been chosen, the user can specify a product…
…which also lives in its own dedicated table.
And here is the relevant portion of the relationships graph for the preceding.
Actually, what you see when you display the Product VL depends on whether “Display as” is set to this…
…or this…
Also, there’s one exception to what I said a minute ago about VL entries in lists 2/3/4 being determined by the entry chosen from the preceding list. You may want to see all the wineries for a given country, in which case, leave Region empty, and the VL will display all wineries for that country with the region shown in brackets.
Needless to say, a value list based on values that live in different tables is not something you see very often. The region lives in the valuelist table, and the winery name of course lives in the winery table. Normally to accomplish this would require a) redundantly storing region names in the winery table, and b) defining a “helper” field to concatenate the winery and region names, but that’s not happening here.
For that matter, the “product” VL is similarly enhanced, representing the concatenation of three separate fields in products, without the need for a schema-polluting helper field to bring those three values together.
Under The Hood
In this demo the heavy lifting is accomplished using variations on the approach we used in Custom Field-Based Value Lists demo #5. Specifically, we use a combination of Hide Object (not to actually hide anything, but to instantiate $$variables) and a new table, virtualvaluelist (a.k.a. “VVL”).
The Hide Object calculations are attached to the input fields here.
The $$vars will contain lists of values, e.g.,
…which will be rendered into discrete entries by calculated fields in the VVL table, with pairs of fields corresponding to column 1 and column 2 of each value list.
Here are field definitions:
Do we need to add a new pair of fields every time we want a new value list? Absolutely not — these fields and the VLs they represent are reusable, and we only need as many pairs as we have VLs displaying concurrently on any one layout. Since there are five VLs on the “filter” layout, we need five pairs of fields.
All of the value lists “start” from filter, reach across a cartesian relationship, and pull their values from fields in virtualvaluelist…
…specifically, like so (detail shown for “virtual.a”, but all five are configured similarly).
In a nutshell, all the value lists in this demo are based on fields in virtualvaluelist, and these fields contain values that are drawn from $$variables, which, as mentioned a moment ago, are populated via Hide Object. Let’s take a look at the first one.
The $$variables for each VL column take the form of $$vvl_x1 and $vvl_x2, where “x” represents some letter of the alphabet corresponding to the VL it forms the basis of (and also to the corresponding column names in the VVL table).
If — as in this case — the 2nd column of the value list will be custom sorted, we will append “.sorted” to that variable name, and ensure the regular (non-sorted) equivalent is empty, as per the…
$$vvl_a2 = ""
…in the above example.
The basic idea is that for any given field where this technique will be used, either the regular “x2” variable or the “x2.sorted” variable will be populated, but never both at the same time. One of them will always be empty (and the underlying “x2” field in the VVL table relies on this being so), e.g.,
Note: prepended BOMs break type-ahead so we only want to use them when necessary.
The remaining value lists are not sorted. Here’s the Hide Object syntax for Region.
And here’s the Winery syntax, which is more complex since when Region is empty, the VL will include the region in brackets (by grabbing the corresponding item entry from the VL table).
For just a moment, let’s skip the Product VL and take a look at the “Display as” VL, which, as mentioned, corresponds to this entry in the code table…
…and these entries in the valuelist table…
…and this Hide Object syntax.
As mentioned previously, the choice made here affects what you see when the Product value list appears.
Because we might add additional display options, and because I was curious whether it was viable, I decided to move some of the logic into related note records. And what are they related to? The entries for “Display as” (code = 5) which live in the valuelist table.
(Note that the term “year” equates to the “vintage” field in the Product table.)
So circling back around to the Product VL, the Hide Object syntax is simplified here since it reaches into the target note record to figure out what actually needs to happen. My nickname for this is “abstracted SQL” since we are using SQL to locate runtime code which is also written in SQL.
A couple final comments regarding this demo: First, auto-enter calcs are used on the input fields in the filter table a) to clear out downstream fields if an upstream field is cleared or modified, and b) to reduce the likelihood of garbage being entered. There is more work that could be done in this area to make data entry more robust — currently the demo prevents multiple entries, and strips out the separator line if a clever user attempts to choose one from a value list.
Second, because the VL $$variables are populated via ExecuteSQL, several of the TOs and most of the joins on the Relationships Graph are not actually required for the technique to work. The only required join on the graph is this one:
These TOs and joins…
…enable us to see related data on the valuelist, winery and product layouts. They can be removed (at the cost of reducing comprehensibility), so that the above becomes this…
…and the value lists will continue to work. Try it on a copy and see for yourself (or check it out here: 2-column value lists, v3.zip).
A Few Things To Keep In Mind
1. Max # of rows in virtualvaluelist table must be >= total number of rows you want to display in any of your value lists
2. Your data must be in first normal form, i.e., single values only in your data fields. Hard returns in any of the referenced fields will cause misalignment chaos.
3. However, empty column 2 values will not cause any misalignment, because the SQL queries in today’s demo return “gaps” as empty rows.
4. Type-ahead works with popup menus on the Mac, but not on Windows.
Why Bother?
1. As already mentioned, avoidance of field-level schema pollution in your system.
2. Safe for live development, since you can create complex value lists without defining new indexed fields. (For one reason why you might care about this, see Live Development + “New Record”.)
3. If your system connects to an externally built FM system, i.e., one where you have the ability to access (but not modify) schema, this technique enables you to build value lists in your system based on multiple fields in that external system, as opposed to having to ask the developer of that system to define helper fields for you.
Why Not Use JSON?
I wanted this technique to be available to as many FM developers as possible, i.e., to be as far-backward-compatible as possible (since it relies on Hide Object, you must use FM 13 or later). Updating this technique to use JSON, the While function, and/or any other recent additions to the FileMaker tool suite is completely viable, and will be left as an exercise for the reader.