>>>>>>>I have a one-to-many view for which I would to give a filter for !deleted() in one table, but not the other. In the view builder (or otherwise), how do I reference one table or the other when giving a filter "deleted() = .F." ?
>>>>>>>
>>>>>>>Erik
>>>>>>
>>>>>>Try: WHERE !DELETED("Table1")
>>>>>>
>>>>>>Ed
>>>>>
>>>>>this gives "Variable 'table1' not found" (where table one is the name of my table).
>>>>
>>>>I tested it and it works fine. Can you give us the full syntax of the SQL you are using?
>>>
>>>This is probably where we are missing each other. This view was created in the view designer, and I am attempting to add the filter in the 'Filter' tab. Adding the above suggestion in the expression builder under filter gives the error.
>>
>>Can you give us the SQL code up to that point?
>>
>>Ed
>
>Sure. Here it is....
>SELECT Memb.nickname, Memb.lname, Histcode.histdesc, History.histyear,;
> Memb.mcampus, History.histtype, Memb.membnum, Memb.address1, Memb.address2,;
> Memb.city, Memb.st, Memb.zipcode, Memb.tel;
> FROM temptest!memb INNER JOIN temptest!history;
> INNER JOIN temptest!histcode ;
> ON History.histtype = Histcode.histcode ;
> ON Memb.membnum = History.hmembnum;
> WHERE Memb.mlocalnum = ?m.mlocalnumber;
> AND LEFT(History.histtype,3) = "OFL";
> ORDER BY History.histyear DESC, History.histtype
>
>I want to filter deleted records in the history table, but not in the memb table. Thanks for your help.
>
>Erik


Since I don't have a copy of your tables, I can't test the following code, but go ahead and give it a try.

SELECT Memb.nickname, Memb.lname, Histcode.histdesc, History.histyear,;
Memb.mcampus, History.histtype, Memb.membnum, Memb.address1, ; Memb.address2, Memb.city, Memb.st, Memb.zipcode, Memb.tel;
FROM temptest!memb INNER JOIN temptest!history;
INNER JOIN temptest!histcode ;
ON History.histtype = Histcode.histcode ;
ON Memb.membnum = History.hmembnum;
WHERE Memb.mlocalnum = ?m.mlocalnumber;
AND LEFT(History.histtype,3) = "OFL";
AND DELETED("history") = .T. ;
ORDER BY History.histyear DESC, History.histtype