FMDiff™   

This site is W3C compliant:
Valid XHTML - Valid CSS
Last modified February 24 2016, 21:23:06 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 modifiable
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 GetAsNumber()? The function Get ( CurrentTimeStamp ) 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.

Why did I change my recommendation from numerical to text result? 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. But there is always stored a value in text format even for numbers, probably for fast display on layouts. So the shorter numeric key is outweighed by the additional storage as text, hence the use of text keys which omits the numeric part.

A Hint to Save some Space and get faster imports

Set the Storage for the primary and secondary keys to Minimal after defining the relationships. This keeps the field index smaller by avoiding the extra word index. You should not search in a key field anyway. Warning: if you make this setting after the word index has been built (p. e. by "Show Index") your relations may not work.

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