Diminishing Value Lists and ExecuteSQL
I've been playing with ExecuteSQL searching out more and more ways to use this new and very powerful feature of FileMaker 12. One of the main advantages to making extensive use of this calculation is to reduce the number of explicit relationships in Manage | Database | Relationships only used for specific "utility" purposes.
Using ExecuteSQL to implement a Diminishing Value List is one such case.
What is a Diminishing Value List?
A Diminishing Value List is a type of conditional value list that updates to display fewer and fewer values as values are selected from the value list. The main purpose of such a technique is to make it much harder for users to accidentally select the same value twice from a value list when doing so would generate an error in your database.
In FileMaker 11 and older, this could be done with a calculation field that uses a self join relationship to build a list of all values currently selected from the value list. Say you have two tables, DataEntry--the table in which you plan to use the value list to enter data and Values--the table where each possible value in the value list is stored in separate records. In FileMaker 11 or older, you'd set up these relationships:
DataEntry::AnyField X AlldataEntryRecords::AnyField
DataEntry::cSelectedValues ≠ Values::Value
cSelectedValues can be defined as this calculation field:
List ( "XXX" ; List ( AllDataEntryRecords::ValueListField ) )
The Value list is then defined with the "Use values from a field" option and values are listed from the Values table occurrence with the "include only related values starting from DataEntry" option specified to make this a conditional value list that filters out each value once it is used to enter data into a record and committed.
How this works:
A return separated list of values such as that produced by the List function matches to values in the match field on the other side of a relationship if any one of the listed values matches. This sets up and "Or" type relationship. Since the ≠ operator is used, any value in the Values table that does not match one of the records in cSelectedValues will appear in the value list.
The main "catch" to this is that if cSelectedValues is empty then there are no related records in the Values table and the value list will be empty. Putting the value "XXX" in this calculation as the first value in the list ensures that this field is never empty and thus you get a value list listing all the values from the Values table when this is the very first record you are editing.
By using this function, we can eliminate the self join relationship between DataEntry and AlldataEntryRecords--producing a simpler relationship graph. This method can also be extended such that additional criteria can be included to more precisely limit what values appear and this can even use criteria that changes in response to users selecting different options.
The rest of this posted message documents a sample project that implements two different diminishing value lists. A download link to a copy of the file this process produces is included at the end of this message.
Create Tables and Fields
Define two data source tables with the following fields:
Fields: Fruit (text)
Fruit (Text) to be formatted with drop down list using diminishing value list
Date (Date) set it to auto-enter the creation date if you wish
Then we'll add two calculation fields that use ExecuteSQL--one for each version of the same diminishing value list:
(If you have exactly the same field and table names, you can copy and paste these expressions.)
List ( "xxx" ;
ExecuteSQL ( "SELECT Fruit FROM dataEntry" ; "" ; ¶ )
List ( "xxx" ;
"SELECT Fruit FROM dataEntry
WHERE \"Date\" =?"
; "" ; ¶ ; Date )
Open manage | database | Relationships and drag from DataEntry::cSelectedList to Values::Fruit to set up the relationship needed for the first value list. This value list permits selecting any given value from the value list once over all records in the table. Double click the Values table occurrence and rename it as "UnSelectedValues".
Now select UnSelectedValues and use the ++ button to duplicate it. Rename this field as UnselectedValuesByDate. Drag from DataEntry::cSelectedListByDate to UnselectedValuesByDate::Fruit.
Define Value Lists
Open Manage Value Lists and define two value lists that both specify the "use values from field" option. For a the Fruit Names value list, Specify that the values be shown from the "UnSelected Values" table occurrence and click the Fruit field from this table occurrence to select it. Select "Include only related values starting from DataEntry" to make this a conditional value list.
Make the same choices for the Fruit Names By Date value list, but select Fruit from the "UnselectedValuesByDate" table occurrence instead of UnSelectedValues.
Create a Layout
Go to the DataEntry Layout and add a second copy of the Fruit field to it. Format both copies as drop down lists but select a different value list for each one. Add the date field if it is not already present. If you want, format it to include the drop down calendar option to make entering different dates easier.
Create test Data
Go to the layout based on UnselectedValues and create some records. Enter the name of a fruit into the fruit field on each record.
Test it out
Now go to the Data entry layout and try selecting different fruit from the drop down lists. Note how the values diminish and how the second list only diminishes based on all records with the same value in date. You can edit dates in the date fields to see how that effects the second value list's function.
Fine tune the design
Write a script with Refresh window  as it's only step. Use the OnObjectSave Trigger to perform this script for both of the Date fields. This script is sometimes needed to get a smooth update of the value list with each newly selected value.
Add a text field, aUniqueKey with this auto-entered calculation:
Fruit & Date
Specify Unique Values as a Validation option for this field. This will trip a validation error if the user changes the date field and thus creates two records with the same values in both Fruit and Date.
Notes on ExecuteSQL
The first parameter must be a text expression that evaluates to produce a SQL SELECT statement. SELECT is the only statement supported by this function. The second parameter: "", is the field delimitter. Since the SQL expressions in this demo only retrieve data from a single field, a delimitting character is not needed. The third parameter is the record delimitter. In our case we need to use ¶ so that each value is separated by a return. The 4th paramter is an optional "arguement" parameter. The first ? in the SQL text will be replace by a value from this argument. In the case of the second ExecuteSQL calculation the contents of the current record's date field is used as an argument.
Date is the name of a field in this file, but it is also a reserved SQL word. It thus needs to be enclosed in double quotes to "escape" it so that it will be treated as a field name instead of this keyword. The backslash (\) in turn "escapes" the quote characters so that FileMaker does not interpret them to be the character marking the end of a text string.
To learn more about what can be used in the SQL expression for this function, see the ODBC and JDBC Guide found under Product Documentation in the Help menu.
The Demo file: https://dl.dropbox.com/u/78737945/DwindListExSQL.fmp12
Note: This thread is now more than 3 months old. Due to a forum bug, threads this old will no longer pop up in Recent Items when you add a new comment to it. If you have a question or comment, please post that either to a new thread or post it to the thread that referred you to this one in order for you comment to get maximum visibility.