AnsweredAssumed Answered

Tutorial: What are Table Occurrences?

Question asked by philmodjunk on Aug 10, 2010
Latest reply on Dec 23, 2017 by philmodjunk


Tutorial: What are Table Occurrences?


     What are table occurrences and how can I use them?

     This post is a new attempt at helping people new to FileMaker understand table occurrences and how to use them to get more out of their FileMaker solutions. (A past attempt: Table vs. Table Occurrence (Tutorial) proved difficult for some to understand.)

     Let’s start with a simple database to illustrate the concept:

     Define a table, Contacts, with the following fields:

     ContactID (serial number)
     Name (text)
     cFax (Calculation, define it with just “Fax” in quotes as its calculation and set it to return “text” as its return type)

     Define a second table, Phone numbers:

     ContactID (number)
     PhoneType (text)
     PhoneNumber (text)

     At this point you have two “tables” listed on the Tables tab of Manage | Database and two “tables” shown in boxes on the relationships tab. While you’re on the relationships tab, drag from ContactID in contacts to ContactID in PhoneNumbers to link the two so that you have:

     Contacts::ContactID = PhoneNumbers::ContactID

     Double click the line you’ve just made and select “Allow creation of records via this relationship” for the PhoneNumbers table.

     Click OK to dismiss Manage | Database. Enter layout mode and add a portal to PhoneNumbers on your Contacts layout. Add both the PhoneType and PhoneNumber fields to this portal. You can now easily add several phone numbers for a single contact and you can use the PhoneType field to enter phone types such as “Home”, “Office”, “Cell” or “Fax”. Add a few such records and make sure to include one entry where the PhoneType is “Fax”.

     Now let’s say you want to put a contact’s Fax number on the contact layout without listing all the other phone numbers for that contact. To do this, we’ll create a relationship between Contacts and PhoneNumbers that matches to records with the same ContactID and which also has “Fax” in the PhoneType field.

     But now you have a problem. You have a field in contacts, cFax, that could be used in such a relationship, but if you drag from cFax to PhoneType to get this relationship, your existing portal will change to only show phone numbers of type “Fax”.

     Here’s how to get around this issue:  Create a second table occurrence of PhoneNumbers so that you can link it Contacts by both ContactID and cFax = PhoneType without changing the existing relationship required for the portal. I’ll walk you through the process first and explain the terms later.

     Go to Manage Database | Relationships and click PhoneNumbers to select that “table” in the relationship graph. In the bottom left corner of this window, there’s a button with two green plus signs. Click this button and a new “table” labeled PhoneNumbers 2 appears. Drag from Contacts::ContactID to PhoneNumbers 2::ContactID. Then drag again from cFax to PhoneNumbers 2::PhoneType to define a relationship that matches records by contact number and when the value “Fax” is in the PhoneType field. Go back to your Contacts layout and use the field tool to add a field to the layout. Select the PhoneNumber field from PhoneNumbers 2 as the field to be added. Return to browse mode and note that the phone number entered in the portal and labeled as “Fax” now also appears in this new field.

     Now to explain the terms: Each “table” in the relationship graph is actually a “table occurrence” and each time you find a post in this forum that talks about table occurrences (sometimes abbreviated as TO), that’s what the writer should be referring to. Each “table” on the tables tab is a “data source table”. Every time you define a new data source table on the tables tab, FileMaker automatically creates a table occurrence box on the relationships tab with the same name. 

     Table Occurrences are simply convenient labels you can use to link your data-source tables in relationships. You can create as many as you need to create different relationships between the same data source tables. You can see this link between a table occurrence and its data source table by hovering the mouse over the arrow in the upper left corner of the table occurrence box. Hovering over this arrow on both PhoneNumbers and PhoneNumbers 2 reveals that they have the same data source table, PhoneNumbers. Conversely, if you click on the Tables tab, you see that the PhoneNumbers data source table lists two “Occurrences in Graph”:  PhoneNumbers and PhoneNumbers 2.

     In our example, the two data source tables are linked in two different ways so that we can see either all the contact’s phone numbers or just the fax number. The additional table occurrence, PhoneNumbers 2, makes this possible.

Note to Readers: This is a thread that I point a lot of people to as a way to explain what I mean by the term "table occurrence". So many people get here from another thread. If you have a comment or question about this information, please click the back button and post it in the thread that directed you here. Due to an unfortunate bug in the forum software, threads like this that are more than 3 months old will not pop back up into Recent Items when you post a new comment to it and thus I am very unlikely to see your new comment if you post it here.