>>>>>>>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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only