Custom Field-Based Value Lists

Introduction

Does this sound familiar? You’ve rolled out a solution that includes a single-record Settings table where authorized users can enter/update various system-wide settings, including one or more fields where the items will appear in popup menus… and then comes the inevitable question: Why don’t items in popup menus appear in the order I entered them? 

It’s a reasonable question, and in the past you might have replied, “Because field-based value lists rely on a field’s index, and their sort order is alphanumeric”… but after reading today’s article your new answer will be: “No problem — let me take care of that for you.”

Interestingly, the above is accomplished with the same value list attached to all three popup menus, and no script triggers… or scripting of any kind for that matter. Read on to learn how.

Demo Files

• custom field based value lists, v1 — illustrates the problem
• custom field based value lists, v2 — an unsatisfactory work around
• custom field based value lists, v3 — another unsatisfactory work around
• custom field based value lists, v4 — a satisfactory work around
• custom field based value lists, v5 — a variation on the preceding

Demo #1

The purpose of this demo is merely to illustrate the standard behavior of field-based value lists.

Under the hood we have a single “Settings” table, with three indexed text fields to store the value lists, and three corresponding global fields that will have popup menus attached to display the value lists.

It doesn’t matter where this second set of fields lives; normally they would inhabit a different table, and we’ll see an example of this in demo 5; in demos 1 through 4 they appear in Settings as a matter of convenience.

Here are the value lists…

…which are attached to the global fields like so…

…and as we saw at the outset, unless you employ some sort of work around, field-based value lists will ignore the order of the source data and will display items in alphanumeric sequence.

Demo #2

In demos 2 through 5 we use byte order mark (a.k.a. “BOM”) trickery to tweak the sort order. The approach taken here in demo 2 is not ultimately satisfactory, but I decided to include it because it a) represents a path of least resistance one might take in an attempt to solve the problem… and b) provides an opportunity to discuss byte order marks.

In a nutshell, when you want to force a sort order in FileMaker, you can insert one or more BOM (zero-width, i.e., invisible) characters in front of a word, and those characters will be taken into account if you sort by Unicode rather than standard English (or whatever your default language may be). This process can be facilitated by employing a custom function to prepend as many BOMs as you wish.

At any rate, here in demo 2, three additional fields have been defined utilizing a pair of custom functions: PrependBOM and CustomList (click the image to enlarge it).

These are indexed calculated text fields which mirror the contents of VL_001, VL_002 and VL_003, but with invisible BOMs prepended to each item based on its list position.

So, for VL_003 (“Phases”)…

…the calculated equivalent becomes…

[BOM]Ready
[BOM][BOM]Aim
[BOM][BOM][BOM]Fire

…and the value lists are now based on the calculated fields…

…and configured to use Unicode.

So what could possibly go wrong?

Well, for one thing, when we choose “Aim” from the drop-down, we are actually choosing “[BOM][BOM]Aim”…

…in other words, polluting our data with BOMs.

Bad idea. Let’s not do that.

Demo #3

The BOMs are prepended for one reason only: to force the value list into an unnatural sort order. If we don’t want them polluting our data, let’s just strip them out with an auto-enter calc…

…and we can use the same syntax for all three:

So, problem solved, right? Well… sort of… let’s make the choice from the popup menu:

Now, unlike in demo 2, the data viewer reports all is well:

But did you notice something missing in the popup menu?

That’s right, no check mark — which makes sense when you think about it, because the field contains “Aim” but the corresponding value list item is “[BOM][BOM]Aim”.

If your conscience will allow you to live with that, then you can stop reading now. Otherwise, let’s move on to demo 4 to enjoy the BOM gain without the BOM pain.

Demo #4

As in demos 2 and 3, we have field-based value lists that respect the item order of the underlying source fields.

Let’s pick “Aim” again…

…and confirm that the data viewer is happy.

To be clear:

• The popup menus are sorting in the desired order
• We see a check mark indicating the selected value
• No invisible characters are stored (or even momentarily inserted) when we make a selection

There are several interesting things going on behind the scenes. First off, “Hide Object” is used to declare/update a global variable: $$arrayVL.

Note the “0” at the end of each statement… we are not actually hiding anything, just taking advantage of the fact that Hide Object is extremely responsive. Clicking into any of the three popup menus updates $$arrayVL with the contents of the designated “VL” source field. You can confirm this via the data viewer (put $$arrayVL on the Watch tab and click the Refresh button to see the current contents).

But value lists can’t be based on variables… or can they? That depends on how literally you interpret the question. While it’s true that you can’t directly base a value list on a variable, you certainly can do so indirectly, by basing your value list on a field which gets its values from a variable.

In demos 4 and 5, we have eliminated the existing value lists, removed the three calculated fields from the Settings table, and added a new table, VirtualValueList…

…defined like so:

Currently the table has 100 records… and, you guessed it, each record corresponds to a particular item in whichever value list you happen to currently be rendering. If you plan to have more than 100 entries in any of your value lists, you will want to add some more records to this table.

(For more information on virtual list, see Virtual List Simplified.) The good news for readers not already familiar with virtual list is that you don’t need to understand it for the techniques in this demo to work. Just make sure that the ID (serial) numbers have a one-to-one correspondence with the number of records in the VirtualValueList table, and that there are no gaps… if you have 100 records, their IDs should be 1 through 100, and the next serial value for ID should be 101.

Next, we’re going to link the two tables via a Cartesian join, and note that the field on the “Settings” side of the relationship must be unstored — for more about this see Andries Heylen’s eye-opening Magic Value Lists demo.

Correction 14 Mar 2018: the relationship must be either Cartesian or use an unindexable field on the Settings side (though there’s no harm in using both). Since I went with a Cartesian join, I could have used any existing fields as predicates.

Now we can define a new value list, “Virtual”, based on col_1_text and col_2_text, using related values starting from Settings, showing values from the second field only, and with “re-sort” set to Unicode.

FileMaker will warn you once per field that it will not work, but they’re just being modest, because in reality it will work just fine.

Click OK both times, attach “Virtual” to the three fields…

…and your custom field-based popup menus are now ready for prime time.

Explanation:

1. The value list uses two columns to ensure you store the clean value (i.e., the unseen column 1), but the sorting magic happens via the visible 2nd column which exists for display purposes only.
2. The special relationship you’ve set up between Settings and VirtualValueList allows FileMaker to use unstored fields as the basis for the value list… as long as you “start” from Settings.

[If the preceding looks familiar, I used a similar approach in the Date Filtration segment of my Virtual List On Steroids article.]

Demo #5

I mentioned at the outset that the fields the popup menus are attached to do not need to live in Settings. Here in demo 5 they have been moved into a newly defined Orders table.

The fields have been renamed, and redefined to be standard text and number fields, rather than globals.

Here’s the relationships graph, and note that Orders is intentionally not joined to either of the other two table occurrences.

If Orders were connected to Settings via a valid relationship, we could have used the same Hide Object syntax as in demo 4. However, since Orders and Settings are not connected we instead use SQL to derive the contents of VL_001, VL_002 and VL_003.

A couple notes on the SQL:

1. For readability, static code has been used… in the real world I would employ robust coding practices to prevent accidental breakage due to field and/or TO renaming.
   [Update 3 Mar 2020: actually, I might not — see GetFieldName Revisited.]
2. Our SQL doesn’t need a “WHERE” clause because there is only one record in the Settings table.

Closing Thoughts

The approach we’ve seen in demos 4 and 5 works well with popup menus and dropdown lists. It does not work with multiple check box or radio button sets on the same layout since $$arrayVL can only contain one list of entries at any given moment. (Andries Heylen’s Magic Value Lists demo includes a possible work around to address this limitation.)

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.