6 Replies Latest reply on Dec 18, 2009 1:33 PM by TSGal

    Lookup Values

    aarono_1

      Title

      Lookup Values

      Post

      I need to create a field with a lookup value that gives values depending on what the previous field displays.  e.g. If one filed displays 'PC' then I need the next field to show dropdown list of the possible PCs.  Or if one field says 'Monitor' I need the next filed to give a drop down list of only the available Monitors... not all monitors, PCs, printers....... etc.

       

      Anyone know how to do this?  I have a separate table which includes all the different types of pcs and monitor etc that we need the other table to read from.

        • 1. Re: Lookup Values
          TSGal

          aarono:

           

          Thank you for your post.

           

          This is also known as a conditional value list.  There is a detailed Knowledge Base article about this on our web site.

           

          http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5833

           

          It is printed below for your convenience.

           

          TSGal

          FileMaker, Inc. 

           

           

          Conditional value lists are value lists that can dynamically update themselves based on the selection of a value in a related field. Conditional value lists can have multiple levels. For example, if you select a value from one field, it will determine the values in the next field and your selection for that field will determine the values in the next field, and so on.

          The following example will give you a basic understanding of how to create conditional value lists:

          1. Create a database called "Menu.fp7". 
          2. Define the following fields: 

            Type (text) 
            Item (text) 

          3. In Browse mode populate these two fields with the following data: 

             Field: Type Item
             Record 1 Lunch Soup
             Record 2 Lunch Sandwich
             Record 3 Lunch Hamburger
             Record 4 Dinner Chicken
             Record 5 Dinner Steak
             Record 6 Dinner Fish & Chips
             Record 7 Dessert Ice Cream
             Record 8 Dessert Popsicle
             Record 9 Dessert Pudding

          4. In the Define Database dialog box, under the Table tab, create a new table called "Order" and define the following two fields within this table: 

            Type (text) 
            Item (text) 

          5. In the relationships graph create a relationship from the "Type" field in the Order table into the "Type" field in the Menu table.
          6. In the Order table in Layout mode, double-click the "Type" field, or select the field and chooseFormat menu > Field/Control > Setup and the Field/Control Setup dialog box will appear.
          7. In the Control Style area, for Display as, choose Pop-up Menu.
          8. From the Display values from list select Define Value Lists…
          9. In the Define Value Lists dialog box, click New. 
          10. In the Edit Value List dialog box, for Value List Name, type a name such as "Type of Items" and select Use values from field.
          11. In the Specify Fields for Value List dialog box, for Use values from first field, select the "Menu" table, and then select the "Type" field in the list.
          12. For Value List Content, choose Include all values and click OK until you exit all dialogs.
          13. In the Order table in Layout mode, double-click the "Item" field, or select the field and chooseFormat menu > Field/Control > Setup.
          14. Next the Field/Control Setup dialog box appears. In the Control Style area, for Display as, choose Pop-up Menu.
          15. From the Display values from list select Define Value Lists…
          16. In the Define Value Lists dialog box, click New.
          17. In the Edit Value List dialog box, for Value List Name, type a name such as "Name of Items" and select Use values from field.
          18. In the Specify Fields for Value List dialog box, for Use values from first field, select the "Menu" table, and then select the "Item" field in the list.
          19. For Value List Content, choose Include only related values starting from "Order" and click OKuntil you exit all dialogs.

          Now when creating new records in the Order table the value you select in the "Type" field will determine the results you have in the "Item" field. For example, if you choose "Lunch" from the pop-up menu in the "Type" field your choices in the "Item" field will be soup, sandwich and hamburger.

          As you add and remove records from the Menu table the value lists in the Order table will change accordingly.  

          • 2. Re: Lookup Values
            aarono_1
              

            Thank you for this.  It has worked and is exactly what I needed...... however, I now am web sharing the database on our intranet and the 'filtered' field displays nothing where it should be displaying the options determind by the previous field.

             

            It all works fine directly from the datebase, but not in a browser.  Is there anything I can be doing wrong?

            • 3. Re: Lookup Values
              guessmaster_1
                

              I have created a database with a conditional value list. The database is growing and now I have added an extra field. So I need a second generation conditional value list.

              For example I had "State" Field and a "City" field and now I want to add "Country" field.

              How do I do this? 

              • 4. Re: Lookup Values
                TSGal

                guessmaster:

                 

                Thank you for your post.

                 

                I believe you want the Country field as the primary key.  Then, you can select the State.

                 

                For the City field, you would just add another level, but instead of just one key in step 5, you would use two keys; Country and City.  Everything else would be the same.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Lookup Values
                  patrickgreenetvg
                    

                  TSGal, I am just about there with your information. That is the second field values are determined by the value in the first field. But on my third and fourth fields the values are only dependent on the first field and do NOT take into account the selection in the second and third fields. I have tried to implement your instruction to create an additional "Key" (relationship correct?) in the relationship screen for the DB, but that is not doing anything for me.

                   

                  I would appreciate your tips for making the third and fourth fields conditional on the fields selected prior to their selection.

                   

                  I am using FileMaker Pro 10.0v1 on Mac OSX.

                  • 6. Re: Lookup Values
                    TSGal

                    patrickgreenetvg:

                     

                    Thank you for your post.

                     

                    If you need to go another level deep, then create a new table occurrence by going into the Relationships graph and add a new table, specifying the same table.  Then, link the second field to the second field in the second table occurrence, and when you specify the pop-up for the third field, you base the relationship on the second field in the second table occurrence.

                     

                    For the fourth level, you add another table occurrence, and link the third field to the third level/field in the third occurrence of the table. 

                     

                    If you need clarification, please let me know.

                     

                    TSGal

                    FileMaker, Inc.