FileMaker Relationship Key Fields (part 1)

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A key field is something that is used to validate a relationship between two (or more) table occurrences. When the data in the two fields match (or compare in a defined way), you have a valid relationship between the two table occurrences.

PLEASE NOTE: As I begin this introduction discussion about key fields, I will refer to some relational terms you may not be familiar with. Do not worry, I will cover these topics in detail in other discussions.

Let’s briefly cover the aspect about how a layout is tied to a table occurrence. This is done because depending on where you are (ie what layout), your relationship possiblities will change. First off, these setting will say this layout is linked to this table. In FileMaker 6, you didn’t have to worry about that because you couldn’t go beyond one table to one file. Second, the table occurrence you link a layout to defines what related table occurrences that layout can use. The common term for this is layout table context.

Now, let’s cover the parent key field and the child key field. The parent key field is the field defined for the table linked to the layout you are on. The child key field is the field defined to the table to which you are linking. The parent key field(s) and the child key field(s) may be called the match key fields, because a relationship is normally valid when they match. Now FileMaker does allow you to set up a valid relationship between the parent key field and the child key field when they do not match. This is because FileMaker 7 and higher now allows you to use different operators in the relationship. So perhaps a more valid term for the match key fields would be comparison key fields. Because the relationship is valid with the parent and key field compare in relation to the operator in use.

The child key field does have to be indexed for a relationship to be valid. Indexed fields are optimized for speed and provide superior performance on searches, calculations and sorting operations. If the field is not indexed when the relationship is executed, FileMaker will index it on the fly (if it can, some field types cannot be indexed). Depending how many records are in the child file, this may take some time. If possible, always index child key fields manually when you create them (manual indexing is one of the storage options available to a field).

Now, let us chat a bit about the primary key field, otherwise known as the record id field, is a field that uniquely identifies each record in a file. It is almost always used as a parent key field. It is defined as auto enter data option when a new record is created and should be sequential. It can be saved in text or number format but I prefer text format myself. The primary key field should never be edited because it can break any existing child records. I do recommend that every table have an auto enter, sequential, non editable primary key field within it.

I tend to label my primary key fields in such a way as to quickly find my primary key field when looking at a list of fields in a dialog box (like when you are adding a field to a layout). I also tend to uniquely identify my incremental serial numbers in the primary key field. For example, I may have a c in from of the numerical data in my clients file or a e in front of the numerical data in my events file (for example CLIENT ID C1005 or EVENT ID E2003).

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.