I think you are describing a many-to-many relationship (i.e. one teacher has many students and each student has many teachers). This is generally handled using a third "join" table. The join table (Teacher_Student) contains only one teacher id and one student id per record. It is used as the portal basis on both the teacher and student layouts.
The relationship looks something like this:-
Teachers -< Teacher_Student >- Students
I have a question about how this works. Do you enter all the records on a layout based on the join table? And are the Student and Teacher tables empty?
Only the portals are based on the join table. The Teachers and Students records are not empty; they contain the names and other data related to each.
Setup the relationships to allow creation (and deletion) of the records in the join table via the relationships.
The portal on the Teachers layout contains the Teacher_Student::Student_id and Students::Name (plus any other student data fields you want). Most likely, the Teacher_Student::Student_id is a drop-down menu based on a Student value list. When you select a student from this list in the portal, it will automatically create a join record with the Teacher_id and Student_id.
The portal on the Students layout contains the Teacher_Student::Teacher_id and Teachers::Name (plus any other teachers data fields you want). Probably, the Teacher_Student::Teacher_id is a drop-down menu based on a Teachers value list. When you select a teacher from this list in the portal, it will automatically create a join record with the Teacher_id and Student_id.
So, using the Teachers layout you can assign students to a Teacher. Conversely, you can assign teachers to a student on the Student layout. FMP maintains the join table records via the relationships.
Thanks Jade! Took me a million times of reading your reply for it to sink in! But it works! Thank you!!!!!
A followup question.
With the same setup, how can I have the Teacher Form create a new student and still keep it associated to that teacher within the same portal? I can select a student previously entered, but right now, I cannot enter a new student.
Any help would be great!
Well, I'm glad you have it working. It is far easier to do than describe. I was hoping that you wouldn't ask your last question…
You can add another portal to the Teachers layout based on the Students table. Check the "allow creation of records via the relationship" checkbox for Students on the Students to Teacher_Student relationship. This will allow you to add a New Student record in the last row of this Students portal. Since the Students record is added from a specific Teachers record, you will find that FileMaker will also add a new record to the join table automatically. In turn, the new Student record will also be displayed in your first portal that is based on Teacher_Student.
So why have 2 portals? Here is the downside (at least I haven't found an easy solution): you cannot use this new Students portal to assign any existing Students to the Teachers record because it is based on the Students table and not on the join table.
It may be neater to allow the user to switch to the Students Layout instead (perhaps with a scripted button or open a new window).