FileMaker Relationship Key Fields (part 2)

From Dwayne Wright PMP
Certified FileMaker Developer

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

FYI ... resist the temptation to add intelligence to a key field. I've been in some databases where the key field was calculated by some data string in the record. The idea is that the coded data entry sequence can identify the type of record based upon strings within the data. This is not a good idea because what if the record changes and the code is no longer correct. You then have the choice of making two bad decisions. One is to change the data in your primary key field ( which can break it’s relationship to other tables). The other is to leave the data string but anyone looking at the code may make a decision on inaccurate data. You may create a code field such as this and use it within your business. This is fine but I'd recommend against using a coded data type of field as your primary key field.


The foreign key field is a type of child key field. In my mind, the foreign key field always has a primary key field as the parent. This means the foreign key field will only match to the one unique record in the parent table. I didn’t used to think this way but reaches this conclusion as I started writing about FileMaker relationships and began to think how to describe them. So we both have learned something here, eh?

A calculated key field is when you add one or more fields together to make a string of text. You might even add some literal text or a calculated function result to your calculated key field. Normally, you would keep all your key fields down to a minimum size but I've seen some calculated key fields get very large. You do need to know that FileMaker indexing only recognizes the first 100 characters in a string of text. So make sure that each string of text in your calculated keys is under 100 characters ( which should be easy to do ). You can have multiple strings of texts ( words if you will ) in a calculated key field and each has their own 100 character limit. So a calculated key field can have over 100 recognized characters as long as each string is less than 100 characters each.

A multiple line or compound key field is an odd little thing in FileMaker but can be very useful in the design of many to many relationships. If a value that is separated by a carriage return in one key field matches a value in the other key field, the relationship is valid and referred to as a multiple line field. That is to say if the Parent key field contains

Red
Yellow

it will have a valid relationship to the Child Key field containing the value of Yellow.

FYI ... Some developers like to add a code to a fields name to indicate if it is used as a key field in a relationship. Primary or parent key fields may start or end with pk ( pk_invoiceid or invoice_pk ). Child or foreign key fields may start or end with fk or ck ( fk_invoiceid, ck_invoiceid, invoice_fk or invoice_ck ).

FYI ... When you are in layout mode, you can tell a regular field from a related field. The related field will have two colons in front of it ( ::homephone ). Double clicking this field will show you the table occurrence it is using. This would be the child file or child table occurrence. If you look at the relationship between the table occurrence you are using for the current layout and the table occurrence for the related field, you will be able to see the parent and child key fields in use.

=
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.