6 Replies Latest reply on Oct 8, 2012 12:01 PM by nickchapin

    ValueLists using ExecuteSQL


      When I first learned FM 7 one of the core concepts of working in this version and even in FM 12 is this notion of context.


      And context is also very important to understand when you are defining value lists. So you define a value list much like you would a relationship in that you look from the originating table occurrence.


      Problem is if you want to reuse the same set of values that you defined in one place in another table. You'll have to attach another table occurrence just to get the same list of values.


      It would be great if there was a 4th option in defining value lists via a SQL statement. And it did not matter what you attached this value list. Much like a list a static values.


      The SQL statement would evaluate always from a specified table occurrence. This would be another place where one could reduce the number of table occurrences that one would need to maintain.

        • 1. Re: ValueLists using ExecuteSQL

          Vince, have you looked at "Magic Value Lists" http://www.filemakerhacks.com/?p=5412

          (and this post http://www.filemakerhacks.com/?p=5357 )?


          • 2. Re: ValueLists using ExecuteSQL

            No I haven't ... I'll take a look thanks Beverly.

            • 3. Re: ValueLists using ExecuteSQL

              I have to admit, that I hadn't heard about the technique Beverly mentioned before, either. I will certainly start to use it in my solutions.


              However, I know another, albeit similar technique that is perhaps less elegant but works fine, too.


              1. There is a table A containing a normal text field x. The text field must not be global.
                (To give you a hint how I have used this technique before: Table A might be a session table where there is one session entry per database user.
                I'm sure other solutions contain other kinds of suitable tables.)
              2. There is a second table B containing a global field Y.
              3. Build a relationship between table A and the field Y in table B. In table A you need to choose a field for the relationship that gives you the power to select one single record via the relationship. This means that by entering an appropriate value into Y you need to be able to restrict the records in table A to a single one. An id field in A might be an appropriate choice but that depends on the solution.)
              4. Use a script/calculation to fill x with a list of paragraph-separated values. (I often use an SQL plugin to UPDATE the value of x, which gives you a lot of independence when and where to fill x with a new list of values.)
              5. Define a new value list consisting of this single field x from table A.
              6. Enter an appropriate value into the global field Y in table B to select the one single record from A whose field x you're interested in as a value list.
              7. If you now assign the value list to just any field of your choice as the drop-down list, you will see the paragraph-separated values in x to choose from.


              I think that there are 2 points, which make this technique interesting:

              1. The value list is not defined by an unstored calculation field.
              2. You can switch very easily between value lists by changing the value in Y. So you might even use this technique to define a whole range of dynamic value lists.


              I hope this helps.

              • 4. Re: ValueLists using ExecuteSQL

                Vince: I would agree with Beverly that the techniques outlined by Kevin on his site are the way to go. I've gone to using a very-slightly modified version for all of my value lists and love it. Very extensible and dynamic.


                With this you can also implement the BOM technique put out there by...uh...ok, I forgot (SeedCode or Solient or someone) so you can force the sort order or the second field. (Instead of A, B, C you can make it C, A, B - that's in the SECOND field of a two-field value list).


                When these two techniques are combined you've got the best possible value lists solutions.




                • 6. Re: ValueLists using ExecuteSQL

                  Yes! Thanks!