7 Replies Latest reply on Nov 6, 2013 3:15 PM by RyanBleam

    Populate drop down list based on a value selected in a previous drop down list

    RyanBleam

      Title

      Populate drop down list based on a value selected in a previous drop down list

      Post

           All,

           I have the following database structure:

           Table 1: hotel

           Fields 1: id, name

           Table 2: coordinator

           Fields 2: id, hotel_id, name

           I have the following data records for the first table:

           1, Hilton; 2, Marriott; 3, Hyatt

           I then have the following records in the second table:

           1, 1, Hilton Person; 2, 2, Marriott Person; 3, 3, Hyatt Person; 4, 3, Hyatt Person 2

           I have a relationship between hotel.id and coordinator.hotel_id in my relationships.

           I then created another layout and have two fields on there. Basically what I'd like to happen is to populate the first field with either a drop down or pop up box with the hotel name. As soon as the user selects an entry from the first field it automatically populates the second drop down list based on the hotel ID that was selected in the first drop down. 

           So if someone selected Hilton in the first field, it would automatically populate Hilton Person in the second field. If they selected Hyatt, then it would automatically populate Hyatt Person and Hyatt Person 2 in the second drop down.

           How can I acheive this?

           Thanks in advance!

        • 1. Re: Populate drop down list based on a value selected in a previous drop down list
          philmodjunk

               This is called a conditional value list. 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, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

               The 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.

               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

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

          • 2. Re: Populate drop down list based on a value selected in a previous drop down list
            RyanBleam

                 Thanks! This worked very good, however I have a small issue. I set mine up basically exactly like yours (after I figured out how you did it). I got the second drop down to work great but populates the second drop down box with the ID and then once I click on it the correct name is there. When I click on the first drop down box I'd like it to auto populate the second drop down box with the name instead of me having to click on the dropdown to get the name populated in there.

                 Here is the demo file: https://dl.dropboxusercontent.com/u/63495839/ConditionalValueListDemo_2.fp7

                 I'm using filemaker 10. When I change the hotel name on the left in my Main layout, I'm hoping that the DropDownListValue gets populated with the name right away instead of me having to click on it.

                 Any suggestions on how to do that?

            • 3. Re: Populate drop down list based on a value selected in a previous drop down list
              philmodjunk

                   You have two options:

                   1) use a pop up menu instead of a drop down list. If your value list hides the ID number in the "first field", then the pop up menu will display the name even after exiting the field.

                   2) Hide the drop down list field behind the name field from the related table. Use Inspector behavior settings to deny browse mode access to this name field. If your hidden drop down list field has the "arrow" option selected, remove that option. When you click on the name field, the drop down list hidden behind it will pop to the front and display the value list of names. When you select a value, the list will disappear back behind the name field and the name field will now display the selected name.

              • 4. Re: Populate drop down list based on a value selected in a previous drop down list
                RyanBleam

                     Sorry, I used the wrong terms. The first selection where I pick the hotel name is a drop down. My second option where I want to see the name is a pop up menu. This pop up menu is the one displaying the ID first. When I change the value in the first selection again, I need to click on the pop up menu again to display the new name. I'd like this populated automatically when I change the value in the first selection.  

                • 5. Re: Populate drop down list based on a value selected in a previous drop down list
                  philmodjunk

                       Now that has me confused! wink

                       

                            The first selection where I pick the hotel name is a drop down. My second option where I want to see the name is a pop up menu.

                       So far so good.

                       

                            This pop up menu is the one displaying the ID first.

                       Why not hide the ID and have it show the name? Why do you need to see the ID? Or is that the case until you change the value selected in the first value list field?

                       

                            When I change the value in the first selection again, I need to click on the pop up menu again to display the new name. I'd like this populated automatically when I change the value in the first selection.

                       How's that again? What new name? If this second field is a conditional value list, changing the value in the first field will require that you select a new value from this second value list formatted field as there could be multiple options to choose from. There's not enough info at that point to automatically select a new value in the conditional value list--only enough info to specify a group of values to display in that list from which you would select a new value.

                       You might, on the other hand, use an OnObjectSave Trigger on the first value list field to automatically clear the second value list field. But you'll still have to make a selection from it to select a new value from the new list of values.

                  • 6. Re: Populate drop down list based on a value selected in a previous drop down list
                    RyanBleam

                         Ok, this makes sense and I appreciate you helping out!. I found out how to set a trigger with the OnObjectSave, how would I write a script to say clear the second value list field?

                    • 7. Re: Populate drop down list based on a value selected in a previous drop down list
                      RyanBleam

                           Nevermind, I figured it out. Thank you very much. This works perfectly now!