Using ExecuteSQL to Produce a Conditional Value List
This thread is so I can add one more link to the "info dump" that I share when people ask about conditional value lists. We are going to take a look at how ExecuteSQL can be used to produce conditional value lists that would be much more difficult to produce without this powerful function that was first added in FileMaker 12.
Note: The info in this thread is only useful with FileMaker version 12 or later. You will also need a basic understanding of how to set up SQL queries as this is not something I've tried to explain in this thread.
The demo file that you can download from this post exploits a long standing feature for relationships where one of the match fields stores a return separated list of values. If I put the numbers 1, 2, and 3 into a field, with a return between each value and then use that field as a match field in a relationship matching it to a number field of the related table, my record will match to any record in the related table that has 1, 2 OR 3 in that number field. Thus, it sets up an "OR" based matching logic distinctly different from the "AND" matching logic you get when you select 2 or more pairs of match fields for a relationship.
We've long used the List Function and check box formatted fields to produce such handy lists of values. ExecuteSQL, however, can be used to produce the same type of list of values with tools for selecting which values to include in the list that go far beyond what is possible with any other FileMaker function due to using an SQL query.
The demo file illustrates two uses of lists of ID values produced by ExecuteSQL functions that are then used to produce a conditional value list. The first layout demos the type of simple conditional value list where a Category is selected in one field and the value list is then limited to values from that category.
This conditional value list would be quite easy to produce with a conventional approach--you'd use a relationship that matches values on the category field. It's not likely that I would actually use ExecuteSQL for such a value list, but it's included in the demo file as a simple example so that you can study the details of this method before I complicate it to produce a value list that wouldn't be so easy to create with conventional methods.
The second layout demonstrates a value list where the values and FileMaker indexing simply won't allow a working relationship on which to base a conditional value list. This value list allows the user to specify a "minimum quantity" in order to further limit the list to values which have a "Production Total" less than or equal to this specified minimum. If Production Total were an indexed field, we could just include a second pair of match fields in our relationship, but this is a calculation that sums data from a related table--thus it cannot be stored or indexed and thus we can't use it like we need in a relationship.
But ExecuteSQL can limit the ID numbers that it Selects if we include an expression in its WHERE clause to only list ID numbers of records where this computed total is greater than or equal to the amount the user enters in the minimum quantity field and so we get a value list that works via ExecuteSQL that would require quite a bit of work to pull off without this useful function.
Here's the link to the Demo File: https://dl.dropboxusercontent.com/u/78737945/ESQLCondValueListDemo.fmp12
Note: if you have been referred to this thread from somewhere else, it is best to return to that thread before posting any questions about this thread. That will keep any discussion of your question in the context of your original request.