Hi, I am trying to write a filter that checks for Debtors that don't have a CN_Group record. It seems to be an issue to have linked tables to the DB_Main or I can't get the syntax right.
The Sql query is simple but how would I add this to my filter?
SELECT CN_GroupLink.GroupID
FROM CN_GroupLink RIGHT OUTER JOIN
DB_Main INNER JOIN
CN_Main ON DB_Main.DebtorID = CN_Main.DebtorID ON CN_GroupLink.ProspectID = CN_Main.ProspectID
WHERE (CN_GroupLink.GroupID IS NULL)
ORDER BY DB_Main.AccountNo
I have used links to another table successfully in filters but not a 3rd table? 2 table example :
SO_Main.Status=0 AND (SELECT COUNT(EmailLogID) FROM SO_EmailLog WHERE SO_EmailLog.InvoiceID = SO_Main.InvoiceID ) = 1
I tried several variations of the below filter but the Joins seem to be my issue :
(Select GroupID FROM CN_GroupLink WHERE CN_GroupLink.ProspectID = CN_Main.ProspectID AND CN_Main.DebtorID = DB_Main.DebtorID) = 'af73293e531c46b8a9ea'
Any help would be appreciated. Cheers