1 Reply Latest reply on Aug 15, 2012 1:36 PM by philmodjunk

    Need help with Drop down Box lookup from related table



      Need help with Drop down Box lookup from related table


      I'm new to Filemaker Pro - been an Access user for years.  I have two tables - one "Booklets" with an autonumber BookletID for key field that includes as one of its fields "AuthorID".  The other table is "Author" with AuthorID as the key field and AuthorName as a text field.  I have related the two tables by AuthorID with a one to many from Author to Booklets.  I was wanting to create a dropdown box for the Booklets Table that shows the Author name but actually stores the AuthorID from the "Author" table as a lookup.  How is this accomplished?

        • 1. Re: Need help with Drop down Box lookup from related table

          Open manage | valuelists and create a new value list.

          Select the "Use values from a field" option and select Authors::AuthorID as the primary field. Then click the "Also display values from second field" option and select Authors::AuthorName as the secondary field.

          This is the minimum requirement. You can set up your field as a drop down list and you can select an author by name, but the value list enters the ID number.

          variations on this theme:

          You can sort on the secondary field to get an alphabetized list of author names.

          You can hide the AuthorID field and only display the names.

          Both options require that AuthorNames be unique. It is good design practice to specify unique values as a field validation for author names if you use either of these options.

          You can use this value list with either the drop down list or pop up menu format options.

          If you specify a pop up menu and hide the primary field, the author name will be displayed in the field when you exit the field even though it stores the ID number. If you delete an author record however, the pop up will change to display the ID number for any records where the ID from the deleted author record was selected.

          If you specify a drop down list, with the primary field hidden, you'll only see the author names in the drop down, but the ID number will show in the field when you exit the field. You can, however, conceal the drop down behind the name field from the related author table if you make that field opaque and prohibit browse mode access. When you click on the front name field, the drop down will pop to the front and deploy. When you exit, it will disappear back behind the name field.

          Auto-complete does not work in FileMaker value lists when the primary field enters a number. There are several ways, however, to use scripting and/or special relationships so that you can use a drop down list of author names with auto-complete enabled that enters a name into the field, but looks up the ID into the ID field. Scripting is used in these cases to handle cases where you might get two authors with exactly the same name.

          Final note: Since a book can have more than one Author and Authors write more than one book, you may need to use a join table to implement a many to many relationship between authors and books.