Unique Auto-entered Primary Key IDs v2.1

[Kurt Knippel]

With the coming of Filemaker 7, I need to update this a little bit to account for how Filemaker stores dates and times.

I tweaked the calculation a bit once more. I have also found that making this into a custom function with Filemaker Developer v7 is an awesome way to implement this.

Since I am continually explaining this concept, I thought that I might post a FAQ on how to create a unique*, non-sequential**, non-random*** automatically entered record ID for you to use as the Primary key in your files for a relational database system.

I give all of my files a PrimaryID_key, which is an auto-entered calculation into a text**** field. The calculation is the concatenation of the Filemaker internal RecordID, plus the current Date and the current Time.

Right( "00000" & Get(RecordID); 6 ) & "-" & 

Right( "00000" &   Hour ( Get ( CurrentTime ) ) & Minute ( Get ( CurrentTime ) ) & Seconds ( Get ( CurrentTime ) ); 6 ) & "-" & 

Right( "00000" & Month ( Get ( CurrentDate ) ) & Day ( Get ( CurrentDate ) ) & Right ( Year ( Get ( CurrentDate ) ); 2 ); 6 )

This generates a value something like:

007865-040474-730452



DanielS suggested adding a couple of randomly generated characters into the key to give a little more safety when using cloned databases and records generated via loops.



I do like his approach of adding one or more random characters into the sequence. I would perhaps modify it as follows to make them break up the sequence a bit more.

Right( "000000" & Status(CurrentRecordID); 6) &

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ"; Round(Random*35;1); 1) &

Right( "00000" & Hour ( Get ( CurrentTime ) ) & Minute ( Get ( CurrentTime ) & Seconds ( Get ( CurrentTime ) ) ); 6 ) &

Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Round(Random*35;1); 1) &

Right( "00000" & Month ( Get ( CurrentDate ) ) & Day ( Get ( CurrentDate ) ) & Year ( Get ( CurrentDate ) ); 6 ) &

Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Round(Random*35;1); 1)

This generates a value something like:

007865X40474R730452F

This way you get the random characters mixed into the key and still stay within the bounds of 20 characters.

This is a 20 character ID which allows something like 100 trillion different unique values. You are unlikely to ever run out. But should you need more Filemaker will index lines up to 60 characters in length. So you simply need to add a space and then add more characters.

"Why 20 characters? Why seperated with dashes? Why is the space necessary in the above example?"

Good questions. Filemaker will index individual words up to 20 characters in length, so I simply choose that to make the ID a nice size. I used dashes to separate the components because I liked the look of it and it makes things easier if I want to compare IDs. The space is required because Filemaker will only index words 20 characters or less. Notice I did not say "up to 20 characters", Filemaker ignores words over 20 characters entirely and ignores lines over 60 characters.

A couple of the great things about this method: it is not any more difficult to implement than a simple sequential serial number, but has none of the limitations or flaws; it can be used in as many files as you would like and all will get unique IDs; it can be used in synchronized solutions and all records will get a unique ID; the IDs are not accidentally replicatable.

The only real flaw in this system (and in ALL systems that use IDs) is Filemaker's Duplicate function. Since this creates an EXACT duplicate of the entire record, including all fields, you will end up with duplicate IDs. This is easy to deal with as you should simply not allow users to use the Records menu and give them buttons and scripts to control the duplication of records. (I have actually never had this problem occur with users, only when developing with a team and having other developers use the Duplicate step without updating the ID.)

Hopefully this has enlightened you and will give you lots of new ideas for your own development. As always I welcome and comments or questions just contact me privately.

Kurt

*the ID created is truly unique within a given file, and is virtually unreplicatable, except through deliberate means (i.e. copy & paste).

**the ID created is not really sequential in the traditional sense, but should you be curious enough you will see that IDs do seem to increase as records get added.

***these IDs are NOT random in nature and do follow a pattern, although the pattern itself is fairly random in nature. The benefit of non-randomness is that there is no chance of replication of ID, as there is in a random system. Also ALL random keys are based on a seed value, knowing this seed and setting it will allow you to create the same sequence of random numbers again and again. This will not happen with the system I use.

****I always use a TEXT field for my IDs, never a number field. There is nothing you cannot do with text. But there are tons of functions that will not work with numbers. There are no performance issues.

FileMaker Version: 7

Platform: Mac OS X Jaguar

[Ken Newell]

From the message. . . .

This generates a value something like:

007865X40474R730452F

This way you get the random characters mixed into the key and still stay within the bounds of 20 characters.

This is a 20 character ID which allows something like 100 trillion different unique values. You are unlikely to ever run out. But should you need more Filemaker will index lines up to 60 characters in length. So you simply need to add a space and then add more characters.

"Why 20 characters? Why seperated with dashes? Why is the space necessary in the above example?"

Good questions. Filemaker will index individual words up to 20 characters in length, so I simply choose that to make the ID a nice size. I used dashes to separate the components because I liked the look of it and it makes things easier if I want to compare IDs. The space is required because Filemaker will only index words 20 characters or less. Notice I did not say "up to 20 characters", Filemaker ignores words over 20 characters entirely and ignores lines over 60 characters.

My comments . . .

I think the 20 characters and a total of 60 rule is no longer applicable to FM7. I believe the new rule is 100 characters.

[Kurt Knippel]

You may be correct, but I have not yet been able to verify this.

[Ugo DI LUCA]

Kurt,

I always felt a bit idiot to ask, but as I can see this new version has that same stuff...why do you Omit the "W" in this calc ?

FileMaker Version: 6

Platform: Mac OS 9

[geod]

You are almost right Ken. According to my tests, using a field for a lookup list, FM7 will correctly index words up to 109 characters long. This is a big increase. I wonder if Kurt will change his method.

Graham

FileMaker Version: 7

Platform: Windows XP

[Ocean West]

Let ( [time = Evaluate(Right("00000"& Hour(Get(CurrentTime))&Minute(Get(CurrentTime))&Seconds(Get(CurrentTime));6));

date = Evaluate(Right( "00000" & Month ( Get ( CurrentDate ) ) & Day ( Get ( CurrentDate ) ) & Year ( Get ( CurrentDate ) ); 6 ));

string = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"]; 

Get ( RecordID ) & " " & Middle ( string ; Round ( Random*20 ; 0 ) ; 1 ) &" "& time & " " & Middle ( string ; Round ( Random*20 ; 0 ) ; 1 ) &" " &  date )

this works too.

[The Shadow]

Why not just use numbers in FM7? They index up to 10^400 now...

[Fridolin]

Hi!

Please call me stupid, but I don't get it. I usually let FM auto enter a serial number with a prefix of the table name and version number of the file. For a invoice it can look like "inv.1.387". I think it's great to have meaningful serial numbers. I can see in what version the record was created (great for checking what went wrong) and no possibilities to end up with duplicates (unless You make an manual error implementing the update). If I want to present the key to the user I extract the numbers with text functions.

Fridolin!

[H.P.]

I don't see the need for a 20 char. primary key. Serial # works fine and do the job well already.

[Kurt Knippel]

Kurt,

I always felt a bit idiot to ask, but as I can see this new version has that same stuff...why do you Omit the "W" in this calc ?

Typographical error. Thanks for point it out.

[Kurt Knippel]

Why not just use numbers in FM7? They index up to 10^400 now...

You can use numbers, just NOT a "number" field. For a couple of reasons: there is nothing that cannot be calculated from a text field, it stores the actual values rather than a representation of the actual values which is what a number stores. Number fields cannot have any alphabetic characters as part of thier index. Number fields will store the value as an actual number, so that 00000000012 is no different than 12 and this is a BIG deal when we are talking keys.

[Kurt Knippel]

Hi!

Please call me stupid, but I don't get it. I usually let FM auto enter a serial number with a prefix of the table name and version number of the file. For a invoice it can look like "inv.1.387". I think it's great to have meaningful serial numbers. I can see in what version the record was created (great for checking what went wrong) and no possibilities to end up with duplicates (unless You make an manual error implementing the update). If I want to present the key to the user I extract the numbers with text functions.

It is simple sequential serial numbers that are the biggest problem, but you have a slightly more complex version which is better, but still with a possible flaw. There are at least 2 ways (one in define fields, one in scripting) in which you can reset serial numbers, thereby completely hosing your keys. Plus from a DBA standpoint everytime you update your solution these serial numbers need to be reset, missing on will have very bad reprecussions on your keys.

In addition to the above my solution will work flawlessly in all remote or synchronized solutions, where more than 1 copy of the database is in use, such as with remote laptops. There is no way to keep serial number synchronized in this situation.

Only field level validation will prevent the duplication of a serial number, and with field level validation if there is a duplicate the user will get stuck with no way to fix the problem.

I agree with you to a certain extent in that I always include a simple sequential serial number in my databases, so that I can simply see the order of records, but I NEVER use this as a key. One of the basic rules of keys is that they should be meaningless.

To be perfectly honest, my system is more complex than is necessary in most cases, but requires almost nothing in terms of programming. It just requires an auto-entered calc and you are done. This system has virtually no possibility of duplication or compromise except through a deliberate effort an even that would be difficult at best, certainly more difficult than a simple sequential serial number.

I would also suggest that you look at the keys that are built in every other RDBMS, they are usually a random series of 20+ numbers. There is a reason that they do not simply use a simple sequential serial number.

[Michael Myett]

If you duplicate a record in FileMaker which uses an auto-entered serial number as a recordID, FileMaker duplicates the entire record except the serial number, and instead inserts the next available serial number.

Perhaps the best way to overcome the risk of duplication is to concatenate an auto-enter serial with your calculation, although it does involve one additional field.

[BruceJ]

DanielS suggested adding a couple of randomly generated characters into the key to give a little more safety when using cloned databases and records generated via loops.

I do like his approach of adding one or more random characters into the sequence. I would perhaps modify it as follows to make them break up the sequence a bit more.

For what its worth - I've found it helpful to leave out certain letters to avoid confusion. Such as O I S and B. I do this not only in these unique record ID, but anywhere where an arbitrary combo of numbers and letters exists.

[Kurt Knippel]

I don't see the need for a 20 char. primary key. Serial # works fine and do the job well already.

Unless someone resets the serial number, or you import records and forget to update the serial number, or you want to have a remote location (i.e. laptop) adding records to a local copy and then integrate them into your system later or someone does a replace on the serial number field...and so on.

Now, I ALWAYS include a simple sequential serial number field in all my tables, I just NEVER count on this as a PRIMARY_ID...I also never show this to the user or let the user manipulate this in any way. The primaryID is for use by the database and controlled by the developer in the solution.

[Russell Watson]

If the user never sees this 20-char primary key, how do you implement drop down lists for choosing a single record. You couldn't use a value list with, say PrimaryKeyId and Name.

[Kurt Knippel]

For those kinds of things, I would use the normal method of having the user pick either soley by name or by name and CustomerNumber, ClientID or whatever they call thier identifier. This would then cause a lookup or auto-entered PrimaryID to change. So the user NEVER sees or interracts with the actual PrimaryIDs in any of the files.

[mediarare]

Are there any adverse affects of transitioning from using serial ids, to using this method? this would of course leave the primary keey set with a mix of the 20 digit numbers generated, and the old serial numbers.

I can't see that there are would be any issues, just want a second opinion.

[Kurt Knippel]

Outside of the problems already discussed about using simple sequential serial numbers, there should be any problems with transition. Filemaker does not care what it matches on...so a 1 character key and a 101 character key can happily live togther.

[SurferNate]

Thanks Kurt for the tutorial. From a comment you made at a FMPug meeting I figured out the general idea for myself (and put it into use in my project) and this discussion has helped to refine that process. The learning curve has been really fast for me and I find that every day I learn something new and very useful.

[Zadkin]

Looking for a compact but unique key with near sequential aspects, I used a very long expression in FM6. Translated to FM7 with custom functions it becomes something like this:

  

Let(

val = Get ( CurrentHostTimeStamp ) - Timestamp ( Date ( 1;1;2001);0 ); 

Mod36(val;5) & Mod36(val;4) & Mod36(val;3) & 

Mod36(val;2) & Mod36(val;1) & Mod36(val;0) &

Mod36(Get(RecordID );1) &

Mod36(Random*36;0)

Mod36(Get(RecordID );0) &

)



With Mod36( number; depth) = 

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Mod(number/(36 ^ depth);36)+1; 1)

The timestamp part creates an unique Id for each second.

The random part is to avoid to run out of luck when the database is distributed with two record creations the same second.

The RecordId part allows to create 1000 records in 1 second without doubles.

The Mod(number/(36^depth);36)+1 part is tricky.

- Middle starts from position 1, so 1 must be added to the modula result that can be 0.

- When depth = 0 we have 36^0 = 1

[Zadkin]

Looking for a compact but unique key with near sequential aspects, I used a very long expression in FM6. Translated to FM7 with custom functions it becomes something like this:

  

Let(

val = Get ( CurrentHostTimeStamp ) - Timestamp ( Date ( 1;1;2001);0 ); 

Mod36(val;5) & Mod36(val;4) & Mod36(val;3) & 

Mod36(val;2) & Mod36(val;1) & Mod36(val;0) &

Mod36(Get(RecordID );1) &

Mod36(Random*36;0)

Mod36(Get(RecordID );0) &

)



With Mod36( number; depth) = 

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Mod(number/(36 ^ depth);36)+1; 1)

The timestamp part creates an unique Id for each second.

The random part is to avoid to run out of luck when the database is distributed with two record creations the same second.

The RecordId part allows to create 1000 records in 1 second without doubles.

The Mod(number/(36^depth);36)+1 part is tricky.

- Middle starts from position 1, so 1 must be added to the modula result that can be 0.

- When depth = 0 we have 36^0 = 1

[Zadkin]

Looking for a compact but unique key with near sequential aspects, I used a very long expression in FM6. Translated to FM7 with custom functions it becomes something like this:

  

Let(

val = Get ( CurrentHostTimeStamp ) - Timestamp ( Date ( 1;1;2001);0 ); 

Mod36(val;5) & Mod36(val;4) & Mod36(val;3) & 

Mod36(val;2) & Mod36(val;1) & Mod36(val;0) &

Mod36(Get(RecordID );1) &

Mod36(Random*36;0)

Mod36(Get(RecordID );0) &

)



With Mod36( number; depth) = 

Middle("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"; Mod(number/(36 ^ depth);36)+1; 1)

The timestamp part creates an unique Id for each second.

The random part is to avoid to run out of luck when the database is distributed with two record creations the same second.

The RecordId part allows to create 1000 records in 1 second without doubles.

The Mod(number/(36^depth);36)+1 part is tricky.

- Middle starts from position 1, so 1 must be added to the modula result that can be 0.

- When depth = 0 we have 36^0 = 1

[T-Square]

Kurt--

I happily implemented this Primary key feature in FM6, and have it set up as a custom function in the FM7 port I have sent out to clients (why Filemaker doesn't have a true Unique Primary Key is still beyond me, but ...).

The routines work perfectly, but I am encountering file bloat that concerns me. With 20-character keys, a file that tracks simple but numerous entries, such as a transactions table, will grow at a surprising rate. I have just such a file that contains its own primary key plus three foreign keys, a couple of numbers and a date. My tests show that this file, when populated with 70000 records, results in a 40MB table. I'm not sure why that would be, but it is.

Given that circumstance, I am now looking at ways to trim the size of the key field. It would seem to me that providing trillions of unique 20-character keys is overkill. A couple of questions:

1) Am I overreacting to the issue of file size? I admit I'm old school, and remember my first (external) 10 Mb hard drive way back when...

2) If I do trim this, I would want to use some numeric figure like the Unix "number of seconds since xx-xx-xxxx" as the second (and only) element in the calculation. Is there a simple way to derive a number like this?

Thanks,

David

[Howard LeCanard]

Here's something that's always bugged me about these conversations. If we need to use these complex keys because we can't trust the auto-enter serial to create unique values, how can we trust the auto-enter serial to produce values that have to be incremental. Invoice numbers for example.

People get very testy if their invoices skip a number, or have duplicate numbers.

[Ugo DI LUCA]

Hi Kurt and all,

I'm facing a bug I thought I should report on one application where this ID structure is implemented. This bug appears for FileMaker 8 and FileMaker 8.5 and is due to the scientific notations evolvement.

Generating this kind of random Ids may lead to some random construction where finally only one letter is pulled into the string.

c

Problem arise then when this unique letter in the ID is a "E", as FileMaker engine interprets this text ID as a not valid number if the potion behind the E is superior to 799.

The consequence will be that the ID will not be counted in any of your Count ( MyRelation:MyID ) or any Summary field as Total Of.

Any aggregate calculation and Summary field actually are concerned.

So for example

0149448000226E2920076

or

017988E00025622920076

would not be counted

I would therefore recommand that at least 2 letters be introduced into the key.

Ciao

HTH