FM Function of the Week: Two Value List Functions

Aah value lists. Reliever of the weary typist, protector of accuracy, usher to the chooser. Well concieved and implemented value lists are, despite my trifling tone, extremely useful in almost any database. In the process of rebuilding and improving an aing system recently, I was tasked with reimagining a layout with about 20 fields, each bearing a pop-up list with its own unique value list. In practice, any given record in this database had information in 5 to 8 of those 20 fields. To make the whole thing more efficient, we decided to replace all those fields and pop-ups with a single portal. We also moved the static value lists into a dynamic and more user-friendly value list table.

Probably the most obvious way to get the contents of a static value list is to open Manage > Value Lists and start copying and pasting. It works, but man is is tedious when you’ve got a lot of lists. Besides, there’s no need to put yourself through that when FileMaker gives us a couple of functions to aleviate the drudgery.

ValueListNames ( fileName ) does just what it says on the label- it produces a return-separated list containing the name of every value list in the specified file. This is the stepping stone necessary to get us to the actual values they contain.

ValueListItems ( fileName ; valueListName ) also generates a return separated list. This time it’s the values that make up the specified value list in the specified file.

Using these two functions in a script, we can get those value list names, then loop through that list to get each list’s constituent values. Rather than try to explain how that all works here, I put together an example file containing that very script. Grab a copy and dig into it to see these two functions in action.

screenshot of an example file that rounds up all value lists for a given file
An aside- I expect a number of folks will read this and wish to point out that the process described here can be (and already has been) achieved with a very efficient and very fast recursive custom function. You’d be entirely correct, too. Not every FMFotW reader owns FileMaker Advanced, however. Going the custom function route with this post wouldn’t just exclude those readers, it would also necessitate some explanation of custom functions and recursion. That’s a bigger side trip than I intend to make on a post about two simple design functions.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.