What do you really want to see and why, Abbasaif?
The problem with DELETED OFF and joins is the deleted flag is lost in the query result. In the result all rows are combined and have DELETED()=.F., of course, they are new rows with a new row structure and combined columns. The WHERE DELETED() clause isn't working wonders to only take deleted rows of any table into account, and what's most important in that aspect, you rarely have the situation all records belonging together by a join condition are deleted.
For example,an order item is deleted from an order that doesn't delete the order itself, the deleted order item has no meaning anymore besides it once was an order item and the customer decided against ordering it. And not only that, if you could inverse the available data to only deleted data, the deleted order item has no deleted order and thus will not appear in the inverse query, as it has no parent order, the order isn't existing undeleted and deleted at the same time, even if it would be Schrödinger's order.
Because of that the typical status is a mix of deleted and undeleted records of a join and therefore as MarK last suggested to turn DELETED() into a real field is the best you may do and then you can BROWSE for any such field being .T. as MarK suggested or just pick one or just display it as info, it'll likely only be for administrative data maintenance anyway.
The bad side effect of deleted rows can be to have a gap in numbering. You can fill that with the next record instead of continuing with the max+1 value, but not if you have a primary or candidate index on that column. So you could turn such fields to their negative value before deletion. But that also doesn't solve that problem for more than one reuse. Imagine you store the sortorder of order items. When you remove the item with sortorder 2 you turn that to -2 and then can reuse the 2, but with a very unsure customer deleting that item again, you get a double -2. The solution here is easy, you don't ever renumber items, you just iterate the records in ascending sortorder and in a report don't print sortorder, but a report variable initialized to 1 and incrementing. To be able to sort in a new item between existing ones is a problem also if no item is deleted at all. You have to renumber all order items then. And in a DBF you have to do that including deleted rows.
In short, you have to deal with the problems arising from deleted rows being kept in the DBF and more important in this case, also in the index tags. Defining all indexes FOR !DELETED() is also a viable idea to not also have the constraint of unique deleted numbers like IDs or sortorders. But before you redefine all your indexes, maybe you find an easier solution.
It's natural once you can see deleted rows in browse with that deletion mark in DELETED OFF mode it would be torture to not be able to undelete it, in other databases you don't have that luxury at all, but developers and users can cope with that, too. All features added to VFP like RECALL and the DELETED() function and are merely a compromise because of the DBF structure.
Bye, Olaf.
Olaf Doschke Software Engineering
