4/10/2014

Understanding and creating many-to-many relationships in FileMaker Pro | FileMaker

Worldwide Sites

Home

Products

Support

Solutions

Support

Knowledge Base

Downloads

Community

Forums

Company

FileMaker Technologies

Purchase

Training

Contact Support

Log In | Sign Up

Understanding and creating many-to-many relationships in FileMaker Pro
What do I need to know to work with many-to-many relationships in FileMaker Pro?
Answer ID: 9922
Last Updated: Feb 11, 2014 08:54 AM PST
Products
FileMaker Pro
13.x
12.x
11.x
10.x
9.x
8.x
7.x
FileMaker Pro
Advanced

A relational database consists of data and information organized into different tables. A table is used to define a specific, single group of information, like a table of customers for a business or a table of products that the business sells. Each record in that table should include a “key field.” A key field is a field or combination of fields that uniquely identify a record (row) in a table. A key field can be referred to as a "primary key" and a "foreign key." A "primary key" is a key field that is in the same table as the record it identifies. A "foreign key" is a key field that identifies a record in another table.
Understanding the difference between a primary key and a foreign key will be useful when dealing with many-to-many relationships in FileMaker Pro.
Relational database design supports many different types of relationships between tables. These relationships are in place to prevent the entry of inconsistent data and enforce referential integrity. FileMaker Pro supports the following types of relationships:

11.x

The most common relationship used when creating relational databases. A row in a table in a database can be associated with one or (likely) more rows in another table. An example of a one-to-many relationship is a single order has many items on that order. And since relationships work both ways it is not uncommon to hear reference to many-to-one-relationships as well.

One-to-one relationship A row in a table is associated to one and only one row in another table. An example of a one-to-one relationship is a person can have one social security number and a social security number can only be assigned to one person.
In most cases there is no need for a one-to-one relationship as the contents of the two tables can be combined into one table.
When one or more rows in a table are associated with one or more rows in another table. An example of a many-to-many relationship is a table of customers who can purchase many different products and a table of products that can be purchased by

relationships

12.x

One-tomany relationships Many-tomany

13.x

many different customers.

10.x
9.x
8.x
7.x

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 manyto-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.
How to setup a join table in FileMaker Pro:
For our example we will discuss a typical many-to many-scenario: Students and Classes. We will have a Student table that contains a record for each student and a Classes table that contains a record for each available class. The primary key field StudentID uniquely identifies a student in the student table. Likewise, the primary key field ClassID uniquely identifies a class in the class table.
A student can take many classes and a class can have many students. Relating these two tables http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro 1/2

4/10/2014

Understanding and creating many-to-many relationships in FileMaker Pro | FileMaker

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.”
1. Create a new table in FileMaker Pro and call it "Signups"
2. In the “SignUps” table, create a primary key field called “SignupsID.” This step is not necessary for the join table to work properly but it is good relational database practice for every table to have a primary key.
3. 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.
4. 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.
5. 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:
1. On a layout based on the Students table, create a portal. The portal should be configured to show related records from "Classes"
2. 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.
For more information about related records and creating portals, see FileMaker Help.

Visit the FileMaker or Bento Forums

Copyright © 1994-2014, FileMaker, Inc.

Legal Privacy Policy Piracy Site map Contact us Knowledge Base hosted by Oracle RightNow

Use of this website signifies your agreement to the Terms of Use. Prices are listed in U.S. dollars and subject to change without notice.

http://help.filemaker.com/app/answers/detail/a_id/9922/~/understanding-and-creating-many-to-many-relationships-in-filemaker-pro

2/2