FileMaker Match Fields

From Dwayne Wright PMP
Certified FileMaker Developer

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

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

For a few years now, I have be concentrating on using the term match field instead of key field for the majority of FileMaker relationships. In fact, it has gotten to the point where I cringe when I hear a developer refer to a global key field. One developer in particular drives me crazy, a pre 2008 Dwayne Wright.

A discussion of proper relationship terms can be a passionate topic between FileMaker developers. Almost none of us "completely" agree but that isn't as bad as it sounds. I was introduced to this concept in a 2007 FTS Train The Trainer session led by Bob Bowers of Soliant Consulting. This isn't exactly how he explained the differences between key and match fields, but more of my 2011 take on the topic.

In a FileMaker relationship, you have at least one field from two table occurences linked together. The linking between the tables is accomplished because the data within them match in a defined way. Usually that way is that the data within them match exactly such as client ID A1234 in a clients module matches A1234 in a corresponding invoice module record.

Here you can see a classic example of a match but I don't call these linking fields match fields, they are indeed key fields.

FILEMAKER KEY FIELDS EXPLORED
If one of those relationship fields contains data unique to each record within its source table AND the layout / calculation uses it as the source of that context, it is commonly referred to as a primary key. That last little bit about context matters because FileMaker relationships can be bidirectional. A primary key fields is NOT a primary key field if it is on the child side of the relationship. In a case such as this, this field is only a match field that has record ID properties, in my opinion.

FileMaker primay key fields are defined with an auto enter data option. That is to say that when a new record is created its data is entered sequentially. They are not always sequential because some developers employ their own implementations of a UUID (universally unique ID) but that is a topic for another post. Primary key field data can be saved in text or number format but I prefer text format myself. The primary key field data needs to be unique and should never be edited. Editing primary key field data can break its relations to any existing child records. I do recommend that every table have a primary key field within it, even if you never plan on using it in a relationship.

The foreign key field is a term given to the field in the child table occurrence of a relationship that is always related to the primary key field in the parent table. Once again context matters in regards to the current layout or how the context is determined within a calculaton. Once again, a foreign key field is used in a relationship to the primary key field.

ABOUT MATCH FIELDS
Any relationship that doesn't use key fields as explained above are match fields. Yes, some relationships in FileMaker do not employ a primary or foreign key field condition. An example would be a filtered relationship in which the parent match field is usually a global field(s).


Here is a classic example of match fields in action.The setup above allows you to be on a client record and enter in dates to view timecards that might be associated to that client within those dates. The global fields on the client side and the date fields on the timecard side are match fields. The client id fields on both sides are key fields.


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.