Auto-entered Unique PrimaryIDs

[Kurt Knippel]

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( "000000" & Status(CurrentRecordID), 6 ) & "-" &

Right( "000000" & Status(CurrentTime), 6 ) & "-" &

Right( "000000" & Status(CurrentDate), 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" & Status(CurrentTime); 5) &

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

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

Middle( "0123456789ABCDEFGHIJKLMNOPQRSTUVXYZ"; 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.

[LaRetta]

You've made my day! I've been working on setting up a 'truly unique' key field and was using date/time/user etc., but this is fabulous! Thanks for giving it to us!

LaRetta

[Ocean West]

Kurt this works great for managing a "Log" file.

Set the key into a Global Field use that global field to relate to a field in the "log file".

When you need to capture a log or history, set the global key, and since there will never be any associated records to this key in the log file you can via the script set all the data fields in the log file. The nice thing with this is you never have to switch layouts or mess with a portal (goto last, etc) this is great for logging user activity.

[BobWeaver]

I had a look at implementing this in a new application that I'm working on. I noticed one problem. If used as an auto-entered calculation, and the user duplicates a record, the 'unique' ID will duplicate rather than creating a new unique ID. So, to use this safely, you would need to set the password privileges to disallow duplication of records, or else completely script the creation of new records.

Another possibility is changing the Unique ID field to a stored calculation field. Is there any danger that a stored calc field that references only status functions will ever recalculate?

Or, am I overlooking something obvious?

[keshalyi]

While you COULD do a lookup to a stored calculation or something, I really don't recommend it. The problem then comes when you have to move the record to another file, something that at least for me happens fairly often (archiving, importing or exporting to another database, recovering lost data, etc). When this happens, suddenly your calc changes, and you lose any related files.

[BobWeaver]

I had looked at the possibility of somehow using a lookup too, but couldn't think of any way to make it work. After I posted my musings about using the stored calculation method I realized that, of course, if you import the records into a new file the calculation is going to change.

The dilemma is that during an import, you want the existing value in the field to remain unchanged, but when you duplicate the record, it must change. So, I guess there is no alternative to doing some scripting.

[PTKen]

As soon as I read these new posts, I went back to take a look because I have been adopting this approach to all my databases now too. As described, when I duplicate a record it created a duplicate key, even though I had it set to validate that the field is unique and non-empty. It allowed it to be non-unique despite the validation rule.

I had it set up like so: text field, auto-enter data as calculation using the calc from this thread.

So, instead I tried making the field a calculation field with the calculation directly as the definition and the result set to text. Now when I duplicate a record, it calculates a new value for the field and my keys remain unique!

Does anyone see any problems or loopholes with this approach?

Ken

[BobWeaver]

Yes. See my previous post. If you ever have to import these records from an older version of your file into a newer version (say you are doing an upgrade for example), then the values will recalculate and all your relationships based on that field will break.

[PTKen]

Oh, I see. I had read the posts but I guess I didn't understand it fully. I'll keep pondering this. There must be a solution...

Ken

[keshalyi]

To be honest, this isn't a limitation of the approach, per se, so much as a limitation of FM Pro. I have OFTEN wished I could specify autoenter fields to autoenter on duplicate. After all, if you set up an old fashioned serializing auto-enter, you get the SAME problem.

[Kurt Knippel]

This is one limitation of FMP. What I do is to control through scripting the creation of new records and the duplication of existing records. I just use a set field to set the ID.

[BobWeaver]

Keshalyi, for the record, auto-enter serial numbers, creation date and creation time *do not* duplicate when you duplicate a record. So, you could create 3 fields:

SerialNo - (text or number) Auto-enter Serial number, prohibit modification

CreateDate - (date) Auto-enter Creation Date, prohibit modification

CreateTime - (time) Auto-enter Creation Time, prohibit modification

and then make a unique ID field calculated from them like this:

Right("000000" & SerialNo, 6) & "-" &

Right("000000" & CreateDate, 6) & "-" &

Right("000000" & CreateTime, 6)

This has the advantage that you will get a new unique ID when you duplicate a record, but if you import or export them, they remain unchanged.

This produces virtually the same result as captkurt's original formula except that the serial number is substituted for the record ID which isn't quite as good, but likely not a major problem.

[PTKen]

You beat me to it! That's an excellent solution. I would take it one step further, just to follow the past thoughts on the matter, and substitute two random characters for the hyphens. It's probably not necessary, but it can't hurt either.

Ken

[BobWeaver]

How would you add the random characters so that they wouldn't duplicate when you duplicate a record, but still remain unchanged if you import or export?

BTW, I agree that creation and duplication of records should be entirely under script control. I'm not as concerned that the users will mess things up as I am about the developer (namely me) doing something really silly while in the process of doing some file merges, upgrades or the like. I've done it before, and it's very embarrassing.

[Anh_Tran]

I found the solution and I have tested it . I use same method as capkurt but when I duplicate ..I set the primary number as Daniel 's calculation.

That solve the duplication and used for import/export

Anh Tran

[johnmci]

Bob, this is just a terrific ID solution and it comes just in time for my work on a big solution for my school district.

One problem...I set SerialNo, CreateDate, and CreateTime to prohibit modification. When I duplicate, they all recalc and I get a new unique ID #. Perfect.

When I export the records, all three fields and the calc'd ID # are what they should be.

But when I import these records back to the original file, CreateDate and CreateTime are blank and SerialNo increments to the next number.

If I set these fields to ALLOW modification, however, duplicating, exporting, and importing all work as they should.

So, am I missing something obvious here? And is it too dangerous to leave these fields allowing modification?

John McInerney

[BobWeaver]

Interesting. When I ran my little test, I hadn't ticked the 'prohibit modification' option yet. Later, it just seemed to be a good idea to do this, but I didn't test it again afterwards. It's odd that it made a difference. Good thing you checked it out before implementing.

When you imported the records, did you import as a text file, or as a Filemaker file?

Now I'm also wondering whether it will make a difference whether the CreateDate and CreateTime fields are defined as text or date (time).

Hmm... I guess it's back to the secret laboratory for more for more evil experiments. (The good kind of evil, of course.)

PS. I'm just guessing about the internal details of how Filemaker does an import, but I suppose that a blank record is created, and all the autoenter fields are filled in, then the import data is put into the record. If the fields are set to prohibit modification, then the createdate, createtime and serialno from the imported data won't overwrite what was put into the field when the new record was created. So I guess it does make sense to not select the 'prohibit modification' option. Since the user should never have access to any of these fields anyway, it shouldn't matter.

[johnmci]

OK...this is just plain screwy...

I went back into my system, REset the fields to "Prohibit modification", created a few new records, and exported them in different formats (tab-delimited, merge, filemaker, etc.).

When I imported them, they ALL worked correctly (that is, the fields and the calc'd ID field stayed the same) - INCLUDING the import from the FileMaker file. That's the one that last night was importing blanks for CreatedDate and CreatedTime and then incrementing SerialNo.

So now, this morning, with the fields set to "Prohibit modification" everything works right: importing does NOT change the ID, duplicating does.

I love this solution so much! I'm just gonna keep banging away at it 'til I can recreate the problem I had last night (and figure out what, I'm sure, is a dopey mistake on my part). Thanks for thinking this through, Bob.

John

[Paul Clerc]

Hi.

I am trying to keep a database free of duplicate records when I import to it. So, I created "counter", which is unique in the db that I'm importing to and auto-generated in the one that I'm importing from. For some reason, records with the same value for "counter" continue to import and the "counter" field just comes up empty in the receiving DB. I appreciate any wisdom proffered!

Thanks, Paul

[BobWeaver]

In the import database, if by "unique" you mean that the field's validation is set to unique, it is of very limited use. In my experience the unique validation only works when a user is manually entering data. It doesn't work during imports or other similar automated record creation, including record duplication.

In the export database, if your Counter field is just an auto-entered serial number, then you are definitely going to have serious problems.

The whole point of captkurt's system is its ability to create ID numbers that are unique even across different files, so that you don't have to worry about checking for uniqueness when importing. That's why the ID number is built from several different components, making the probability of generating two identical ones negligable.

[Paul Clerc]

hmm. okay, well thanks - I had no idea that this was the case about unique records and auto-generated numbers.

So - to use Capt. Kurt's method - how would I assign this value to the PrimaryID_key cell? I tried using the define fields function to assign a calculated value, but that does not seem to update the fields...

So (thanks for your patience I will catch on.. )... once I figure out how, I should create this field in the source database and then import the record? I understand how this will insure uniqueness within one or the other db, but how does this work with an import?

Thanks a million for your help

Paul

[BobWeaver]

When importing records, there are at least two different scenarios.

Scenario 1

---------------

You import records from some foreign (non-filemaker) source into your destination file. As far as the destination file is concerned, these are new records which need to have an ID that is unique with respect to the existing records. The unique ID can either be imported with the records as long as they don't duplicate existing ID's in the destination file, or they can be created by an autoenter process during the import. If the original ID's are imported, and were created using captkurt's scheme, then even if they were created in two separate files, there is virtually no chance that there will be any duplicates. So, everything is fine.

Scenario 2

---------------

You import parent records from some external source into a destination parent file, and you also import some corresponding related records from an external child file into a destination child file. The imported parent and child records are related by an existing unique key field, so this MUST be imported into the two parent and child destination files in order to keep the relationship intact. You don't have the option of creating new ID's (at least not without a lot of trouble). So, you definitely need to have a system where you can guarantee that the externally created ID field will not be a duplicate of any existing ID's in the destination file.

If you use something as simple as a plain serial number that starts at 000001 and increments upwards, and you import records from another file that uses the same type of serial number, it's a virtual certainty that you will get duplicated ID's when you import.

[PTKen]

This is working great now. Thanks for this solution. I have one last question on the subject (the novice in me is about to shine through...)

The calculated unique ID, should I set this field to store the calculation results or set to calculate as needed? My thought is that I should store the caluculation, but I wanted to ask before jumping in.

Thanks!

Ken

[Kurt Knippel]

Ohh, this NEEDS TO BE STORED! If you set this to recalculate, you will break all of your relationships!

[PTKen]

Duh! Of course, I figured that out right before coming back to this forum. This is one of those instances when I wish life had an "Undo" command so no one would have ever seen me ask this question...

[falkaholic]

There is also the question of user friendliness. If, you are using this method for say, invoice numbers, this might not be as clear as "INV-00321." Having Joe on the shop floor ask Bill: "I've been meaning to ask you about invoice 007865X40474R730452F."

Having a calculation the simplfies the ID for display purposes might work. Thoughts anyone?

As well, its recommended that your prefix your IDs with 2 or 3 characters, like the above "INV" for invoice. Kurts way puts it at 20 characters, so I'd do a "INV" & " " & <ID calc>. The space is so ithe whole ID gets indexed.

[Ugo DI LUCA]

Yes, you're right falkaholic,

Ihad terrible thoughts later on about my ID settings(see post ), and was quite curious to know if

1. it is better to use the Inv0035 ID as the relationship to line item wether that this auto-entered unique_ID. I'm using some Max and Min(Invoice N

[BobWeaver]

As Captkurt has mentioned in the past, these unique ID's are for internal use only. The user should never see them. If you want to have an additional autoentered serial number like "INV-00321" to keep the user happy, that's fine but keep the real unique ID separate and hidden from the user, where no one will be tempted to monkey around with it.

[falkaholic]

True, but doesn't having a auto entered serial defeat half the purpose of this method?

Half the reason I would want unique IDs is for getting around the importing of other FM records. If there is a 2nd ID the the customer sees (which in my opinion, is the most important one for usability) and if that ID can be non-unique, its nots going ot help; might as well skip it.

What I've done is to have an update script that uses 5,5's Set Next Serial Value [] and Max() to import.

[Kurt Knippel]

Here's the thing. The ID that the customer sees, can be made as unique or non-unique as you need it. The customer can change it, it can be deleted, whatever. None of this has any effect on the structure of the DB, this is what the ID that I proposed is for.

I had a customer who used Phone Numbers for thier customerID (before I got there). Sounds unique right? Well there were unless the customer changes phone numbers, then all kinds of lost records. Once I switched them to my system, they continued to use the Phone Number for the CustomerNumber, but since nothing was linked by this, they could change it daily and not a single record was lost.

[harryk]

Right("00000" & NumToText(Status(CurrentRecordID));5)&"_"&Right("00000"&NumToText(99999 * Random);5)

That's what I use. Capt Curt is better, since using date/time unsures a unique number on the right side also (meaning the status(currentrecordID to be on the left side.

Unless someone fiddles with the date...

But my question is in fact: can the same status (currentrecordID) occur more then once in the same database? I mean, when you delete records, does status(crID) count through, or does it pick up at a certain point when deleting records? In other words, can the number 32000 (example) be used twice when a record with that number was deleted before?

Sorry, using a lot of words for the single question: is status (currentrecordID) always unique?

Harryk

[harryk]

Keshalyi, for the record, auto-enter serial numbers, creation date and creation time *do not* duplicate when you duplicate a record. So, you could create 3 fields:

SerialNo - (text or number) Auto-enter Serial number, prohibit modification

CreateDate - (date) Auto-enter Creation Date, prohibit modification

CreateTime - (time) Auto-enter Creation Time, prohibit modification

and then make a unique ID field calculated from them like this:

Right("000000" & SerialNo, 6) & "-" &

Right("000000" & CreateDate, 6) & "-" &

Right("000000" & CreateTime, 6)

This has the advantage that you will get a new unique ID when you duplicate a record, but if you import or export them, they remain unchanged.

This produces virtually the same result as captkurt's original formula except that the serial number is substituted for the record ID which isn't quite as good, but likely not a major problem.

Bob, this is a very nice solution. If you have the time, I suggest you put your solution in an example file with all the ins and outs in the follow-up of this lengthy tread. (about the prohibit modification etc.)

The only thing I wondered about, is, if the first part of the concatenation, the auto entered serial could be replaced in some way with the status (CurrentRecordID), but I may miss something. May be not possible.

Harryk

[Kurt Knippel]

The RecordID is unique for every record in the DB and is never reused. The Serial Number can be reset or renumber by any number of means, and does not insure uniqueness, even under the scrictest developer's standards.

This is one of the many reasons that I do not use an auto-entered serial number. It is so easily messed with that you might as well treat as one would a phone number.

[BobWeaver]

This may be a bit nit-picky, but it is important to bear in mind the difference between Filemaker's internal record ID, and a field whose contents are based on the internal record ID. Otherwise someone can get into a lot of trouble.

Within one file, every record will have a unique internal record ID. If the record is deleted that ID will never be reassigned to another record. But if these records are later imported into another database, their internal record ID's will almost certainly change to a new ID. However, a unique ID key field that was set with either an auto-enter calc or a set field script step earlier in the old file will still retain the old record ID. This is a common situation which can happen when you update a client's database by importing his working records into a new empty clone. When a new record is created in the new file, it is possible for that new record to be assigned an internal record ID that matches one of the ID's that was used in the old file. If the unique ID field formula consists of nothing more than Status(CurrentRecordID) then you could end up with a duplicate "unique" ID. That is why the unique record ID field should not be based solely on Status(CurrentRecordID), but should include additional components (like creation time and date) to help add uniqueness to the field.

Now let's consider another case. Suppose that you don't use a set field script step or an auto-enter to set the unique ID field. Instead, you just use a regular calculation field. If you do that, then it is guaranteed that the field will always be unique, even after importing them. Unfortunately, although unique, they will not be the same value as they were in the old file. So any relationships that are based on them will break.

Now, what about an autoenter serial number? As long as the user is not given access to the define fields dialog, or any scripts that alter the serial number, the serial number will be unique for every new record. However, when the records get imported into a new file, they will remain the same value they had in the old file, but there is the likelyhood that a newly created record will get a serial number that has been used by one of the imported records.

So, in summary, neither the internal record ID or an auto-entered serial number is acceptable when used alone, but I think either one is acceptable when combined with other components (creation date and time, or a random number).

There are definitely differences to how each of them behave in different circumstances. So, the one you pick should be based on your own particular situation. For example, the suggestion that I gave earlier using a combination of three autoenter fields--serial number, creation date and creation time-- and a calculation field that concatenates them, was intended for the particular situation where you allow the user to create or duplicate records without a script. The internal record ID is not useful in that situation. But for a completely scripted application for which Captkurt's original method is intended, then I would definitely go with the internal record ID.

[Ugo DI LUCA]

Hi ,

Strange issue here....

I've always been concerned about how to somehow use the Unique_Id as an identifier as well as a Unique Record_Id.

In a Product file where each Product is part of a fixed category and where any new record get scripted from global fields, I wanted to use the following calculation :

Right( "0000" & Status(CurrentRecordID); 4) & global &

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

Right( "00000" & Status(CurrentTime); 5) &

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

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

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

where the global is a "2 letters identifier" for the category.

Using "AB" as a global, this would have generate values something like:

0000ABG03232T7313883

0800AB903230Y731388F

But here's a copy-paste of what was sometimes entered when running the test :

nnu>AB5032202731388H

n