8 Replies Latest reply on May 6, 2011 11:35 AM by jetmaster

    Related Field and Value List

    jetmaster

      Title

      Related Field and Value List

      Post

      I have two tables. One is a db of laboratories and the other is a db of people who staff them.

      In the LAB db I have a primary key of the lab ID # and a field called Lab_Name.

      In the STAFF db I have a primary key of the staff ID # and a foreign key for the Lab_ID and also a foreign key for Lab_Name. Not sure if this is the correct thing to do though.

      On the layout for the staff db I want to have the Lab_Name field from the Lab db and I want it to be a value list drop down menu which I have set up as such. For each staff member I want to indicate which lab they are affiliated with by choosing a lab from the drop down menu.

      The problem is when I click inside that field, no list appears. Nothing happens even when I relate the two tables. I think I am not relating them properly though. I'm not sure what fields i have to relate in the two tables.

      Does this make any sense at all?

        • 1. Re: Related Field and Value List
          philmodjunk

          You appear to have this relationship:

          Laboratory::LabID = Staff::LabID

          If so, then you can do this instead (this is the simple design, a more sophisticated approach is also possible):

          Place the Lab_Name field from Laboratory on your Staff layout.

          Format Staff::LabID as a drop down list where the values in column one of this list are Laboratory::LabID and the values in column 2 are the lab name.

          When you select a lab in the drop down, the matching lab name will automatically appear on your layout.

          Note, if you want the drop down list of lab names so that you can use an auto-complete enabled drop down where you type in a few letters and the list filters down to just the matching names, this can also be done, but then you have to add a script to look up the corresponding LabID number to enter into the foreign key field in the staff record.

          • 2. Re: Related Field and Value List
            jetmaster

            I am confused (or ignorant :) )

            You say place the Lab_NAME field on the Staff layout BUT then you say format Staff::labID as a drop down menu (not Lab_NAME). How do I format a field that is not on the layout? Did you mean Lab_NAME and not Lab_ID?

            Also you mention COLUMNS in formatting the drop down list and I have no idea what you are referring to there....

            • 3. Re: Related Field and Value List
              philmodjunk

              How do I format a field that is not on the layout?

              Add the field to your layout. You'll need it if you intend to link staff records to specific lab records.

              When you set up a value list, you can specify that the values in the value list are drawn from the fields of a table you select. (Open Manage | Value Lists, start a new value list and check out the options you have when you use the specify field option.) You can display two columns of data for your value list. The data in the first column is what is entered into the field when you select that value. The second column provides additional data to help the user make the correct selection. In this case the name of the Lab will help you select the correct Lab while entering a lab ID number.

              • 4. Re: Related Field and Value List
                jetmaster

                OK thanks that works but why do I need the Lab_NAME field on the staff layout then?

                • 5. Re: Related Field and Value List
                  philmodjunk

                  You don't need a lab name defined in the staff table at all.

                  Adding the lab name field from the lab table to your staff layout serves to show what name is selected for a given staff record. It's an option. You can also configure the drop down list as a pop up menu, hide the first column (an option in Manage Value list) and then you do not need to add the lab name field to the layout as it is now displayed in the Lab ID field. (This can be confusing at first as you are entering the Lab ID, but the popup menu displays the matching lab name.)

                  • 6. Re: Related Field and Value List
                    jetmaster

                    OK last question (I am sure we both hope!):

                    When I add the lab name field from the lab table to the staff layout it says UNRELATED TABLE.

                    If I relate the lab name in lab table to the staff ID key in the staff table nothing happens...just like at the start of this...

                    sorry so many questions

                    • 7. Re: Related Field and Value List
                      philmodjunk

                      In Manage | Database | Relationships

                      Drag from LabID in the one table to LabID in the other so that they are related by LabID, not by name. You don't even need the Lab name field in the staff table.

                      • 8. Re: Related Field and Value List
                        jetmaster

                        WOW. Works perfect! Thank you so much. You are an amazingly patient and knowledgeable resource!