Dynamic Value List Filters

With the introduction of the function ExecuteSQL in Filemaker 12, I find it now fairly easy to have dynamic value lists. An instance this may become useful is if you change the values in a value list for future records.

Example
If you have a field teamName, and the original values contained, Montreal Expos, Toronto Blue Jays, Detroit Tigers…
The Montreal Expos now moved to Washington so you will want Montreal removed and Washington added to your value list for new records. However you still have old records which contain Montreal Expos as a value.

In order to find Montreal you will either you need keep 2 lists up to date, or you can use the ExecuteSQL function for calculation field and use that field in a value list so that it will find all teamNames with no need to update it. You will also want this to be distinct values.

Here is the code.

ExecuteSQL ( “SELECT DISTINCT teamName FROM Players”; “”; “”)
This will pull every records teamName and only unique values. Thus making Montreal and Washington both values in the value list.