AnsweredAssumed Answered

Common Forum Relationship and Field Notations Explained

Question asked by philmodjunk on Aug 22, 2012
Latest reply on Apr 3, 2014 by GiovanniAndreani


Common Forum Relationship and Field Notations Explained


     This post is being made so I can refer others to it when they have trouble understanding commonly used text based notation to describe fields and relationships.

     Compare the following text to the uploaded image from Manage | Database | Relationships:


     Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
     Products::__pkProductID = LineItems::_fkProductID

     Note how ----< stands for a "one to many" relationship and note how it is represented by a similar line in Manage | Database.

     Note how the first line of text matches up with the boxes and lines in Manage | Database and how the following two lines document what fields are matched to which in these relationships.

     Additional notes:

     __pk is a commonly used naming convention to identify Primary Key fields. A primary key uniquely identifies each record in the table where it is defined. In FileMaker, it almost always is an auto-entered serial number field. I've used two underscore characters so that it sorts before all other field names in any alphabetized list of field names--often very useful when working with fields duing database design.

     _fk is a commonly used convention for Foreign Key fields. Values in foreign key fields are not unique. They match to a value from another table--often the other table's Primary Key. Note how the rest of the field's name identifies the table and primary key field to which it matches. A single underscore ensures that the field will sort before all fields except the primary key field. Foreign key fields are not set up to auto-enter a serial number. If they have any auto-enter setting at all, it's a calculation designed to automatically match it to the primary key of the current record in another table.

     Invoices, LineItems and Products shown in this example aren't necessarily the names of actual tables in your file. They are called table occurrences. To learn more about table occurrences, see this thread: Tutorial: What are Table Occurrences?

     Though not shown in these examples, sometimes I need more than one Table Occurrence with the same data source table. In those cases, I use the name of the data source table first, then use | followed by additional text that identifies the key purpose of this additional table occurrence. Thus, if I added another Table Occurrence, Products|Taxable to the example given here, Products would be the name of its data source table and "Taxable" helps me to know that this relationship only matches to taxable items from the Products table.