Folks, 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 had the same problem. Here’s my solution:
Create an additional stored calculation field named stateCity which concatenates the state and city fields with a delimiter. For example, the calculation would be: state & “»” & city. So, the output would either be: OR»Albany or NY»Albany.
Then in the SQL statement for the OnObjectEnter script trigger, for the WHERE argument, put the stateCity field as the condition. Also select the stateCity field as the last argument. Like this:
..WHERE stateCity = ?” ; “” ; “” ; TableName::stateCity ) )
Now, only Albany zip codes from the proper state will show up in the value list!
Same commenter here as “hardwise”. Pardon that I’m an SQL newbie, but I figured out how to restrict your conditions within the SQL statement (instead of having to create an additional concatenated field). You use additional AND arguments.
So, instead of finding the zip code by city only:
..WHERE city = ?” ; “” ; “” ; TableName::city ) )
add additional AND arguments in the statement, and add the additional table::field arguments in the proper order at the end of the calculation, too:
..WHERE state = ? AND city = ?” ; “” ; “” ; TableName::state ; TableName::city ) )
Those additional table::field arguments at the end of the function are the input fields. The example I gave were using the very fields making up the data for the value list also being the input fields:
..WHERE state = ? AND city = ?” ; “” ; “” ; TableName::state ; TableName::city ) )
But, if you were using the gInput fields as in the demo field, it might be:
..WHERE state = ? AND city = ?” ; “” ; “” ; settings::gInput ; settings::gInput[2] ) )
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
This is a very interesting technique. I just created the bare bones version for myself to find out when exactly this “feature” becomes available. Quite easy to setup and lots of applications. I don’t want to spoil the fun here, but unfortunately it’s not documented by FileMaker, and you even get a warning dialog that this will not work *read: is not supposed to work*.
It makes me think about global unstored calculations a few years ago. Using a certain ritual of of button clicks in FileMaker Pro 7.0v1 schema, you were able to create unstored calculation that were equally global. This created a nice array of possibilities for module development, as you could access field calculations of unrelated tables which is super cool. After a maintenance release of 7 the “creation ritual” was not possible anymore, but created fields continued to function correctly. It finally broke in 12, because it did not survive the conversion.
I made a demo file back then and sent it to FileMaker, documenting the advantages, and a plea to add official support for this technique. The response I got was that it was a concept that was too complex to support. Luckily the number of developments where I implemented this technique was limited, but for some of them I had to replace the ugc technique by script triggers, Thank God for that new feature.
So… for those planning to use this technique in your development. Think about it. It’s not supported officially, and might break in any product update or upgrade. Apparantly FileMaker does not like stuff to become too complex, maybe because it becomes also more complicated to support and/or document.
But when I look at the 12 version, I’m beginning to doubt my own opinion…:-)
Until FileMaker officially supports this technique I prefer to call it a hack. Kevin, no pun intended…:-) Maybe we should look at FileMaker first, to get an “Urbi et Orbi”.
Hi Peter,
I agree that one should always proceed cautiously since life is full of uncertainty… and I too was, ahem, inconvenienced by the behavior change you mention.
Regards,
Kevin
Kevin and Andries, thanks so much for this article and making the demo available. Once I got my mind around it and got it set up, it is a simple technique that I’ve always wanted to do in FMP. Thanks so much!
Just a note, Andries, about the demo file. There’s a typo in a couple of the SQL statements in the “Some more Examples” layout. From the “Value Lists that look for a Pattern” example on the ginput[4] field, the last argument – “%” & settings::gInput[4] & “%” – is also found in the ginput[6] and ginput[7] calculations. Perhaps leftover from duplicating the ginput[4] field.
Hey Kevin,
Nice write up about a cool technique. This seems like a feature that would be already implemented by FM – being able to dynamically create value lists on the fly without complex relations.
But I ran into one issue, in FM14 anyway. It looks like the structure is all working correctly and my value list updates. The issue is that when I change a record’s data so that the value list changes, and then click the drop-down to view the new list (or what should be a new list), the UI seems to keep the value list from the prior showing, i.e. in it’s cache. If I click the drop down a second time, it updates correctly.
I’m using a slightly different approach, although one that was described in Andries’ demo file: I am using a global field as the source of the value, instead of an unstore calculated field. My script trigger on the UI element then sets this global field directly instead of using a global variable as an intermediary. One less moving part in my opinion.
So as I am stepping through the script to see what happens, clicking on the drop-down shows the value list before the script even fires – hence the fact that it is from the cache or some such. The script then goes through and populates the field correctly, thus the 2nd time that it is clicked it will show the new list. It seems that the global-variable-structured version of this would suffer from the same issues. Any thoughts as to what’s going on?
I suppose if you went with the g-var + unstored calc field version, you could trigger the setting of the global variable via a tooltip calculation instead of using the script trigger, thus firing before the drop-down was ever opened…
Thanks,
Justin
A wonderful technique, which I had some difficulty implementing. Why? You need one record in the “settings” table!
Mindblowing. Hard to imagine this technique has been around so long.