The following example from FileMaker Help explains the set up of my question perfectly. I just need help taking it one step further: I would like to have a layout based on the Students Table and with a portal that shows all the classes available (in the Classes Table) with the classes that the student has chosen marked with an x. In other words, in the layout, I would like the student to see all the class options as well as see the classes they have marked/chosen all in one portal. What is the cleanest way to accomplish this? Help finding an answer will be greatly appreciated!
A student can take many classes and a class can have many students. Relating these two tables together would be difficult without the use of a join table. The join table will contain the primary keys from the student table and the class table. The join table can be called anything you want. For this example we will call the join table “signups.”
- Create a new table in FileMaker Pro and call it "Signups"
- In the “SignUps” table, create a primary key field called “SignupsID.” This step is not necessary for the jointable to work properly but it is good relational database practice for every table to have a primary key.
- In the "SignUps” table, create, at least, a “StudentID” field and a “ClassID” field. Since these fields will be populated when records in either the “Students” or “Classes” tables are created, no auto-enter field options are needed.
- Additional fields can be added to the “Signups” table as necessary. Some examples of other fields that might be appropriate for the “Signups” table would be a “date” field to keep track of when someone started a class or a “cost” field to keep track of how much someone paid to take a class. Join tables typically hold fields that might not make sense to have in any other table.
- Relate the tables together based on the primary and foreign keys
If a student signed up for three classes, a student would have one record in the Students table but would have three records in the “Signups” table – one for each class he or she signed up for.
A by-product of utilizing a join table is that you are able to access fields and data across tables without having to create a separate relationship. For example, if you wanted to display a list of all the classes that a student was signed up for you could do the following:
- On a layout based on the Students table, create a portal. The portal should be configured to show related records from "Classes"
- Add the appropriate fields from the Classes table into the portal
Now, as you browse through records in the Students layout, the portal will display all the classes a particular student is signed up for.