10 Replies Latest reply on Feb 22, 2016 5:19 AM by cillion

    Dropdown list to show other values

    cillion

      Hi!

      Im trying to make a layout that you can pick a value(name) from a dropdown, after you have choosed this field all the other fields will show. So the other fields will show as empty till the name is choosed. And I dont want to make a new record for every search.

      Someone know how to do this?

        • 1. Re: Dropdown list to show other values
          richardsrussell

          Let's say the field you're trying to fill from your drop-down list is called "YourField", and it's in "TestTable". Select ALL the other fields on that layout and format them as follows:

           

          Hide Objects.jpeg

           

          In this case, it's just a simple 1-line statement that controls the hiding of the other objects, which you can just type directly in that space, but you can click on the pencil icon to create quite elaborate conditions for hiding and showing layout objects.

          • 2. Re: Dropdown list to show other values
            jgomes384

            Create a table (let's call it Search) with a single Global field: searchKey.

            Create a relationship between searchKey and the Name field in your data table.

             

            On the layout (based off of your Search table) place the searchKey field then all data fields you want to show from your data table.

             

            Set the searchKey field as a dropdown. Now anytime you change the searchKey value, the relationship will change and pull up the relevant data.

             

            You can set the searchKey to "" on layout entry so it appears blank.

             

            Note- this will only work well if you only have one instance of each distinct name in your data.

             

            Hopefully this makes sense...

             

            -Jason

            • 3. Re: Dropdown list to show other values
              richardsrussell

              As you can see, given all the tools FileMaker Pro makes available to you, there's more than one way to pet the cat.

              • 4. Re: Dropdown list to show other values
                siplus

                Like for example a onModify trigger on the dropdown, if it's empty you go to a layout where there's only the dropdown otherwise you go to the layout where the dropdown and all the other fields are.  This is for ppl with FM < 13 who don't have "hide when....".

                • 5. Re: Dropdown list to show other values
                  cillion

                  I testet this solution now. The problem here is that when YourField is selected once, the other fields will stay there. If the user make changes to the YourField the other fields must compare to that value again, how to do this ? Do you know?

                  • 6. Re: Dropdown list to show other values
                    richardsrussell

                    I'll make up an example here of how the contents of a second field can depend on the value of "YourField". Let's say that you're running a travel agency and "YourField" is the destination that somebody might want to travel to. If the destination is Paris, the price of the trip is $800; if it's Rio de Janeiro, it's $1000; if it's Toledo, Spain, it's $1100; if it's Toledo, Ohio, it's $29.95; and so on. You create a table called "Destinations" which has (for present purposes only) just 2 fields, "City" and "Price", and you populate it with data such as the examples from the preceding sentence.

                     

                    Then you create a relationship in which "TestTable::YourField" equals "Destinations::City". "TestTable" will use that as the basis for a lookup from "Destinations".

                     

                    Let's say that a second field in "TestTable" is called "Cost of Trip". Find it in your list of fields and double-click on it to call up this dialog box:

                     

                    Cost of Trip Options.jpeg

                     

                    Click on the "Specify..." button opposite "Looked-up value" to call up this dialog box:

                     

                    Lookup Specs.jpeg

                     

                    Make it look like what you see here: Start at "TestTable", look up from the "Destinations" table, specifically the "Price" field, and if there's no exact match, don't look up anything. (Exception: Your specs will say "(When a new entry is made in the field 'YourField' ...", not "(When a new entry is made in the field 'One' ...".)

                     

                    OK your way back out.

                     

                    Thereafter, any time you change the city name in "YourField", FMP's amazing look-up powers will automatically update the cost of the trip for you.

                    • 7. Re: Dropdown list to show other values
                      richardsrussell

                      Here I am replying to myself, which is a sure sign of schizophrenia.

                       

                      However, re-reading what I posted above, I had an additional thot about the value list that you'd be using for "YourField" if you were actually using it in the travel-agency example. You could set up a 2nd relationship between "TestTable" and "Destinations" that would use the cross-product relationship. That's one in which all of the 2nd table's records are linked to any record in the 1st table. Here's what it would look like, using the name "AllDestinations" for that 2nd table occurrence (TO) of "Destinations":

                       

                      Cross Product.jpeg

                       

                      (Incidentally, the "One" field lives in every table I create. It's an indexed calculation field = 1, and it's useful for all sorts of things, including creating these cross-product relationships.) If you click on the little box between the 2 tables you'll see the relationship's specifications dialog box:

                       

                      Find Cross Product.jpeg

                       

                      That drop-down list in between the 2 tables is where you get to specify the nature of the relationship (using operators like =, >, x, etc.).

                       

                      Then, when defining the value list you intend to use as the basis for the drop-down list in "YourField", base it on the "City" field as viewed in the "AllDestinations" TO:

                       

                      Value List Cities.jpeg

                       

                      You'll end up with a drop-down menu within "TestTable" that reflects all the cities in your "Destinations" table:

                       

                      Drop-Down List.jpeg

                       

                      Now, it doesn't take a lot of imagination to realize that such a drop-down list would quickly grow to be quite long as you added more cities to your "Destinations" table, so in a little bit I'll add another reply that explains how you can make it more manageable.

                      • 8. Re: Dropdown list to show other values
                        richardsrussell

                        OK, here's the promised followup to the followup. In this one I'm going to change the cross-product ("x") relationship between "TestTable" and "AllDestinations" to a simpler "=" one to the TO that I've renamed "Destination Picker":

                         

                        Destination Picker.jpeg

                         

                        You'll notice that this relationship has a match field named "City Initial" on either side of it, but they're not defined in the same way. The one in "TestTable" is a simple text field:

                         

                        Initial in TestTable.jpeg

                         

                        whereas the one in "Destinations" is calculated based on the name entered in the "City" field:

                         

                        Initial in Destinations.jpeg

                         

                        This will, for example, produce the value "M" when the value in "City" is "Madison, Wisconsin".

                         

                        Then modify the definition of the "Cities" value list like this:

                         

                        New Value List Definition.jpeg

                         

                        In particular, notice that the redefined value list will include only related values, not all values as it was with "AllDestinations". That means that, when you type a "T" in the "City Initial" field in "TestTable", the value list that drops down will have only the cities that also have that initial:

                         

                        Drop-Down List Limited.jpeg

                         

                        This will give you a manageably sized drop-down. Hope that helps.

                        • 9. Re: Dropdown list to show other values
                          cillion

                          Thanks alot for all the different posible solutions guys. But sorry am new to this and some of these is over my head, so I still can do this in my solution.

                           

                          Let me explain my setup now:

                           

                          All the information rounded with green should show only the related value inserted in the red rounded field.

                          2016-02-19_23-12-48.png

                           

                          2016-02-19_23-12-28.png

                           

                           

                          2016-02-19_23-04-05.png

                           

                          2016-02-19_23-03-07.png

                           

                          2016-02-19_23-27-28.png

                          • 10. Re: Dropdown list to show other values
                            cillion

                            Richard, see the example in this file: WeTransfer . The case is that it dont seem to be show only the field related to that YourField.