Join Tables in FileMaker Pro


FileMaker has a good overview of the different types of relationships you might create when building a database system in its help section. I wanted to illustrate it better for a client, this is my attempt.

Overview

One-to-Many Relationships are the standard way of building a relational database. Sometimes a developer comes up against situations where they need something more complicated. Think of instances where you might need Many-to-Many Relationships. In the situation I am working on, we needed a way for multiple Customers to be linked to a single Form. The form was a waiver and customers were signing up for themselves and their children at the same time on the same waiver form. We needed a way to link the waivers not just to the parent, but also to the children.

One-to-Many Relationship

Standard database relationships are created by linking ID numbers between data tables. These fields are referred to as key fields. The image above is an example of a regular database relationship—one record (the Customer) can be linked to multiple (Form) records. In this example a Customer record could be continuously updated with a more current form — One Customer to Many Forms.

Many-to-Many Relationship

According to FileMaker’s Help page on Join Tables, “In relational database design, a many-to-many relationship is not allowed.  Consider the example of keeping track of invoices.  If there were “many” invoices with the same invoice number and one of your many customers called up asking about that invoice number; how would you know which specific one they were referring to?  The answer is you could not.  An invoice should have a unique “ID.”

To get around the problem of having a many-to-many relationship you need to break apart the many-to-many relationship into two one-to-many relationships.  Using a third table, commonly called a “join table”, does this.  Each record in the “join table” would have the foreign key fields of the two tables it is joining together.  Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created.  It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins.”

Adding a Join table between the two existing tables allows the possibility of linking multiple records to the same record. More than one Customer record can be linked to a single form.

Examples

This example imagined above might be appropriate where two Customers are from the same family and they share a single form. This setup also supports the previous capability of having one Customer linked to multiple Forms.

Another common example of a join table use is a school with Students, Signups and Classes. Each student signs up for multiple classes and each class can have multiple students. The Signups data table is the join table.

Relationship Graph

Customer data table is linked to the Join table. Join table is linked to the Waiver table.

Join table has at least these three fields:

  1. ID Customers
  2. ID Waivers
  3. ID Join

Portal View

From the Customers layout, we can reference the Waivers table (one jump away on the relationship graph) through a portal.

Advertisements

7 Responses to “Join Tables in FileMaker Pro”

  1. So many explanations on relating tables but I have yet to find an explanation on how you take data from a database and import to a clone, or for example if you end up having to start a build from scratch and still want to use a working data set. For example allowing filemaker to auto enter serial numbers during import would munge the tables necessary order to allow the tables to continue to read other tables correctly and in the correct order. How do you import related tables and after you have imported them, how do you verify your data set is intact?

    • Good questions. There was a session at DevCon 2015 on building a data migration system by Jonn Howell, President of DataExperience, Inc. He covered most of the pain points you are bringing up in the process of importing files from an existing system. He talks about automating the process.

      If you are a member of the FileMaker Community web site (why not? It is free). There is a video of the session with slides and a demo file here: https://community.filemaker.com/docs/DOC-6017

      • Thanks Douglas,

        I was hoping to make FMDC this year but couldn’t due to a number of reasons. I went to FMs website to try to get the jump on next year and they don’t have anything posted yet. I am shooting for the FMDC 2016 and hopefully I will make it. I also plan to get a FM Dev Subscription. I’ve wanted one for a long time and only just realized I could get access to server which would really be helpful while I am learning.

        To answer your question, Yes I am a member of the FM Community. I appreciate the link. I will head over there now to watch. Thanks for responding.

  2. Douglas I wonder if the video and slides are actually in the Dev Subscription area? I apparently don’t have the proper rights to access the page.

%d bloggers like this: