Tables, Table Occurrences, and Relationships

Understanding the difference between a table and a table occurrence is vital when developing a relationship in a FileMaker solution. If the Relationships Graph for your FileMaker solution never has more than one table occurrence per table, then it may not be important to understand the differences. However, once you do understand the difference it may help you to utilize relationships in FileMaker in ways you did not realize were possible. To understand how tables, table occurrences, and relationships work together we must understand each one.

The best place to start is to first understand a table. A table stores information about one specific kind of thing: for example, a given table may store information about vehicles, or swimmers, or scholarships. A table stores its data in a row-and-column format. Each row represents one instance of a thing: one vehicle, or one car, or one scholarship. These rows are often known as “records” in database terminology. The columns define the common data elements stored for each instance of a thing: a car’s weight and model year, or a scholarship’s amount. These columns are often called “fields” in database parlance. A table is thus the collection of these records and their field data. In FileMaker, tables are created in the Manage Database Dialog under the Tables tab. All database data in FileMaker is stored in tables.

A table occurrence is a visual representation of a given table in the Relationships Graph. The best way to think of a table occurrence is as a window or point of view into the actual table. This window or viewpoint can be used to see and/or manipulate the data that resides in the table. Layouts, for example, are based on a specific table occurrence and can access the data in the specific table occurrence or any data related to that table occurrence. The use of these table occurrences will be easier to understand once w look at relationships. Finally, what is a relationship? A relationship is way of reaching data in one table based on the information that resides in the records of another table.  This is done by linking one or more fields in one table occurrence to one or more fields in another table. For example, you might link a customer ID on an Order record with a customer ID on a Customer record. That’s a way of saying “this order belongs to that customer.” Another way to think about this is that relationships are a pre-specified query from one table occurrence to the next.  The relationship we just described, for example, would also allow you to find all orders for a given customer. By using these relationships we can report on data in other tables and also display their information through portals from the standpoint of the current table occurrence. Now that the definitions are out of the way, why are these tools useful? &And why does FileMaker allow more than one table occurrence per table in the relationships graph? If a relationship is like a pre-specified query into another table, there are times when we need to search for data in one table in multiple ways.  That is why we need table occurrences. Imagine a straightforward Company-Contact Management System.  We may have the following tables: Company, Contact, Address.  The way these tables are related is as follows: – one Company can have multiple Contacts – one Company can have multiple Addresses – one Contact can belong to only one Company – one Contact can have multiple Addresses, and these addresses may not need to directly relate to that contact’s company

We don’t need to create a table specifically for Contact Addresses and another for Company Address to implement this solution.  We can simply create one Address table and have it appear as two table occurrences: – Contact Address will be a table occurrence that is based on the Address table and directly relates to the Contact table occurrence – Company Address will be a table occurrence that is based on the Address table and directly relates to the Company table occurrence.

Here’s how the Relationships Graph might look. Table occurrence with the same color are based on the same underlying table.

Why not just relate both Company and Contact to the same Address table occurrence?   FileMaker doesn’t allow for loops in the Relationships Graph. A loop would exist if there were ever more than one pathway between any two table occurrences. Since Company and Contact will need to be related to each other, by relating them to Address we would form a loop.  Now, why are loops forbidden?  Well, computers aren’t as smart as we are. A computer can only follow specific instructions.  Imagine that your computer is “standing on” the Company table occurrence and you tell it that you want to see the Address that belongs to a specific Contact.  Well, the computer sees that it is ”standing on” the Company table occurrence and that there are two ways to get from there to Address. One is by going directly to Address and the other is by going to Contact and then on to the Address.  Since both ways are valid the computer cannot decide which way to take on its own.  This is why we need table occurrences. By having multiple table occurrences that point to the same table, all we need to do is to tell the computer the specific table occurrence which we want to grab the data through (in the above Graph it would be Address1).  Then the computer can go to that specific table without any confusion. Between any two table occurrences in the Relationships Graph, there will only ever be one unique path.

Through this example we can see how table occurrences are just a window or way to look into the table to see or manipulate data. We can also see that relationships are just a way in which we direct FileMaker from a perspective on one table to a perspective on another table (a table occurrence can also be thought as a “perspective on a table”). I hope this will help people understand the difference between tables, table occurrences, and relationships. or a more in-depth understanding of these concepts, data modeling in general, and many more aspects of FileMaker I highly recommend taking the FileMaker Training Series.