FMDiff™

This site is W3C compliant:
Valid XHTML - Valid CSS
Last modified November 22 2011, 03:49:51 CET.

Generating Reliable Keys for Relationships

What are keys good for?

Generally speaking, a key (field) is the link between somehow related tables. The characteristics of a key could be described as follows:

1. must not be visible to the user and hence not user modifyable
2. must be unique for the table they are used in (and only there)
3. must remain unique after cloning and re-import of data
4. must create a new unique value on record duplication
5. must survive export and import even from other sources

Most people use Auto-enter Serial Number in a key field

This seems straight forward and reasonable and surely serves its purpose in very simple stand-alone solutions. But sooner or later this function is on it's limits. Imagine you clone the file and import the old data. This resets the serial number and you start producing duplicate serial numbers with every new record you create. As soon as you start thinking about how to implement the script step Set Next Serial Number[] you should abandon the Auto-enter Serial Number for key fields completely.

What is the best solution for this task

I will not conceal that there are other solutions, some of them are really complicated. I think however that there is a simple solution that meets all the above requirements, using a combination of functions.

Field Name	Type	Options
serial		Number	Auto-enter Calculation replaces existing value

GetAsNumber (
  GetAsNumber ( Get ( CurrentTimeStamp ) ) & Get ( RecordID ) 
)

How does this work?

The core function is Get ( RecordID ) which if used alone would not meet the requirements as cloning resets the record ID. But in combination with Get ( CurrentTimeStamp ) it becomes unique since it is practically impossible to create the same record ID at the exact same time - even if these records were created on independent machines and the data is merged later.

So why two times GetAsNumber()? The function Get ( CurrentTimeStamp ) usually returns the date and time as text in the system's locale format unless applied to a timestamp field. But we need the numerical representation of the timestamp, hence GetAsNumber(). This numerical result is then concatenated with the record ID (a number) which yields a text result that is eventually taken care of by the surrounding GetAsNumber() function.

Why is it important to have a numerical result at all? This has more economic reasons, since numbers require fewer bytes than text - roughly just one third. Since every record has a unique key, every key value contained in the index is increasing the file size. Shorter keys can be processed faster too.

Some programmers still prefix the key value with some letters to identify the purpose of the key. I think this is completely unnecessary. A key value should not be looked at. Its purpose must be obvious by the field name.

Suggestions, opinions, experience reports and other hints are welcome via our Contacts page.