6 Replies Latest reply on Apr 1, 2014 1:08 PM by JordanBommelje

    Value list from specified value in field



      Value list from specified value in field


           So what I'd like to do is create a value list from a table where a specific field is specific.

           Essentially, I'm looking to: SELECT fullName FROM employees WHERE position='manager'; and populate this as a value list. I cannot figure out how to do this with FileMaker. 

           Can anyone give me some pointers?

        • 1. Re: Value list from specified value in field

               What you are describing is a conditional value list. There are three ways that you can use to set this up and a SQL query is one of them.

               Here are some links on the subject:

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Value list from specified value in field

                 In those examples, they seem to do more than what I need. Those all are conditional on a specific value in another drop down. What if I have a single drop down that I want a list from a specific value that will not change? Sorry if that doesn't make sense. It seems like these examples have a list, then provide results based on that selection, but if I don't want to have that initial selection and it will always be the same thing, how do I populate that list?

            • 3. Re: Value list from specified value in field

                   There are three methods for a conditional value list and while two can be used in conjunction with another value list that specifies a category, none of them have to be used with such an additional value list.

                   Take a look at the forum tutorial. Option 1 in that tutorial uses only a single calculation field and no relationships of any kind. This "hardwires" the value list to a specific subset of values from another table--which seems to be exactly what you have requested here.

                   Option 2 in the same tutorial and which is also the method illustrated in the knowledgebase article is based on a relationship between two tables. While the match field used for that relationship can have  a value controlled by a value list, it can also be set up as a calculation field that always has the same value in every record of that table and then you also have a value list like you require.

                   And the third method, which uses ExecuteSQL to controll what values appear in the value list, you can use a WHERE clause that matches to a constant value rather than a value specified by a value list to limit the values listed.

              • 4. Re: Value list from specified value in field

                     I feel like this is way more difficult than it should be.

                     I'm sure part of it is me struggling wrapping my head around it, but I'll try to lay things out to try and get some better guidance:



                     Positions (positionID, positionName)

                     Employees (employeeID, fullName, positionID)

                     DailyNotes (noteID, manager, employee, noteContent)


                     In the Daily Notes layout, I'd like to have a drop down to select a manager then a drop down to select an employee.

                     Note: A manager would be in the list of employees as well as every other employee.


                     Hopefully this helps. I've been trying things out for 6 hours now with no luck.

                • 5. Re: Value list from specified value in field

                       To repeat, use option 1 from the forum tutorial.

                       Say the Position ID for a manager is the id value of 1.

                       Define this stored, indexed, calculation field in Employees:

                       If ( PositionID = 1 ; FullName )

                       Define a value list with EmployeeID specified as the Field1 value and this calculation field as the Field2 value. Hide the first field or sort on the second field.

                       The value list will only list managers from the employee table.

                       PS. I didn't set the calculation to refer to position name in the Positions table as that calculation could not be a stored, indexed calculation.

                  • 6. Re: Value list from specified value in field

                         Thank you. That made much more sense to me. I guess I just needed it spelled out. Now it totally makes sense. Thanks!