SQL Value Lists

Discussion created by nickchapin on Jul 26, 2012
Latest reply on Sep 1, 2012 by 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 ( and Andries Heylen ( 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?