Knowing how to use a FileMaker Join table ( though not exclusive to FileMaker) can solve many data-model related questions for us. As we fiddle with the Entity Relationship Diagram to work out the exact relationship of entities for our custom apps, we can rely on this join table technique to properly structure seemingly complex data models. Let’s take a look at how this concept is used in our work.
What does a FileMaker Join Table Solve?
The join table, as it is commonly called and known, solves the complex problem of how to structure the data model when the entities are related in a many-to-many style. A classic example of these entities includes classes and people. Many people have many classes. A join table joins these two entities together.
The join table shows the data related correctly with no duplication of records. If I needed to set up a roster of students, and I didn’t use a join table, I’d have to duplicate either student or class records. For example, in the class table, I’d have to have one record (with the same class) per student. In the Classes table? That’s a problem. Classes is an entity: one record per unique class. I shouldn’t duplicate class records to just make room for all the people.
What is a Join Table?
Join tables are simple tables that hold the primary keys of the tables it is joining together. In the above example, a join table has a field holding the key for the Person record. It also contains a field for the key of the classes record.
This table holds one record for every unique combination of the joining entities. In this case, it holds one record for each student and for each class they are in. If Jamie is in five classes, this table holds five records with Jamie’s primary key value and each one of her classes’ primary key values.
Naming a Join Table
It’s a small but vital point: naming a join table. It’s actually really difficult to name things, at least it seems so. Here are some naming options:
- The unoriginal but descriptive and easy-to-generate name with the two joining tables concatenated: “People_Classes”. That works. It tells you what’s in the table. But “People_Classes” as a name does not describe its entity: what is in this table. What this table represents.
- The hard-to-generate name that describes the purpose of the table, what the table is holding. Instead of “People_Classes” I’d use “Enrollment” or “Register” or something like that.
Pick a naming style. I think the 2nd choice is better. My future self will enjoy seeing “Enrollment” vs “People_Classes”, I think.
Once the data is set up, the next step is to create the viewing area. A FileMaker join table gives us the freedom to view its contents (and thus the correct data) from both sides of the join table or from the table itself.
On a student record
From a student record, that is, from the Students table occurrence, we can see all of the classes in which she is enrolled. And we can do this through, as usual, a portal. But what from what table occurrence will the portal be drawn? The description above gives the answer: “. . . in which she is enrolled.” The portal on the student layout comes from the enrollment table. We’ve established that the enrollment table holds one record per class per student, so one student’s class list is found here.
However, as I described above, this enrollment table only contains foreign key fields. From where does the class name come? Simple: we add the class name field from the classes table through this relationship.
On the Classes Record
From the classes table occurrence, we can see all the students in each class. Again, a portal sourced from the Enrollment table is set up. And by adding the student name field into the portal, we see all the students in the class.
From the FileMaker Join Table
The join table is best used for a list, a report of the students in each class. Since we need the student name and the class name, we simply add these fields to the report layout.
So how are records added to this FileMaker join table?
Let’s take a typical Use Case. The principal of a school needs to enroll students in classes. She would go to the classes layout and, one at a time, add students to classes. (Forget the fact that this is terribly slow 🙂 ). So she starts on “Algebra” and needs to add students. So where is the new record created?
If you stop and think about it, there’s only one place: in the join table, I mean the “enrollment” table. In this table we create a record with the current class’s primary key and a student’s ID.
So then the script would follow these steps:
- Get the ID of the class in a variable.
- Get the ID of the selected student in some manner in a variable
- In the join table, Enrollment, create a new record.
- Set the foreign key fields with the variable data
- Close the window.
- Refresh the current layout.
The demo file attached shows my scripting method. Look at the Classes layout. Step 2 above is the most challenging of the steps (though it isn’t challenging at all).
I’ve also, on the Student layout, used another method for adding records to the join table.
The Problem Solver’s Data Structure
The Join table solves a complex problem for us. It allows our clients to see complex relationships in their data. And it is a high-level tool that gives us more power at our fingertips.
In this video, I illustrate the above post.
Download the sample file here.
Also published on Medium.