Asked by:
How to select all activities with both customerID and incidentID in CRM 2011?

Question
-
Hi,
I want to select all activities with incidentID (left join - where incident exists) and contactID (join).
For example, if activity is linked to either customer or incident or marketing campaign I want to see the TOP level of cusotmer's contactID in all cases even if it is linked via enquiry or customer campaign etc.
Please see my basic query below:
SELECT
ar.ActivityId
,ar.PartyId
,ar.ActivityPartyId
,ap.RegardingObjectId
FROM ActivityParty ar
JOIN ActivityPointer ap ON ar.ActivityId = ap.ActivityId
I use CRM Dynamics 2011.
Many thanks
- Edited by Tadas V Thursday, November 27, 2014 10:54 AM Adding more details.
Thursday, November 27, 2014 10:19 AM
All replies
-
Use the query below
select ap.ActivityId ,ap.RegardingObjectId,i.IncidentId, ap.RegardingObjectIdName, I.Title, RegardingObjectTypeCode from activitypointer as ap left join Incident I on I.IncidentId = AP.RegardingObjectId left join Contact as C on C.ContactId = Ap.RegardingObjectId where RegardingObjectTypeCode = 112 or RegardingObjectTypeCode = 2
Regards, Saad
Thursday, November 27, 2014 10:33 AM -
Thank you Saad. It does bring some light into my issue. I want to get contactID at all times. I guess that Left join to contact will bring activities that are directly linked to customer. I am also interested in activities that are linked via incident or marketing campaign to customer.Thursday, November 27, 2014 10:41 AM
-
You need all activities that are linked to contact. You also need all activities that are present on incident and marketing campaign and have customer as contact.
correct me if i am wrong.
Regards, Saad
Thursday, November 27, 2014 10:53 AM -
Yes to both. In general, I need all activities that are linked to contact. I am also interested in activities that are linked to contact via other entities such as enquiry or marketing campaign.
Thursday, November 27, 2014 10:55 AM -
you can use something like this for all activities of contacts and activities related to contacts on incident.
select ap.ActivityId ,ap.RegardingObjectId, I.customeridname, I.CustomerIdType, C.FullName from activitypointer as ap left join Contact C on C.ContactId = AP.RegardingObjectId left join Incident I on (I.IncidentId = AP.RegardingObjectId and I.CustomerIdType = 2) where (RegardingObjectTypeCode = 112 or RegardingObjectTypeCode = 2 ) and (C.FullName is Not null or I.CustomerId is Not null)
Similarly you can do it for campaign and enquiry.
Regards, Saad
- Edited by Mohd Saad Thursday, November 27, 2014 11:44 AM
Thursday, November 27, 2014 11:43 AM -
The easiest way to do this is to create a separate query per relationship (e.g. one for activities related directly to the contact, one for those by case etc.) and combine them using UNION
Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk
Thursday, November 27, 2014 12:00 PMModerator