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.
Brilliant! Sometimes I have to scratch my head about FileMaker and how things that should be so simple (e.g. a sort order on a value list) are so difficult – but talented developers like yourself show there is always a workaround, you just have to find it!
Thank you Daniel. I appreciate the kind words.
If you make the global field have an auto enter calc with the second choice unticked, containing the formula SortValues ( Self ; 2 ) then even though it is a text field you can manually add a 6 at the end of the text list and it will automagically sort to the start
Further to that, you need a way to deal with the case that you manually remove the first item so the auto-enter might need to be:
Let ( _a = SortValues ( Self ; 2 ) ;
Case ( Left ( Self ; 1 ) = ¶ ; Middle ( _a ; 2 ; 9999 ) ; _a )
)
this would need adjusting further if you deleted more than 1 value while not yet committing the field, probably into a While, but this is enough to solve the quick case
Hi John,
The goal is to produce a properly-sequenced value list. Does what you’re proposing offer an alternative method to accomplish this? If so I’m unclear on how that could be the case. Would you be willing to send me a demo file? TIA.
Kevin
Demo received. Thank you John. To be clear, the order of items in the global text field is irrelevant as far as I’m concerned. The goal is to produce a numerically sorted value list and the global text field is just an intermediary step along the way. In a “real world” implementation no one would need to interact with or even see that field.
I recently had a case where I needed a value list to sort “numerically” but the values were alphanumeric. Things like 1, 1B, 1CSE, 2B, 11CSE, 14B, 273B, etc.
Some of the numbers got fairly high as well.
My first attempt was prefixing byte order marks (BOMs) for the number value. But that quickly didn’t scale well. In addition to getting really slow, there was also a point where the sort stopped working. I think the index only looked at the first X number of characters… and if the number (and therefore number of BOMs) exceeded that, the sort was useless for numbers higher than that.
But then I changed it to the number of BOMs corresponding to the number of digits of numbers. So 1-9 had 1 BOM, 10-99 had 2 BOMs, 100-999 had 3, etc. That was performant and more importantly, kept working with higher numbers.
I’m not sure if this technique would have worked for that? Maybe in combination with “Show values only from second field”?
That’s where I would leverage the techniques shown in demos four and five here
https://filemakerhacks.com/2018/03/12/custom-field-based-value-lists/
(Because you need a second column)