>>I'm having a little difficulty understanding how VFP treats deleted records. I understand that a deleted record is not removed from the table and is just marked for deletion. I was under the impression that if I "SET DELETED ON", I could code as if the records marked for deletion were not there.
>>
>>I posted a similar question a little earlier but I didn't get a response. I have a table with 4 fields. zip_pk, zip_zipcode, zip_city and zip_st. 2 indexes Primary is zip_pk and a candidate zip_zipcode.
>>
>>If I delete a particular zipcode and then try to recreate it I get a uniqueness violation. So I changed my base form class to put "SET DELETED ON" in the load event. This didn't do the trick. There has to be a way around this without issuing a pack? Isn't there?
>>
>>sorry to be a nuisance
>>
>>Paul
>
>Index normally sees all records deleted and non-deleted. There are few ways to avoid unqueness violations:
>1. You can add FOR NOT DELETED() clause to tag expression. This is quick but not very good way because this tag cannot be used for Rushmore anymore.
>2. You can recycle record, i.e. if you see that you have already deleted record with the same index expression, then instead of creating new record, you just go to the deletd one and recall it.
>3. You can completely eliminate case of violations, by changing index expression field for a record when you delete it. For example, if you have primary key=1345 and you delete the record, you also change primary key to -1345.

Hi Ed,

I think I found a solution to this problem in Advisor. I was wondering if you could tell me your opinion, and tell me if this is a Rushmore optimizable solution.

Change the candidate index from:

zip_zipcode

to:
zip_zipcode+iif(deleted(), transform(recno(), "999999999"), space(9))

thank you,

Paul