Peter:

Maybe I'm missing something (not surprised if so) but assigning a unique id hasn't been much of an issue. Maybe you're talking rocket science and I'm back in the stone age. That's what happens when your first programs were on a punched card system with no OS. Then the "unique ID" came from a control card at the beginning of a job and God save the computer operator that screwed it up -- God did save a lot of operators back then.

One need for a unique ID that I can think of is in for sales orders/invoices/checks/journal entries, etc. In that case I setup a separate database that has one record that contains the next sequential invoice or order number. When a new number is needed, lock the record containing the next sequential number, increment the number, add my new invoice (or whatever), then unlock the updated record that contains the next sequential number. No big deal, hardly ever a problem, and if someone resets the number they could potentially start duplicating sequential numbers. But that can be avoided by simply checking for duplicates and issuing a warning, etc. Bottom line, very very very rarely are there problems.

In another situation is where someone in a multi-user environment wants to isolate a group of records for special handling. For example, they select several records that are to be batched together and transmitted somewhere. To uniquely identify these records I have a field comprised of the user's computer name (first part of SYS(0) result) and TTOC(DATETIME(),1). Since I know that any one user at their workstation can only be doing one thing with my application, and can typically count on all the workstations having unique names, the resulting string is different from anything else. If the unique ID field indicates the record has been grabbed by someone else then you manage the exception.

And then, once in awhile I want to assign a unique key to a record in a file that is being shared by others but don't have a separate database with a control record (like the invoice example did have). This is probably the issue you are addressing. Since the keys assigned are typically in ascending sequence I often again use a date/time stamp (using the TIME() function to attain a little finer granulation) and check to see if a record already exists with that ID. I like date/time stamps because they can be calculated without going anywhere, and give you a reference point if you're trying to debug a problem. On the other hand, it's a pain if a workstation has their date/time incorrect - which happens often enough. Duplicate identifiers can occur for a couple of reasons (a) my instance of the program is generating keys so rapidly that approximately 1/18th second resolution just isn't short enough (b) another user in a multi-user environment just generated the same key. In any event, if there is a dupe recycling through the computation usually generates a unique ID. The only gottcha I can think of is you have to lock the file, not the record, while you're going through your key generation/checking/update routine. In any event, the file lock won't be held for very long. If a date/time stamp isn't desired you can always check the contents of the key field in the last record in file and use that as a starting point for the next key.

Like I say, maybe I'm missing something, in dire need of recycling my synapses, like to hear myself typing, or need to manage my time better.

Regards,

Scott