ExecuteSQL, Level: Advanced, SQL, Version: FM 12 or later

More Fun with Value Lists, part 2

Update 20 Aug 2012: Dr. Noda has updated the demo files to include Rob Russell’s clever fill graphic trick (as per the responses following the article). Thank you Rob and Dr. Noda.

As promised last time, today we’re going to look at a technique from Dr. Osamu Noda, which uses FM 12’s ExecuteSQL to create what appears to be a value list when in fact there are no value lists defined at all.

This technique, like some of the others we’ve seen recently, is decidedly esoteric, but it shows some clever outside-the-box thinking, and I appreciate Dr. Noda taking the time to share the demo (ValueListWithoutDefinition-RR-Edition) and write up his notes. One of the things I particularly like about this technique is that it allows the order of the value list items to be customized, even though the values come from a table.

The zip file contains two versions of the demo… the second being optimized for performance.

Here are Dr. Noda’s notes:

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.

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

5. Speed problem (if you have)
If you use the ExecuteSQL function in the onlayoutEnter trigger script and put the result set into the global variable ($$resultset), it works faster (maybe).
[repeating field definition (for value list)]

6. The design of value selection
There are so many variations 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.

7. Tip: how to look like a checkbox
Prepare 2 repeating fields, one for the value to show, and another for the “check mark”. Then put one over another in the layout mode.
Using conditional formatting, you can turn on/off check mark.

8. 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 radio buttons, check boxes, dropdown list, accordion panel, etc.
In Filemaker 12 we can use fmp:// technique. Now webviewer is interactive. There are great possibilities in the Filemaker – Webviewer interaction, I believe.

9 thoughts on “More Fun with Value Lists, part 2”

  1. That’s a clever little trick with the web viewer, I like it.

    Just on the check mark technique in step 7: you can do this without defining the two extra fields. Create a lightweight image represent your check mark and set the fill graphic for the field to the image. Then add a conditional formula to evaluate the fill colour to transparent for the fields/repetitions that _don’t_ meet your requirement.


  2. I would love to use the web viewer based value list, but it does not seem to work in my copy of the demo file. The key value in the prefs field does not update when selecting a city from the drop down list, however manually changing the key value in the prefs field will update the selected city displayed in the value list.

    Am I missing something basic?


    1. Thanks for writing Karstyn… I also was not clear on that portion of the demo and you’ve prompted me to take a second look. The main portion of the demo uses fields and script triggers, and works great.

      But on the second screen we have a web viewer constructing the value list, and a note indicating that the technique is for a) files hosted via FileMaker Server *or* b) FM Go files (hosted or standalone).

      The web viewer is constructed via the wvList custom function, and if we check out the definition (without in my case, pretending to understand everything going on in there), we will see that after the selection is made, a script will be performed using the “FMP” protocol… which does not work on standalone FMP files.

  3. “FMP” protocol doesn’t work on standalone fmp file. That’s right. But there is a technique to interact with web viewer. In these cases, I use the technique below.

    0.[ set variable ] $path=Get(TemporaryPath) & sample.html
    using this path,
    1.[ export the field contents ] ( which contains the html code)
    for example, here is the code.

    2.web viewer definition : “file:/” & Get(TemporaryPath) & “sample.html”
    3.run timer script which checks the web viewer’s source property repeatedly.( Use GetLayoutObjectAttribute function)
    If you check the source in the script,you can get the text which contains the selected value.
    ex. file:/………../sample.html?5

  4. Thanks for the quick reply Kevin. As soon as I started to read it I had one of those ‘of course, I should have known that’ moments!

    Placed the demo file on my server and viola!

    Thanks again.

  5. Dr. Noda has provided a new demo file to show a web viewer value list selection proof-of-concept that does not use the “FMP” protocol, and therefore is suitable for standalone FileMaker Pro solutions.

    Demo file: FMPless-Interaction

Leave a Reply

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