>Hey Walter,

>>IMO, there is no common wishdom. When you're using intelligent keys, you'll have the add a FOR !DELETED() filter to the primary index. To make rushmore work, you'll have to have another (regular) index on the same field.

>The FOR NOT DELETED() filter didn't seem to work
>when I tried it. Have you ever used it succesfully?

Yep, I use it quite frequently. What do you mean by it doesn't work ? What doesn't work ?

>I'm trying to keep the size and complexity of my
>CDX files to a minimum. I'm having some performance
>problems with some of my merging apps now because of
>my CDX's I think. So creating two indicies for the
>same field may not be preferable.

>I don't understand why they can't use filtered
>expressions anyway?!?

Me too. I sure will be glad if FOR NOT DELETED() filtered indexes were rushmore optimizable when SET DELETE = ON.

>>If using artificial keys, you won't need the FOR !DELETED() filter on the primary index, however, you'll have to force the logical intelligent key to remain unique. The most efficient way to do this is by a Candidate key. However here you still have to deal with the DELETED() records. So you'll have to add a FOR !DETELED() filter to the candidate key. In fact you're shifting the problem from the primary key to the candidate key.
>
>...and haven't really solved the DELETED problem
>at all

This conslusion is correct.

>>Another way is to force uniqueness at your business logic. But this is not as secure as a candidate index. One bug in your application (or another hacking your table via for e.g. ODBC or VFP) might cause the ship to sink.

>Yeah, I considered that, but for the reasons you've
>outlined, I've discarded it.


>>IMO, This whole DELETED() matter is a big shortcomming in VFP. It would be nice if we could regard a deleted record as a NON-existing record.

>Or at least offer this as an option.

I've submitted a VFP.next wish for this behaviour. Though I really think this enhancement is valuable I don't think it will be in VFP 7.


>What did you think about the
>... + TRANSFORM( DELETED()) idea?

Then you can only have one deleted record for the PK value. Besides this, the TRANSFORM function is quite slow.

>>>I have tried filtering on NOT DELETED(), but 1)
>>>it doesn't work, and 2) you can't use the
>>>index in otherwise optimizable queries.
>>
>>1. It should work.

>Seemed so to me. I haven't revisited it since
>I couldn't get it to work in the first place.
>If you say that you've used it successfully, I'll
>give it another shot.

Please try. It should work. If not, then come back to me and report exactly what doesn't work.

Good luck,

Walter,