4 Replies Latest reply on Sep 1, 2012 11:43 AM by nickchapin

    SQL Value Lists

    nickchapin

      I've been doing a bit of reading and learning about the new tricks using ExecuteSQL for conditional value lists and it got me thing. Some of the projects I work on contain a number of value lists in tables; the original developer likes to use a name/value pair for VLs and allow the end-user to add, delete, etc. without allowing them access to FMPs Value List dialog. These VLs use the traditional second field as the human-readable list on top of the ID that the field actually holds.

       

      Anyway, I started to look at the fine examples by John Ahn (http://www.filemakerhacks.com/?p=5357) and Andries Heylen (http://www.filemakerhacks.com/?p=5412). Although these are great and I appreciate their work, I wondered about using the SQL technique for non-conditional value lists - so-called "normal" value lists.

       

      It also seemed that John's use of merge variables over the field for pop-up menus seemed a bit out of place. Furthermore, both of these techniques seemed to fail if there were different non-conditional value lists on the same layout. Finally, one (maybe both - can't remember) relied on an additional settings table with at least as many records as there were value lists.

       

      Maybe I'm missing something (highly likely), but neither of these seemed to release the "power" I've been hearing about with regards to value lists in 12.

       

      Anyway, I've put together a small file that does most of what I was thinking 12 might do; all values and IDs are in a single table accessible without additional relationships to the UI table. However, it still needs an extra two fields per value list. It also needs, like the samples from John and Andries, a way to set the initial value lists on opening the file. (You can see this in the sample as I didn't call the setup script. Call it yourself before you select a VL and you'll see what I mean. Also try it after a clean open by clicking one of the color fields then hovering over the other color field.)

       

      So, am I wrong in thinking that this may not be the panacea I thought it would be? Does this approach have a lower schema cost than a traditional approach to value lists? How are you (if you are) rethinking VLs in 12?

       

      Any thoughts or comments?

        • 1. Re: SQL Value Lists

          Nick Chapin wrote:

           

           

          It also seemed that John's use of merge variables over the field for pop-up menus seemed a bit out of place. Furthermore, both of these techniques seemed to fail if there were different non-conditional value lists on the same layout. Finally, one (maybe both - can't remember) relied on an additional settings table with at least as many records as there were value lists.

           

           

          Hi Nick.

           

          I am not sure what you mean by the merge variables being out of place. The reason that the demo has them there is to demonstrate that no additional TO, fields, etc are needed except the few calcs that are used for the value list.

           

          Once that value has been set into the field the corresponding text value is displayed for the user. You can reuse the same value list and fields over and over again and there is no need to create multiple value lists and supplemental fields for each field.

           

          I use separation so I dont really have a lot of calcs in my tables. Most of the calcs are used for display purposes on the layout, but as always everyone has their own style. For me this way just an additional way to have a cleaner data file.

           

          -John

          • 2. Re: SQL Value Lists
            nickchapin

            Hi, John. Thanks for the reply.

             

            As I said, I'm just trying to understand how using SQL VLs in a non-conditional manner would save me schema. In my world I have multiple tables with two fields each - ID and Value. These are used to create standard VLs used throughtout the solution. Obviously, each use needs a seperate TO for each anchor.

             

            In your file you do the same as far as the tables go - having three tables for the teirs. That's one of the traditional models for conditional value listsand all is good. I'd like to get away from having so many tables.

             

            On your layout you need to place a merge variable over the field for the user to see the textual value of the field. That's all good, but it's an additional object on the layout. As you've discovered, if you use a drop-down or pop-up menu then you would need to have that display variable. As you know, in FMP's standard pop-up menu the textual value is displayed - no need for the variable. Additionally, one can enter find mode and use the pop-up menu.

             

            That's what I'm trying to replicate using the technique you've outlined: the ability to use pop-up menus with textual display without additional objects; the ability to use those fields in find mode with the VLs properly displayed; and the combination of many tables for VLs into one table.

             

            To do that the only way I saw was to actually create additional VLs and fields. But, as I said, perhaps I'm misunderstanding something.

             

            Your file is great, btw.   It helped me to get to the file I made that does (most) what I want. I was just thinking I may be missing something and wanted to ensure I wasn't.

            • 3. Re: SQL Value Lists

              Hi Nick.

               

              As I said earlier, I use separation in most of my solutions so a few additional objects on the UI level do not bother me. IMO having those objects are worth having a cleaner schema without a boat load of additional fields.

               

              As for using popup menus and performing finds, although the conditional VL demo does not address it directly as I tend to allow users to find in those fields freely instead of conditionally, since your scenario isnt for conditonal value lists, you should be able to perform the finds easily with the technique.

               

              See attached.

              • 4. Re: SQL Value Lists
                nickchapin

                Thanks, John. Your second file definately helped me get my head around it all.