3 Replies Latest reply on Mar 22, 2010 8:55 PM by FentonJones

    Drop down lists, show 2nd field



      Drop down lists, show 2nd field


      As a former Access user I'm used to having drop down lists to select a key field that instead of displaying a serial number displays an alternate field. For example having a table of locations with fields LocationID and Location_Name. I can get the drop down (in another table) to show the location name but then it displays the LocationID which is not human friendly. What am I missing here or is there a way to handle this that I'm just not getting?



      FM 10 Pro on Snow Leopard. 

        • 1. Re: Drop down lists, show 2nd field

          MS Access combo boxes are one of the few things I miss from MS Access. Filemaker's drop downs have improved a lot, but they have a long way to go to meet MS Access in this one area.


          To do what you want you can select Pop-up Menu instead of drop down list. Then the field will display column 2, but store column 1.


          There are ways to play games with the layout that simulate displaying column 2 and storing column 1 with a drop down list but it's more work and complicates your layout design.

          • 2. Re: Drop down lists, show 2nd field

            This answers one of the questions I have been looking for. The question I have is, based on the example above, if the user wants to enter a new location name that is NOT in the drop down list, can I have them enter it into the drop down list and get the program to add it to the location table automatically? In MS Access I would in the Property sheet set "limit to list" to yes and then in the "On not in list" Event Procedure, I would write  some VB code to add the new entry into the table. Is there an easy way to do this with FileMaker Pro v10 advanced? I thought of using a value list and let the user edit the entries, but this would add unnecessary size to the database. It is better, from what I have been told, to store the Primary Key value from the lookup table than the word or phrase itself and let the program look up entry when needed.



            • 3. Re: Drop down lists, show 2nd field

              I think the answer to your question is to use the "Values from a field" option. The Value List will then show the unique entries from all values entered in that field, in the entire table (unless filtered by relationship). You can then use the "Show Only 2nd field", in order to hide the IDs. 


              There is hidden "gotcha" in this method, which is that it cannot deal well with duplicate values in that 2nd field. It uses the Index of the field for its values. Indexes contain only unique entries. Hence if you have two "John"s, you will only ever see 1, and will alway pick the 1st John entered. It is a good idea to Validate that 2nd field, for Unique, at the Field definition level, if you use this technique.