CRM 2011: Querying Activities Using FilteredActivityPointer

Nathan Eccles, 28 November 2012

When looking to retrieve activity information into a report using SQL, using the FilteredActivityPointer view is often adequate as it returns the generic information which relates to all activities. Unfortunately, we have not been given access to add fields to this universal view and as such, if we want to look at a universal custom field we have added to activities the FilteredActivityPointer view will not suffice. 

If we are required to return a list of all activities showing the activity type, the subject of the activity, and a custom date field “Follow Up On” we are required to look at the individual activity entities to get information regarding the custom field. The first way to achieve this is through querying each individual activity entity and using UNION ALL to get them all to return as one result;

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       Main.mag_followupon AS followupdate
FROM FilteredTask AS Main

UNION ALL

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       Main.mag_followupon AS followupdate
FROM FilteredEmail AS Main

UNION ALL

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       Main.mag_followupon AS followupdate
FROM FilteredAppointment AS Main

UNION ALL

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       Main.mag_followupon AS followupdate
FROM FilteredPhoneCall AS Main

UNION ALL

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       Main.mag_followupon AS followupdate
FROM FilteredLetter AS Main 

However this is slow, and there’s a lot of repeated code. The more efficient way to achieve this is by starting with the ActivityPointer and joining out to the activity types you require in order to retrieve information from the custom field; 

SELECT Main.activitytypecodename AS activitytype,
       Main.subject AS subject,
       COALESCE(Task.mag_followupon, Email.mag_followupon, Appo.mag_followupon, Phone.mag_followupon, Letter.mag_followupon) AS followupdate
FROM FilteredActivityPointer AS Main
       LEFT JOIN FilteredTask AS Task ON Main.activityid = Task.activityid
       LEFT JOIN FilteredEmail AS Email ON Main.activityid = Email.activityid
       LEFT JOIN FilteredAppointment AS Appo ON Main.activityid = Appo.activityid
       LEFT JOIN FilteredPhoneCall AS Phone ON Main.activityid = Phone.activityid
       LEFT JOIN FilteredLetter AS Letter ON Main.activityid = Letter.activityid 

The COALESCE statement looks through the list of fields and returns the first non-null value. Because each row returned will only contain the specific information from 1 of the activities we are joining to, there will only ever be 1 value present in the set of values the COALESCE statement is looking at.

Currently this query will return all activities, and link to the specified ones where possible. If you wish to make the query look at only the specified activities, then the following WHERE clause can be added; 

WHERE ((Task.activityid IS NOT NULL)
       OR (Email.activityid IS NOT NULL)
       OR (Appo.activityid IS NOT NULL)
       OR (Phone.activityid IS NOT NULL)
       OR (Letter.activityid IS NOT NULL)) 

This method also allows for you to have different conditions on the different activity types by adding additional criteria to the joins. 

While the example I have given is relatively simple, this method can be used for complex queries and will greatly increase performance of the report and reduce the need for duplicated code, making it easier to manage.