Running negative queries

Sometimes you need to get a list of records (say contacts) that don’t have any related records of a particular type (e.g. appointments).

Unfortunately, this is not possible using Advanced Find as it can only return lists of records that have related records (e.g. contacts with appointments). Yes, you can narrow the related records (e.g. contacts with appointments after a certain date) but not to the extent of querying for contacts with no appointments.

To run this type of query you will need to resort to SQL. For example, to get a list of active contacts that have no active appointments and no active tasks use the following:

select fullName from FilteredContact as C
where
NOT EXISTS (select regardingobjectid from FilteredTask AS T where T.regardingobjectid = C.contactid and T.statecode='0')
AND
NOT EXISTS (select regardingobjectid from FilteredAppointment AS APP where APP.regardingobjectid=C.contactid and APP.statecode='0')
AND C.statecode='0'
order by fullname

The SQL statement can be used to write a SQL Reporting Services report or in a data source definition for an Excel spreadsheet.