3 Replies Latest reply on Feb 17, 2012 11:30 AM by philmodjunk

    Creating dropdown lists from a child table



      Creating dropdown lists from a child table



      I am very new to Filemaker.  I am using 10.0v3.  I am not as new to relational database design. 

      I have two tables- an Organization table and a Religions table. Each organization can only be associated with one religion. I set my religions up in a separate table because I may use this table for other relationships as the project expands. Each religion has a Religion_ID_pk that I have as a fk in my org table.

      In the data entry screen I would like the list of religions from my religions table to appear as a drop down for a user to select from and then have the org table store the associated Religion_ID assigned that religion. I am having trouble with this.  Could anyone lend some insight?  



        • 1. Re: Creating dropdown lists from a child table

          Open Manage | value lists and click new to start a new value lists.

          Enter a name for your value list and select the "Use values from Field" option.

          In the left hand drop down, select an occurrence of your Religion table. Then click Religion_ID_PK to select this primary key as the value for this "first field". Then select the "Also display values from second field" check box and select the name field from your religions field for field 2. If you click ok at this point and use this value list with a drop down list or pop up menu, you'll see a two column list of values with the ID in column 1 and the name in column 2. If, however, you select the "show values only from second field" option, then the ID's will not show in the value list. In either case, selecting a value from this value list enters the ID of the relgion you select from the value list.

          There's a key difference between the pop up menu and drop down list options when you select the "Show from second field" option: When you exit the field, a field with drop down list formatting will display the ID number entered into the field. If you use a Pop up menu format, the entered value is hidden and you see the field 2 name associated with the selected ID instead. In cases where you prefer the drop down list, you can define a relationship between the two tables and add the name field from the related field to your layout to display the name.

          Note, however, that there is am important aspect to this type of value list when you select this "show from second" or when you specify sorting values by the second field: If two records have different ID's but the same name field used for field 2, only the first value will be visible in the value list, the duplicate names will be excluded from the list. This is actually good interface design, but catches new developers by suprise from time to time.

          • 2. Re: Creating dropdown lists from a child table

            This was super helpful thank you. And to anyone else out there who may search this at some point for assistance I would add one last step that was causing me problems:
            when you are setting up your drop down, where it asks for Dispaly data from: select the parent table (probably the current listed table that you working in to create the drop down on the data entry layout, but at some point in my infinite wisdom I changed this). You want to show data from the fk field that is being replaced by a display from your value list, not the table and field from which the value list is derived.

            • 3. Re: Creating dropdown lists from a child table

              That's an excellent follow up comment. We "old hands" often forget the "gotchas" like that which can trip up a "newbie".Wink