FileMaker Databases, Tables, Table Occurrences, Layouts and Relationships

Modified on March 26, 2020.

A database server is like an electronic filing cabinet

A filing cabinet can have many drawers, like many databases on your database server.

Each drawer is like an individual database.

Each drawer can have many folders, aka a set of related tables in a database.

Each folder is like a table, aka a set of related records.

Each folder can have many pieces of paper, like individual records in a table, aka rows.

Each piece of paper can have many bits of information, like fields in a record, aka columns (like First Name, Last Name, etc).

    For example:

  • Filing cabinet drawer called COMPANY = database called COMPANY
  • Folders called Contacts, Projects, and Invoices = tables called CONTACT, PROJECT, and INVOICE
  • Paper forms in the Contacts folder / Projects folder / Invoices folder = records in those tables

What is a database?

A database is a set of related tables.
COMPANY is our database .

What is a table?

A table is a set of related records.
CONTACT, PROJECT, and INVOICE are the tables in our COMPANY database.

What is a table occurence (TO)?

Table occurrence is the term for each “box” you see in FileMaker’s relationship graph.

A table occurrence:
– is a visual representation of a data source table in the Relationships Graph
– refers to a specific table and is used to help identify a relationship linking two or more tables in your database

For any given table, you can create as many table occurrences for it as you need to design different relationships linking your tables.

Layouts 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 we look at relationships.

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 occurrence. For example, you might link a Contact ID in an INVOICE record with a Contact ID in a CONTACT record. That’s a way of saying “this invoice belongs to that contact.”

Another way to think about this is that each relationship is 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 invoices for a given contact. By using relationships we can report on data in other tables and also display their information through portals related to the current table occurrence.

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 may need more than one table occurrence per table.

We don’t need to create a table specifically for contact_INVOICES and another for project_INVOICES to implement a solution. We can simply have one INVOICE table and have it appear as two table occurrences: contact_INVOICES will be a table occurrence that is based on the INVOICE table that directly relates to the CONTACT table occurrence, and project_INVOICES will be a table occurrence that is based on the INVOICE table that directly relates to the PROJECT table occurrence.

Why not just relate both CONTACT and PROJECT to the same INVOICE table occurrence?

That would be a loop/circular path. FileMaker doesn’t allow for loops/circular paths in the Relationships Graph. There can only ever be one unique path between any two table occurrences.

How to decide which table occurence (TO) to use for a layout?

Selecting a TO tells FileMaker what table to go to for the data and what relationships to use when linking to other tables. So, you can get very different results for a layout, value list, calculation or portal just by selecting different TOs even when they refer to the same data source table.

You always want to use the parent data source table as your layout’s table occurence.

For example:
If you want to see the Contact’s Invoices in a portal on your layout, your layout’s parent data source table = CONTACT = the CONTACT TO. The poral = the contact_INVOICES TO.

In a calculation field, you use a “Evaluate this calculation from the context of” drop down to identify the parent table’s TO.

For example:
If you want a calculated field in CONTACT that counts the number of related INVOICE records that each CONTACT record has, “Evaluate this calculation from the context of” = CONTACT = CONTACT TO. Then the field for the calculation comes from the contact_INVOICES TO.

If you use a relationship to filter the values in a value list, there’s a “include only related values starting from” drop down in the bottom of the dialog to specify the parent TO.

In a script, you have to use Go To Layout to specify a layout (and thus its specified TO) before you can successfully use a script step that refers to fields in a related table – because you must go to a table/be on layout of the parent TO to refer to one of its related table’s fields.

In each case, FileMaker uses the TO name to determine what table will function as the parent TO. Once you’ve specified the related TO by selecting its name from the drop down in the specify field, specify calculation, specify portal or other such dialog, FileMaker can both locate the table that will serve as the related child table and also know which key fields you selected in the Relationship graph to link the two TOs.

Most places in FileMaker where you see “Table” it’s really a reference to “Table Occurrence”.

What is a layout?

A layout:
– is the view for the records in your table
– is like the piece of paper in a file folder in a filing cabinet
– can be a data entry form, list of records, report, envelope, or group of labels

How to manage your FileMaker relationships and layouts?

You need a plan on how to manage your FileMaker relationships and layouts. Both Anchor-Buoy and Selector-Connector are great relationship graph techniques. Pick one.

Both recommend that layouts be based only on your parent TOs (aka anchors). And, that you do not base your layouts on your child TOs (aka buoys).

In our example, this would be one TO and one layout for each table: CONTACT, PROJECT, and INVOICE.

Anchor-Buoy

Wrangling Relationships

FileMaker Anchor-Buoy Tutorial

Lessons 10 & 11: Anchor Buoy

Selector-Connector

FileMaker Tutorial – Selector Connector Demystified

FileMaker Data Modeling with Selector Connector

Both

A Little [FileMaker] Relationship Advice and Anchor Buoy