Demo Files
Demo #2 is a variation on demo #1, and uses the contents of a $$variable as the basis for the value list.
The Challenge
Recently I had a need to use a dynamically-generated ascending sequence of numbers as the basis for a value list, and in a multi-user-safe manner. By dynamically-generated, I mean that the list of numbers would be calculated at runtime and would not be known in advance. And by multi-user safe, I mean the methodology would need to allow multiple users to simultaneously and independently populate the value list with their own sets of numbers.
First Attempt At Solving
There are a number of ways a developer might go about solving this problem, and my initial plan was to…
a. Populate a global text field with the list of numbers
b. Base the value list on that global field using a technique I picked up from John Ahn at DevCon 2012 in Miami Beach (as described in Magic Value Lists) and which I subsequently explored in detail in Custom Field-Based Value Lists.
The basic idea is to set up a relationship where the “primary” predicate is unstored. In this case I went with a self-join from my Settings table to itself like so…
…though I could have instead used a global field on the left (primary) side… the important things being that the left-hand predicate is not stored and the right hand predicate is stored, and of course that the relationship is valid (in this case we have an equijoin operator and each field = 1).
[Note: a Cartesian join relationship would work here as well.]
The next step was to create a related value list using this relationship…
…starting from Settings and reaching into “Settings.magic”, using values from the global text field. As expected (if you’re familiar with the technique), FileMaker displayed an error saying it wouldn’t work…
…but then after clicking OK the value list worked perfectly — or seemed to at any rate. (Normally you can’t base a value list on an unindexed field, but a relationship like the one used here makes it possible.)
At first all appeared to be well.
But then it dawned on me that all would not be well if, for example, the numeric sequence spanned the number 10…
or the number 100:
I needed the value list to sort numerically, not alphabetically. So… what to do?
Possible Alternatives
Well… returning to to the aforementioned Custom Field-Based Value Lists, it occurred to me that I could use the trick employed in demos 4 & 5… a somewhat complex agglomeration of byte order marks, virtual list, and a two-column value list masquerading as a single-column value list.
Another option might be to have some sort of utility table with a standard number field, and the value list based on a relationship keyed to the user, and then create/delete records on the fly as necessary to ensure the value list items were correct.
But both of those ideas seemed like an awful lot of work to accomplish something so basic.
A Simpler Approach
What if I could somehow “trick” a number field into accepting a return delimited list of values? For example, in Settings, what if, rather than pointing my value list at a global text field, I were to create an unstored calculated number field to echo the global text field…
…and then define a corresponding value list?
Yes, that works and is much simpler.
Closing Thoughts
The value lists in today’s demos are “portable”, which is to say they can be used anywhere in the file. (Thank you Darren Terry for explaining this in one of the online forums many years ago.)
To see this portability in action, navigate to the “Orders” layout, which is not connected to Settings on the Relationships Graph…
…and observe that the value list displays the desired values here as well.
