>>>>>>>>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

Ed- Thanks for the reply. I got 'deleted('history') = .F.' to pass in the view designer under filters, but it seems to have no effect on the outcome of the view. History records that are deleted show up anyway (!?!?!?) Does anyone have any idea what I am doing wrong?