Print 
PDFFolks, we have a superb demo today (MagicValueList), which comes courtesy of Andries Heylen of BH&A…
But first a bit of background. Prior to July 18, 2012, if anyone had told me you could base a value list on an unstored field, my response would have been something along the lines of…
- What app are you using? (Because it sure as heck ain’t FileMaker.)
- Why are you wasting my time with this nonsense?
- Is today April Fool’s Day?
- What are you smoking?
But then John Ahn showed this amazing Conditional Value List demo during the DevCon “Unconference” session devoted to ExecuteSQL (see previous posting), and to my way of thinking, the most intriguing part of session was only incidentally concerned with SQL, because John seemingly had achieved the impossible — a value list based on an unstored field.
Here’s a value list definition from John’s demo:
And here’s the field the value list is based on:
So it turns out that it isn’t “impossible” after all, and since I was at DevCon I took the opportunity to ask one of the FileMaker engineers about this behavior, and he assured me that while the behavior is undocumented, it “works as expected” and is not likely to go away any time soon.
And since Andries was in the audience during the session, and since he had a long flight home to Belgium a few days later, he was able to put together the MagicValueList demo, and suggested that FileMakerHacks might be a good place to showcase it.
Andries has done a superb job of explaining the intricacies of this technique, so really there isn’t much else for me to say except thank you to both Andries for the demo, and to John Ahn for the revelation that what most of us thought was impossible… isn’t.
A reader reminds me that this technique, while obscure, is not new; in fact Bruce Robertson wrote about it a few years ago in Advisor Magazine.
The original purpose for the session and demos was to talk about the ExecuteSQL and showcase how using the new function could benefit a developer’s current solution, and perhaps allow them to think a different approach to traditional FM methods. We had discussed what to watch out for and how it wasn’t so “scary” to implement; especially for those that hadn’t even touched it. I wanted to demonstrate that they did not necessarily have to do a complete overhaul to their more comfortable FileMaker world but use as another tool in their belt and make life more simpler. The could such techniques to their arsenal such as the portal sorting (multiple column or single) and conditional valuelists. That being said, I am not a reader of Advisor and was unaware of such.
And I saw this technique demonstrated by Michael Horak (Comment) before any of them. People do not create … people only discover. And more than one person can discover the same thing. It happens all the time.
This is indeed true, when I “discovered” it I was just flabbergasted and thought: this is something the FileMaker community should now.
Therefore the small demo (I hope it is clear from the demo that this is not my discovery, but that I am just the demo-builder).
What do you think of this idea?
— Value List without definition —
1.Get the data of valueList by using ExecuteSQL function and extend the data into a repetitive field directly.
[FIELD DEFINITION] GetValue(ExecuteSQL(……….);Get(CalculationRepetitionNumber))
2.Make the repetitive field seem like checkboxes or radio buttons using conditional formatting
3.Make a script that registers which cell of the repetitive field is clicked
Using this technique , You can get the flexibility of the design and flexible sort.
The idea is nice, and might be useful in certain circumstances. Only the fact that you can display them sorted as you wish is a nice extra!
However I think it is limited due to the fact that you need to show as many repetitions as you might have values in your “value list”. Also I think your point 2 is not as easy (or fast) to implement.
But maybe I am missing the point, so if you have a demo, please share :)
Thank you for a reply.
#1 how to restrict the members of the value list to show
I use WHERE clause to filter the items to show in the ExecuteSQL function
#2 speed problem(if you have)
If you use the ExecuteSQL function in the onlayoutEnter trigger script and put the resultset into the global variable($$resultset),it works faster.(maybe…)
[repetitive field definition(for value list)]
GetValue($$resultset;Get(CalculationRepetitionNumber))
#3 the design of value selection
There are so many variation of the value selection style in the world of the web.I think Filemaker also should have the ways to do that.
So this technique comes from that.I wrote “Make the repetitive field seem like checkboxes or radio buttons…”,but you don’t have to do it necessarily.
To point out the selected item,you just turn the color of the text or background by using conditional formatting.
#4 Tips : how to look like a checkbox
Prepare 2 repetitive fields.one for the value to show,and another for the mark “check”.Then put one over another in the layout mode.
Using conditional formatting,you can turn on/off check mark.
#5 Another way : webviewer
We can use a webviewer as a selection tool.Pass the resultset of ExecuteSQL function to the webviewer.In the webviewer,many techniques of the web
to show the value list are available.We can use radiobuttons,checkboxes,dropdown list,accordion panel,etc….
From Filemaker 12 we can use fmp:// technique.Now webviewer is interactive.There are great possibilities in the Filemaker – Webviewer interaction,I believe.
I have a Demo file.But I don’t know how to post it.
You can send it to me (kevin@kevinfrank.com), and I will post it here.
I’m finding it difficult to make the jump from concept to implementation. I always store IDs. So, how would I use this technique to capture an ID while displaying text value choices?
The technique is for single-column VLs only. See More Fun with Value Lists, part 1 for a two-column approach along somewhat similiar lines.
Is it possible to have multiple lines inside each value? For instance, I’d like to return a name and address as follows:
Jane Doe
123 Main St.
——————–
Jane Doe
2200 Busy Ave.
——————–
Mary Jones
7898 Lowery Lane
What I’m trying to accomplish is search for people. Currently, if I have two people with the same name it shows up once in the value list, as expected. Clicking on the name goes to a list view of all those people with the same name and has more info to help the user differentiate the results.
I know I can show multiple pieces of information on one line:
Jane Doe: 123 Main St.
——————–
Jane Doe: 2200 Busy Ave.
——————–
etc.
However, I’m curious if I can display the info in multiple lines. In my ExecuteSQL() I’m using the default separator of the comma to separate the name and address. Then, when defining the field I’ve tried this
Substitute ( GetValue ( $$value ; Get ( RecordNumber ) ) ; “,” ; ¶ )
This properly splits the field calculation into two lines. However, the value list grabs the first value then comes back for the second value, so what I get is this:
Jane Doe
——————–
Jane Doe
——————–
123 Main St.
——————–
2200 Busy Ave.
How about using a picker window instead of using dropdown list?
How about using the Linefeed [ASCII11], instead of CR [ASCII13]?
Will FileMaker see the two lines as ONE two-line value and sort them accordingly in the value list?
It’s interesting.
I tried it.
Using vertical tab[ ascii 11 ],in the popup-menu it works fine.
( But in the drop down-list,it doesn’t. )
Great point of view!
Thank you very much!
Okay, so i tried out the example file choosing OR as the state and Albany as the city. Got a long list of zip codes which were clearly not ALL in oregon. I chose state=NY and city=Albany, got the same list of zip codes. ?
So you COULD pick a state and city and then be able to select a zip code that goes with a different city also named Albany.
So in this instance it’s not really a list that’s conditional by state AND city.
I’m loving this and it’s certainly changed my life in a big way. There’s a huge variety of ways that this can be useful particularly in conjunction with ExecuteSQL ().
I’ve been hoping to find a way to force the sorts on the resulting list as the value list will not honor the sql “Order by”. Any thoughts on this?
Thanks
Perhaps you could leverage this technique from Marcelo Piñeyro?
Extending FileMaker Pro’s value list sort capabilities using the Char() function
I tried this very interesting technique, thank you!
I found out that it also works with a global field ‘feeding’ the unstored calculation field, so you don’t need a triggered script to refresh the valuelist.
In the example above, use a reference to a global field instead of the $$z_valuelist_text variable in the formula. When defining the valuelist (based on the relation!), FileMaker will warn you that it may not work, as it cannot index the (unstored) calculation. But it will nevertheless produce a nice dynamic valuelist!
HE