Auto-entered Record IDs

[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.

The calc is:

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

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

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

This generates a value something like:

007865-040474-730452

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 eexample?

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 seperate 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 accidentially 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 reply to this FAQ or contact me privately.

Kurt

*the ID created is truely 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.

[ July 25, 2001: Message edited by: CaptKurt ]

[Kurt Knippel]

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.

code:

---

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 way you get the random characters mixed into the key and still stay within the bounds of 20 characters.

[d94dsj]

Kurt,

I wonder what method you use to save a "found set" of records? With the 20-character long key, the "traditional" method of using "Copy all" and pasting into a global text field, and then using a relationship to get back to them, limits the number of records to 3047, which isn't very many...

Thanks for any comments,

Daniel

[Steven H. Blackwell]

Copy All Records is essentially an obsolte method for resolving larger found sets. See the work by FSA Member Deborah Fuchs of Aptworks Consulting. http://www.aptworks.com

HTH

Old Advance Man

[Kurt Knippel]

quote:

---

Originally posted by Old Advance Man:

Copy All Records is essentially an obsolte method for resolving larger found sets. See the work by FSA Member Deborah Fuchs of Aptworks Consulting.

http://www.aptworks.com

---

Debbie's work on this is fantasitc and really shows alot of possibilities for the saving of found sets.

Something else to keep in mind is that using the 20 character ID is NOT necessary for keeping a found set, there are so many other methods as well.

Personally I use the Copy All Records pretty rarely, and generally never to save a found set.

[d94dsj]

Many thanks for the link and your comments, OAM and CaptKurt!

I've browsed through the PDF document describing the techniques, and I'm quite impressed with the quality of it. I especially like the "scientific" approach and the generic reasoning that is applied.

But the document does not talk about saving found sets in particular, and the only technique I found applicable to my situation was #3 Update Matching Records. (The situation is mixed Win/Mac environment, and FMP 5.0).

The way I figured I could use the technique for saving the found set was basically the following:

1. Create a helper file: foundset.fp5

2. Export all primary keys to the foundset.fp5 file.

When returning to the found set, I would import records from the foundset.fp5 file with the options of "Update Matching Records in Current Found Set" (not "Add Remaining Records") and use a "dummy" global field for importing data from an empty field in the foundset.fp5 file.

Now, this works for any number of records, but it might not work in a multi-user scenario if two or more users tries to save the found set at the same time, unless you use one helper file per user.

Plus, it takes quite a while. I tested this locally on a laptop PC (PII-366 MHz) in a test file with about 7000 records and about 20 field with random data in it. It took under 10 seconds to export the data, and over one minute to import all records for returning to the found set.

This will increase when setting it up on a server unfortunately does not seems very efficient.

Am I missing something obvious here? Is there a better way which I can't make out from the techniques in the document?

quote:

---

Originally posted by CaptKurt:

Debbie's work on this is fantasitc and really shows alot of possibilities for the saving of found sets.

Something else to keep in mind is that using the 20 character ID is NOT necessary for keeping a found set, there are so many other methods as well.

Personally I use the Copy All Records pretty rarely, and generally never to save a found set.

---

Three of the four techniques described in the document relies on either FM5, FM5.5, or Applescript and thus does not work on PCs or in earlier versions of FM (I still have clients using FM4...).

The remaining technique (Multiple Find Requests) is not applicable to saving a found set, at least I cannot see how it would be.

I now plan on using the "Copy All Records" method for found sets less than 3407, and maybe the above for more. But I'm concerned about the multi-user issues.

I guess you could increase the number of records in the found set in the "Copy All Records" if you use CurrentRecordID, but then it would be harder calculating the limit (well, maybe not that hard...).

Are there any other (better) methods for saving a found set?

Thanks for more comments,

Daniel

[Steven H. Blackwell]

You could save the record ID's for the specific found set. The problem with that of course is that it will later fail if one of those records were to be deleted. You could control this by having the deleted be scripted against the multikey holding the ID's.

A word or two about those keys. The limit for each LINE is 60 characters, and no word in a line can have more than 20 characters. If a line violates this

this restriction the key will fail at that line and ALL subsequent lines.

Old Advance Man

[d94dsj]

quote:

---

Originally posted by Old Advance Man:

You could save the record ID's for the specific found set. The problem with that of course is that it will later fail if one of those records were to be deleted. You could control this by having the deleted be scripted against the multikey holding the ID's.

---

But the "Go To Related Record" script step does not fail if some line of the multikey contains an "unvalid" key. So I'm afraid I don't understand what you mean...?

quote:

---

A word or two about those keys. The limit for each LINE is 60 characters, and no word in a line can have more than 20 characters. If a line violates this

this restriction the key will fail at that line and ALL subsequent lines.

Old Advance Man

---

I'm aware of this - but do you mean that this fact has any implication on the above technique? I can't see any, but please prove me wrong!

Do you think I'm right in my other comments in my previous post, do you agree I mean?

Thanks...

/Daniel