Great comments, Garrett! I'll use it. Currently, this is for a rehabilitation practice that has 4 digits in the client id field. I hadn't automatically assigned the id yet because usually in a practice or facility environment, the case # is assigned on the fly by a receptionist (manually using a chart).

However, reading your note made me remember not to ASSume anything. I'll put a call in to them Monday to see if they want the product to assign the codes.

JR

PS--Yes, the last name first name thing is a poor way to assign numbers; however, my name would still be the same--chose to keep my given name when I got married. . . But that's another story, another site. . .

>>How do you get around the Uniqueness is Violated error? Is there
>>anything more savvy than populating the unique code field with something
>>like '####' and going out and looking for a duplicate?
>
>Well, for primary keys, you should use a surrogate key: a key that has
>no meaning. In other words, FOSTEDON01 would not be a good primary key
>for my wife: when she married me and changed her name, the key would
>either not match her name, or it would have to be changed in every place
>that referred to it.
>
>Another thing is that primary keys are used for joins, and the smaller the
>key is, the faster the join. If you use an integer for a primary key, it's
>only 4 bytes wide, but you can store 2 billion unique keys in it.
>
>I use a separate table and fill in primary keys in order, but other people
>use random key generators like SYS(3), and check for uniqueness before
>they insert the key in the table.
>
>As far as candidate keys go, I have handled this at times by trapping the
>error, and at other times by USE AGAINing the table I was inserting into,
>and searching in the second table for the value.
>
>Your Mileage May Vary. :-)