3 Replies Latest reply on Mar 3, 2014 10:06 AM by philmodjunk

    How do I select a Parent record via a dropdown list?



      How do I select a Parent record via a dropdown list?


           I need help figuring out how to link a child record to a parent record via a dropdown list.

           I have a parent table and a child table, with a one to many relationship defined via a primary key in the parent table and a foreign key in the child table.  

           I want to create a layout for child table instances and include a field from the parent table.  I want to be able to, for a given instance in the child table, to select a record in the parent table and create a link b/t the two records (in other words, store the primary key from the parent table in the foreign key field in the child table).

           It seems like this is a basic functionality, but I cannot see to figure out how to make it work.



        • 1. Re: How do I select a Parent record via a dropdown list?

               Let's say that you have these tables, fields and relationships:


               Parent::__pkParentID = Child::_fkParentID

               If so, then on your Child layout, you can format _fkParentID with a drop down list or pop up menu. The value list would be defined in Manage | Value Lists with the "use values from a field" option. This opens a dialog where you can select two fields for values used with this value list. For the first field, select __pkParentID from Parent. Then click the "also display values from second field" check box and select a name or description field from Parent so that users can select a parent record using data that is more user friendly than just a number or string of UUID text. You can choose to hide the first field to only show the second field provided that the text from the second field is unique for each record in Parent.

               There are a number of ways to work with that basic value list, but keep in mind that it's the simplest option to set up. It works well for short lists of values, but not as well for longer lists. But once you have the basic method working, there are a number of options for making longer value lists easier to work with.

          • 2. Re: How do I select a Parent record via a dropdown list?

                 Excellent.  I have got that working. Thank you!

                 One additional question:  I figured out how to have the value list show a second field (a text field, say ParentName to go with the first field Child::_fkParentID), but I cannot figure out how to have the field that I am displaying in my layout display the ParentName, instead of the ID field.  I want the ID fields stored behind the scenes, with the user not seeing them.

                 Also, any suggestions on good online sources for managing this?  I have done a few searches and listened to tutorials, but I can't seem to find any specific sources on this topic.

                 Thank you!


            • 3. Re: How do I select a Parent record via a dropdown list?

                   If you use popup menu instead of drop down list and your value list is set to only show the ParentName field, this will happen automatically.

                   To get the same result with a drop down list field takes a bit of sneaky layout design. Take the ParentName field from the related table. Give it an opaque fill color. Use behavior settings in the inspector to deny access to the field when in browse mode. Put it on top of your drop down list field (don't select the "arrow" option for your drop down list.).

                   Now, when you click on the ParentName field, the drop down list hidden behind it pops to the front and deploys the value list. Once you select a value, the drop down list disappears back behind the ParentName field and the ParentName field updates to show the Parent Name of the selected ID number.