>>>So now I have a double problem - inventing a (surrogate) primary key,
>>>which can be automatized, and the old problem once more - avoid the
>>>clash between possible natural unique keys. Since the key is entered as
>>>a first field, and it does take some time 'till the user gets to the
>>>last and saves, other user may have gotten the same key from the key
>>>offering routine; the third and fourth got another ones. Now the first
>>>three users revert and don't save - I have three unused keys. I don't
>>>mind, but tax collectors are very suspicious if the invoice numbers are
>>>not exactly consecutive.
>>>
>>>Did anyone solve this problem already?
>>
>>I've got a little bit different but still associated with the above issue question. There's a user-entered field that should be unique. So, I use candidate index which is checked when user tries to save changes with tableupdate(). The bad thing is that deleted records are also checked unless I use filter for not deleted() (goodbye Rushmore :( ). The workaround that I see is to use two indices on that field (w/ and w/o filter).
>>
>>Does anybody have better solutions?
>>
>>Thanks in advance.
>
>You could use following solution:
>1. You have candidate index (no filter).
>2. When you try to save a record and trap duplicate key error, you can check >deleted() status of the existing record and, if deleted=.t., recycle it.

Thanks, Edward. I tried that approach and here some of my thoughts:

1. This works only with views. When I used table (ether with record or table buffering) I couldn't move record pointer to check if the record with the same field value is deleted. The error "The uniqueness of ...bla,bla,bla violated" occured when I tried to do that.

2. What if user doesn't add a record but changes the existing one? How do I recycle the deleted record? I tried to REPLACE the unique field of the deleted record with the STR(primary key)(I use surrogate Integer keys) of that record, assuming that the field is a string and user can't put numbers in it. So the value would be unique.But it won't work with numeric fields.

Thanks, again.