11 Replies Latest reply on Aug 17, 2016 11:43 AM by eric

    Table vs. Table Occurrence (Tutorial)

    philmodjunk

      Title

      Table vs. Table Occurrence (Tutorial)

      Post

      In this forum and throughout Filemaker’s help system, you’ll encounter the terms “Table” and “Table Occurrence”. In many places, the terms are used interchangeably even though they refer to distinctly different but related concepts. This has generated some confusion among new Filemaker users.

      Table:

      A table represents the blue print for storing a group of data items into a single object known as a record. The individual data objects that make up a record are called fields. You can represent this structure with a grid of rows and columns where each row represents a record and each column of data is a specific field, in other words, a table.

      Every Filemaker Pro database stores its data in one or more tables. These tables can be stored all in one file or different tables and/or groups of tables can reside in separate files. One of the goals of good relational database design is to structure the tables in an efficient logical manner that avoids storing duplicates of the same information in multiple records.

      Table Occurrence:

      A table occurrence is the term for each “box” you see in Filemaker’s relationship graph. Each one 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.

      This is where the confusion starts for many new users:  When you first define a new table in Filemaker, the system automatically creates a matching Table Occurrence with the same exact name places it in the Relationship Graph. Though it has the same name as your table, it is not the same object. In fact, you can delete the table occurrence from the graph and your table will remain part of your database. Likewise, if you click on the tables tab and delete the table, the (now orphaned) table occurrence with the same name will remain in your relationship graph.

      To see what table serves as the data source for a given Table Occurrence (TO), point your mouse at the arrow in the upper left corner of the TO box and the name of the TO’s data source table will popup. You can also double-click it to see the TO’s data source table and you can use this dialog to change the TO name or point the TO at a different source table.

      There are buttons at the bottom of your graph for creating new TO’s or duplicating an existing one. An especially helpful button is found just to the left of the magnification percentage. Click on a TO and use this button to “Select tables with same source table” (it should really read "Select Table Occurrences with same source table".)  Now use the color palette to assign these TO’s a unique color and you can identify all these TO’s that have the same source table at a glance.

      Why would I need more than one Table Occurrence for the same table?

      Frequently, you need to define more than one relationship between a pair of tables. In order to define a 2nd (or 3rd, or 4th…) relationship, you need a new “box” on your relationship graph or there would be no way to keep straight which fields you want to use as keys in each relationship. You’ll also find that you can’t connect TO’s in a “circle”. If you try to relate TO A to TO B and TO B to TO C and TO C back to TO A, you’ll find the Filemaker forces you to create a new TO (confusingly called an “instance” in the dialog that appears) for TO A to prevent such a “circle” of related TO’s. Since Filemaker allows you to reference records in related tables 2 or more TO’s away from your current TO, preventing such “cycles” makes this work with more consistent results and avoids circular definitions.

      How Filemaker uses a TO to manage relationships:

      Selecting a TO tells Filemaker what table to go to for the data, it also tells Filemaker what relationships to use when linking to other tables. Thus, you can get very different results for a layout, value list, calculation or portal just by selecting different TO’s even when they refer to the same data source table.

      Filemaker really needs three things in order to determine what related records to reference in a given situation: The parent table, the related child table and the list of key fields and operators that will be used to match the records between the two tables.

      If you look at any situation where you select a TO, you’ll find that there’s a way to identify the TO of the parent table.

      1. If you are selecting a TO so you can place a field or portal on a layout, the source table of the layout’s specified TO is the “Parent Table”.
      2. In a calculation field, you use a “from the context of” drop down to identify the Parent Table’s TO.
      3. If you use a relationship to filter the values in a value list, there’s a “starting from” drop down in the bottom of the layout to specify the parent.
      4. In a script, you frequently 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.


      In each case, Filemaker uses the TO name to determine what Table will function as the parent table. Once you’ve specified the Child 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 determine what key fields you selected in the Relationship graph in order to link the two TO’s.

      Some places in Filemaker where you see “Table” and it’s really a “Table Occurrence”:

      1. “Table:” shown in tool bar while in layout mode and used to identify the selected TO for the current layout..
      2. “Current Table” and “Related Tables” in the Specify Field, Field / Control | Setup… , Part Definition (“sorted by” field), dialogs.
      3. “From table:” in Go To Related Record script step (as seen in the script editor).
      4. Numerous locations within Filemaker Pro help also refer to “Table” where it should really refer to a table occurrence.