ExecuteSQL, Level: Advanced, SQL, Version: FM 13 or later, Virtual List

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? 

2018-03-09_14-44-10
The “problem”: field-based value lists sort alphanumerically

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

2018-03-23_120634
From demo #4: field-based value lists mirroring the order of their underlying source fields

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

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.

2018-03-10_082555.png

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…

2018-03-10_084639

…which are attached to the global fields like so…

2018-03-10_085650.png

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

2018-03-23_120425

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.

2018-03-10_095255

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

2018-03-10_100223

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”)…

2018-03-10_101219.png

…the calculated equivalent becomes…

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

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

2018-03-10_112232.png

…and configured to use Unicode.

2018-03-10_121410.png

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

2018-03-10_14-28-11

…in other words, polluting our data with BOMs.

2018-03-10_120726.png

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…

2018-03-10_122013.png

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

2018-03-10_122323.png

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

2018-03-10_14-28-10.png

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

2018-03-10_125637.png

But did you notice something missing in the popup menu?

2018-03-10_14-28-10.png

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…

2018-03-10_14-28-11

…and confirm that the data viewer is happy.

2018-03-10_125637.png

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.

2018-03-10_14-52-02

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.

2018-03-11_14-43-15

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.

2018-03-11_16-39-38

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.

2018-03-11_16-40-57

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.

2018-03-12_103917.png

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

2018-03-12_14-33-02.png

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

2018-03-12_13-13-03

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

2018-03-12_13-13-13.png

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.

2018-03-12_13-16-19

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.

14 thoughts on “Custom Field-Based Value Lists”

  1. Hey Kevin. Is there any chance you could make “v4, a satisfactory workaround” in FMPro 12?

    1. Hi Charlie,

      Demos 4 and 5 require “Hide Object”, which wasn’t introduced until FM 13. I tested to see if one could instead use “Conditional Formatting” in a similar manner, since that was available in 12, but unfortunately it’s not viable.

      Regards,
      Kevin

  2. Hi Kevin,

    I work with FM11 mostly, so the #4 and #5 are not for me. May I wonder, why do you use for Demo #2 global calcs? If simple stored and indexed calcs similar to Demo #4 & 5 are used, then valuelist based on two fields, showing only 2nd field’s value = PrependBOM ( V_001 ; ID):
    a) shows values in order given by ID, looks fine as Drop-Down List and Pop-Up Menu;
    b) does not clutter input filed with BOM’s at all (as value from 1st field is inserted)
    c) for the same reason, checkmark in menu IS visible, when Pop-Up Menu is selected for input.

    I’m rather on try & let’s see what happens side then thinking too hard, so there may be (and often is) quite easily something wrong in my theory that I have completely overlooked… But the only problems I can see is this does not allow to use ID as value (and displaying “real” value from related table), and, separate “Settings” table is required for every valuelist, else duplicated values in main table will cause problems.

    Thanks, Milan

    1. Hi Milan, would you email a demo of your suggested technique to kfrank at kevinfrank dot com? Thanks.

      1. Hi Kevin,

        I almost get it now (I do not have access to your files, I only have FM12 & 11 installed), so I figured out just that your technique relays on single record in Settings table (multple values inserted on 1 field), opposite to my approach getting single values from multiple records.
        I now almost replicated #4 using ScriptTriggers in FM12, but OnObjectEnter works on second click, not first, unfortunately…
        Thanks for reply and no need to bother more with my stupid question.

        Thaks again, I still have lot of #*& learn…

        Milan

  3. Well, that made me thinking about various approaches to valuelists, as yours is definitely more comfortable for users, but does not allow to insert ID of the value instead of the value(name) itself. Both have its pro’s & con’s… it depends.
    Also ScripTrigger definition “OnObjectEnter” certainly has some flaws in its name.

    Regards,
    Milan

  4. The calculation you have in the Hide Object attribute on the popup menu can be put into a custom function. (But I like to have a CF that produces useful stuff too, so a variation follows). Putting it into a CF achieves two aims: (a) I can use a less brittle SQL expression, and (b) the Hide Object calculation is much more succint.

    The Hide Object calculation would then look like this:

    `ValueList(Settings::VL_001) and 0`

    NB: do not write it as `0 and ValueList(Settings::VL_001)` .. the falsely value of `0` will shortcut the evaluation and the CF won’t get called (and hence the $$arrayVL not set).

    My CF looks like this:

    // ValueList ( field )
    // Construct custom lists from table values
    // Returns Value List – in record order (not index order!)
    // Use in Field Hide calculations as: ValueList(field) and 0
    // From https://filemakerhacks.com/2018/03/12/custom-field-based-value-lists/
    Let (
    [
    _fieldref = GetFieldName ( field );
    _fieldTO = Left( _fieldref; Position(_fieldref;”::”;1;1)-1);
    _fieldname = Replace ( _fieldref ; 1 ; Length(_fieldTO)+2 ; “” );
    _fieldvalues = ExecuteSQL (
    “SELECT ” & Quote ( _fieldname) & ” FROM ” & Quote ( _fieldTO ) ; “” ; “” );
    $$arrayVL = _fieldvalues // used by virtual value list magic
    ];
    _fieldvalues
    )

  5. One shortcoming of this overall technique though is that value lines of “-” get presented as selectable values (i.e. not unselectable dividers).

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.