locked
How to select all activities with both customerID and incidentID in CRM 2011? RRS feed

  • 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 PM
    Moderator