locked
Finding Contact in Activity Table in DB RRS feed

  • Question

  • I have to do reports on Activities using the data from the DB.

    I want to find the Contact IDs for the From and To persons in the Activites.

    Does anyone know what field is the contact in the Activities table in the CRM DB?

    Tuesday, November 10, 2015 4:43 PM

Answers

  • This data is in the activityparty table. The contactId will be in the partyid, for records where partyobjecttypecode = 2. The participitationtypemask field indicates whether the recipient was in From, To, CC etc - these are the participitationtypemask values:

    Sender

    1

    Specifies the sender.

    ToRecipient

    2

    Specifies the recipient in the To field.

    CCRecipient

    3

    Specifies the recipient in the Cc field.

    BccRecipient

    4

    Specifies the recipient in the Bcc field.

    RequiredAttendee

    5

    Specifies a required attendee.

    OptionalAttendee

    6

    Specifies an optional attendee.

    Organizer

    7

    Specifies the activity organizer.

    Regarding

    8

    Specifies the regarding item.

    Owner

    9

    Specifies the activity owner.

    Resource

    10

    Specifies a resource.

    Customer

    11

    Specifies a customer.

    Partner

    12

    Specifies a partner.


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by JMcCon Wednesday, November 11, 2015 2:08 PM
    Tuesday, November 10, 2015 5:13 PM
    Moderator
  • Hey,

    I think below should work. If you need the Contact related info then you can join with contact.

    SELECT 
    ActPointer.subject, Contact.ContactId 
    FROM FilteredActivityPointer AS ActPointer
    Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
    left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
    and ActParty.partyidname is not null
    where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
    and ActParty.partyobjecttypecode = '2'

    Thanks!

    • Marked as answer by JMcCon Wednesday, November 11, 2015 2:08 PM
    Wednesday, November 11, 2015 2:47 AM

All replies

  • This data is in the activityparty table. The contactId will be in the partyid, for records where partyobjecttypecode = 2. The participitationtypemask field indicates whether the recipient was in From, To, CC etc - these are the participitationtypemask values:

    Sender

    1

    Specifies the sender.

    ToRecipient

    2

    Specifies the recipient in the To field.

    CCRecipient

    3

    Specifies the recipient in the Cc field.

    BccRecipient

    4

    Specifies the recipient in the Bcc field.

    RequiredAttendee

    5

    Specifies a required attendee.

    OptionalAttendee

    6

    Specifies an optional attendee.

    Organizer

    7

    Specifies the activity organizer.

    Regarding

    8

    Specifies the regarding item.

    Owner

    9

    Specifies the activity owner.

    Resource

    10

    Specifies a resource.

    Customer

    11

    Specifies a customer.

    Partner

    12

    Specifies a partner.


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    • Marked as answer by JMcCon Wednesday, November 11, 2015 2:08 PM
    Tuesday, November 10, 2015 5:13 PM
    Moderator
  • Thanks for the reply David. So I just go into the ActivityParty table and the partyid field is the contactid? Is the participationtypemask field in the ActivityParty table?
    Tuesday, November 10, 2015 9:41 PM
  • Hey,

    I think below should work. If you need the Contact related info then you can join with contact.

    SELECT 
    ActPointer.subject, Contact.ContactId 
    FROM FilteredActivityPointer AS ActPointer
    Inner Join FilteredActivityParty AS ActParty on ActPointer.activityid = ActParty.ActivityId 
    left join FilteredContact AS Contact on ActParty.PartyId = Contact.ContactId
    and ActParty.partyidname is not null
    where ActParty.participationtypemaskname in ('Sender', 'To Recipient')
    and ActParty.partyobjecttypecode = '2'

    Thanks!

    • Marked as answer by JMcCon Wednesday, November 11, 2015 2:08 PM
    Wednesday, November 11, 2015 2:47 AM
  • Thanks for the reply.
    Wednesday, November 11, 2015 2:09 PM
  • Thanks guys.
    Friday, November 13, 2015 9:51 AM
  • Hi Kalim, Can you explain this code a bit. For example why do you have that the participationtypemaskname in (Sender, ToRecipient) instead of having it equal to 1 and 2 ? Also why is partyobjecttypecode equal 2 ? Thanks
    Friday, November 13, 2015 8:07 PM
  • Hey,

    PatyObjectTypecode 2 is for contact. You can use either name or value both will work. I have used name in my example. You can use Value also ie. participationtypemask. It is not going to be changed. 

    Thanks!

    Friday, November 13, 2015 11:38 PM
  • Thanks for the reply Kalim. So if it is the contact or citizen you are getting the value of then you use participationtypemask equals 2? You use a left join as you want to get all the actparty regardless if there is a match for it in contact .... Will this not return all the contacts irrespective of whether it is attached to an actparty ? I'm just a bit confused why you use the left join at this point.
    Saturday, November 14, 2015 5:12 PM
  • There should be no difference in the results between using the left join or an inner join - there would only be a difference if there were an activityparty with partyobjecttypecode = 2, but the partyid didn't match to a contact, which shouldn't happen.

    Note that partyobjecttypecode = 2 means the party is a contact, whereas participationtypemask = 2 means the party is a 'To Recipient'

    I'd suggest you always use the numeric values of participationtypemask to filter, and not the labels, as the labels could change (e.g. based on language)


    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Monday, November 16, 2015 6:15 PM
    Moderator
  • Thanks for the reply David. Is participationtypemask = 1 mean the party is a 'Sender'
    Monday, November 16, 2015 7:48 PM