If you set this up with one name per field (best design is one name per record), you can define a value list of names from the other table with auto-complete enabled so that you need only type in a few letters to narrow the list down to just a few or even one name to select for the field.
- Open manage | value lists
- Create a new value list and use the "Use values from field" option to list the names from the specific name field in your table of names.
- Click OK to dismiss this dialog.
- Then select a text field on your layout where the user will enter a name and use the data tab of the inspector to format it as a drop down list. Enable the "autocomplete using value list" option and you have your value list.
Ok, thanks for the guidance, PhilModJunk… but I’m still having trouble. Maybe you can point out my error? Here's some more detail.
I have a “trip” table set up to enter trip descriptions. I want a portal set up to show participants for any given trip, based on a list of eligible students. I have a “student” table set up listing the eligible students (along with other information, like teacher, classroom, etc), and have made a value list based on that table. I have made a third table which is the “participant list”, which only has a “tripID” field and a “student ID” field. The tripID field in the trip table and the studentID field in the student table are unique, uneditable serial numbers; in the participant list table, they are just numbers. The intent is to add records to the participant list which relate to the student list.
The participant list table is related to the trip table as a child, and also to the student table as a child. In the “edit relationship” window between the trip table and the participant table, I have checked “allow creation…” and “delete related…” under the participant table. I have checked the same boxes for the participant table in the participant/student relationship options.
The main layout (so far) is based on the trip table, with a portal for students. The portal is set up to show last and first names from the related student table. The first field (last name) is set up as a drop down field, using the student value list.
But it doesn’t work, and I can’t understand what’s going on. If I enter fields directly into the participant list table, and manually enter the tripID, they show up in the trip layout portal. But I can’t add participants through the portal, though as noted above I’ve checked “allow creation” for the participlant list table. Matter of fact, if there are no names already in the portal, there’s no drop down arrow, nor can I select the field in the portal. Other fields in the trip table, are editable.
I tried having the tripID in the participant list be updated from the trip list tripID, but that doesn’t seem to work either.
This is a classic many to many relationship and you appear to have set it up correctly.
But your layout based on trips, should show a portal to Participant_List instead of students. You can then use a drop down list of students to select them for a trip. I would not base the relationships in this database by student name, however, as this will cause problems if two students have the same name or a student changes their name. (step parent adoption, for example)
Here is a many to many demo file you can look at and examine for ideas: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html
You may find the "check box" layout a useful way to select students for a trip.
Feel free to post back here with questions.