locked
CRM 4.0 and the Email table RRS feed

  • Question

  • I would like to create an SQL Query to lelect Emails in the CRM 4.0 EMail table. I understand that the ActivityPointer.ActivityTypeCode must equal 4202 for Email type records. How can I tell which table is associated to the Email such as the Contact or the Opportunities, etc.

    Any sample SQL scripts that I could have access to?

    Thanks

    Russ


    Russ

    Tuesday, October 20, 2015 4:42 PM

All replies

  • Hi,

    Just of the top of my head, I have no access to a crm database atm, you need to join on (or Query) the activityparty table on the activitypartyid column (again, I Think). In the activtyparty table the objecttypecode, is 2 for Contact, 1 for account, 3 for opportunity and 8 for user. Look for the objecttypecode on bing (;-)) and you might find them.

    So, activitypointer Points to email, and activityparty. Activityparty Points out which record is associated with the activitypointer. If you find that these things give you a headache, you can be sure that you're not the first one :D

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, October 21, 2015 11:08 AM
  • If you only want email activities, you can use the Email view, rather than ActivityPointer, which avoids the need to filter by ActivityTypeCode

    The ActivityParty view has information about the recipients and sender - this is joined to Email / ActivityPointer via the ActivityId column. The other relevant fields on ActivityParty are:

    • PartyId - joins to the primary key of the related record (contact, account etc). I'd use a UNION query to cover the different entity types
    • ParticipationTypeMask - indicates the party type - e.g. To recipient, CC, Sender
    • AddressUsed. Email address of the party. Either this, or the PartyId (and maybe both) will be populated

    If you want the entity that the email is regarding, you can get this from the regardingobjectid on the Email view, or from ActivityParty


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

    Wednesday, October 21, 2015 7:33 PM
    Moderator
  • David's solution is cleaner.

    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Thursday, October 22, 2015 7:44 AM
  • This is exactly the direction I want to go.  I do have an additional question.

    If John Smith sent and and received emails.

    Which field has John Smith's name and his email address. 

    I would think his sent email would have his email address in the "Sender" field?  And his name in the Email.OwnerIDName field??

    Email received would have his email in ToRecipients.  Where would I find the From user information.

    Or I may be way off on this????

    Thank you very much for your help.


    Russ

    Wednesday, October 28, 2015 7:07 PM
  • Hi Russ,

    You have the sender email on the sender column, if you want more information about the sender you have to go to the activityparty via the activitypointer which is referenced in the activityid column as David wrote.

    If it seems a bit fuzzy, don't worry, it took me some time to get a grip of this too :)

    Regards


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Wednesday, October 28, 2015 9:42 PM
  • Here is what I am getting so far.

    It does look like for a Received Email we will not be able to get the Sender's actual Name, only their sent from email address.

    EmailStatus = Sent

                   Sender = sender email

                    Created by = sender name

                    Participation

                                    To = PartyIDName (To Name)

                                             AddressUsed (TO Address)

                                    Retarding (same as TO)

    EmailStatus = Received

                    Sender = Sender Email

                    Sender Name  ???  <- Not available

                    Participation

                                    To = PartyIDName (To Name)

                                             AddressUsed (TO Address)


    Russ

    Thursday, October 29, 2015 12:55 PM
  • Hi Russ,

    with this query

    select apa.* from email e join ActivityPointer apo on e.ActivityId=apo.ActivityId join ActivityParty apa on apa.ActivityId = apo.ActivityId where e.ActivityId = <ACTIVIYID OF EMAIL>

    you will get all acitvityparties of the email. ParticipationTypeMask tells you what the party is doing, sender, to, cc for example, the PartyTypeCode tells you what sort of object it is, 1 is account, 2 is contact, 8 is user, 2020 is queue (I think). To find the full name you need to join the right table according to the typecode.

    Hope this helps


    Rickard Norström Developer CRM-Konsulterna
    http://www.crmkonsulterna.se
    Swedish Dynamics CRM Forum: http://www.crmforum.se
    My Blog: http://rickardnorstrom.blogspot.se

    Thursday, October 29, 2015 1:25 PM