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.