myIdea Logo

My Filemaker Pro Programming Standards: Keys and Relations

This page is part of the internal FileMaker Pro programming standards of Lutz Pietschker. No claims of any sort can be derived from the description of these standards. In particular, no claim can be made that these standards are complete and without errors, and that any of my software projects follow to these standards in part or completely.
The page content was last revised on (ca. 2008)

Go to start page

Document Content

This document describes the creation of indexes and keys and the rules for creating relations.


Keys are used as unique identifiers of each record in each table; each table must have such a key field (exception: Tables used for value list creation may have more than one such field, see value lists section). Any key field value must be unique within the table. Key fields can not be changed by database users, and they retain their original values indefinetely.

Unique keys may be built in one of two ways:

Serial Number keys may only be used in tables with reference content, i.e. tables whose content is more or less static. This does not exclude that fields in the record change their value, but new records may be created only by well-defined processes that make sure that existing keys are not changed and that new keys are added in a way that the keys are still unique identifiers.

Calculated text keys are built by a custom function called lp_CreateKey_t that resides in the $app file. They are built after this schema:

<user> <datecode> <timecode> <randomnumber>

,where the four value parts, separated by blanks, are


Indexes for the text keys described above are defined to use Unicode indexing.

To prevent indexing where it is not needed during normal database usage, indexing shall be turned off for fields that do not need it. The necessity for indexing shall be checked prior to solution delivery.


A case in point of the last rule are relations that check for uniqueness during record creation. It is far easier to do so using a self-join relation than to do so from outside, and thus it is allowed.

This page is copyrighted by the author according to the copyright note.
All rights reserved. Lutz Pietschker, Berlin/Germany, 2011 ff.

, last change 2011-03-12