The Primary Key Field In FileMaker

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

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

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. A primary key field can be used in relationships of all types but primarily in one to many relationships. Even if you don’t think you will ever use a primary key field, I’d recommend creating one in every table you create.

There are some basic guidelines for Primary Key Fields. You can bend these rules from time to time but you shouldn’t make a habit of breaking them.

1) Primary Key Fields Should Never Be Empty And Set For Auto Enter On Record Creation
2) Primary Key Fields Should Have Unique, If Not Serialized Data In them
3) The Data Within A Primary Key Fields Should Never Change (be edited)
4) Validation Should Be Used To Make Sure The Data Is Not Empty And Unique

When importing data, you should always make sure your primary key fits the above criteria. If necessary, you may need to edit the primary key field on the found set of imported data to make sure it is unique.

As you might have guessed, fields are an important part of a FileMaker relationship. It is also important to setup the field options for key fields to ensure a smooth running relationship.

Auto Entry - Used to auto enter a pre-determined text string into a key field when new records are created. Let’s take a customer and an invoice relationship for example. You will likely link the two with a customer id field. So when you create a new customer record in the customer table, you will want a new and unique text string in the customer id field. This can be easily done with the auto entry in a field option. In the field options, you will see a checkbox option next to Serial Number. You will want to check that option. You don't have to enter in the next serial value or increment by fields, unless you want to. I tend to enter in a starting alpha character in my next value field that matches to the table. For example, for a clients table, I may start off with a value of C1001, an invoice table I1001 or an employee table with e1001.

Validation - One of the option screens available from the define fields tab. You can validate the information just entered into a field is of a particular type, not empty, unique, existing, member of a value list, in a particular range or equal to a particular calculation. This is normally done to insure data integrity. For a primary key field, would would normally check the Not Empty and Unique validation settings.

Storage Options - The area where you can manually set a field to have indexing on, off or only on when needed. It is available from the options dialog box in define fields. To index a field manually, open the define dialog box, choose the field you want to index, click the options button and then click the Storage Options buttons to get to the final indexing dialog box. See indexing for more information. In FileMaker 7 and above, globals are no longer a field type like text, number or date. In the more modern versions of FileMaker, globals are a storage option. A global storage setting can make a field contain the same value for all records within that table.

Layout Field Behavior - You have a new layout field option called Field Behavior. This allows you to set a field to be entered into in browse mode or find mode. This appears as two different check box options for each. When checked, you can enter into that field in that mode. When it is not checked, you cannot enter into that field in that mode. Many times, you will want to have your key fields setup so they cannot be entered into in browse mode but can be entered into to perform searches.

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.