Sign in
Microsoft.com
 
Microsoft
 
 
 

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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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