Complex Filters
EXISTS
The EXISTS function acts on a query and returns true if there are any matches. Conversely, it returns false if there are no matches. This is useful in View filters to limit the records in grid.
In the following example, a Contacts View with the following filter will only return Contacts that are currently active in a Group:
EXISTS(SELECT GP.Group_Participant_ID FROM Group_Participants GP
JOIN Groups G ON G.Group_ID = GP.Group_ID
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE()+1))See EXISTS Function.
Correlated Subqueries
A query used to calculate a field or filter a view is called a subquery. It is a query inside the larger query (the View). In most cases the subquery is correlated with the view. This is done using a WHERE clause in which one or more fields in the subquery are tied to a field in the View.
In the following example, a Contacts View with the following filter will only return Contacts that are currently active in a Group. The highlighted portion is the clause that correlates the subquery with the outer query (view).
EXISTS(SELECT GP.Group_Participant_ID
FROM Group_Participants GP JOIN Groups G ON G.Group_ID = GP.Group_ID
WHERE GP.Participant_ID = Contacts.Participant_Record
AND GETDATE() BETWEEN GP.Start_Date AND ISNULL(GP.End_Date,GETDATE()+1))This same technique may be used in a calculated field to correlate the the calculation with the record in the view.