Rest API (CRM2020R2) Unable to get associated Person by Email

Hi there,

I am using the Rest api to retrieve data but am having problems getting the Person if I have an email address

The first step /sdata/crmj/sagecrm2/-/Email?where=Emai_EmailAddress eq 'email here' works great returning an email object

However when I try to get its Person associations it returns no results 

/sdata/crmj/sagecrm2/-/Email('emaliid')/Person?startindex=1&count=10
If I do it the other way around from the person, it returns the expected email
/sdata/crmj/sagecrm2/-/Person('personid')/Email?startindex=1&count=10
Is there something I am missing?  
Help!
Thanks
  • I have not used the Rest API much.  However, just knowing how the tables work in the background, I think I can explain what might be the issue.

    The email data is stored in two separate tables (well really three...the last one is explained at the end of this response.).  This is so a person can have more than one "type" of email address.  One person to many emails.

    The tables are Email and EmailLink.  The Email table typically will contain the Email ID and the Email .... but not the PersonID.

    The PersonID is stored on the EmailLink table along with the type. This table is used to build a "bridge" between the person and email record.

    These are the really important fields on the EmailLink table.  I am listing four of them (elink_emailid, elink_entityid, elink_recordid, elink_type) with a quick description.

    elink_emailid (this links back to the Email table where you can pickup the email address.)..

    elink_entityid  (Person would be 13, Company would be 5).  It is important to choose the right entity because the record number could be the same and represent another company or person.

    elink_recordid (this should match pers_personid when 13 is selected for the entityid)

    elink_type (this represents the type = Business, Private, etc.)

    Based on what you have above, it appears that you are attempting to use the Email table to find a person id on it.  However, there are no record IDs on that table.

    It appears that when you run it the other way, the API is including the link table.

    I hope to be getting into the RESTapi soon.  However right now, I am not familar with what methods are available to get to data so I cannot steer you in the right direction.

    I only know that the Email table does not contain the personid.  Thererfore, I am guessing that is the issue here.  :-)

    Also there is another table that stores the record id and the email in the same record.  It is CRMEmailPhoneData.  I am not sure if it is offered through the API, but it makes it easier to get to the email address without having to create views with a join.

    I hope this helps!

  • Thank you Michele it helped tremendously and I am able to find the Person now!

    This is what I did (starting with an email)

    1. Search for the email object that has the email ie [email protected] Get that email object's id
    /sdata/crmj/sagecrm2/-/Email?where=Emai_EmailAddress eq '[email protected]'

    2. The Person association doesn't work so we need to use an EmailLink association. However when you link the Email and EmailLinks it doesn't actually filter down to that Email's Links so you need to put in the query yourself
    /sdata/crmj/sagecrm2/-/Email('EmailId')/EmailLink?where=elink_entityid eq 13 and elink_emailid eq 'EmailId'

    3. Now that I have the email link I can use that to get the person
    /sdata/crmj/sagecrm2/-/Person('RecordId')

    Thanks again!!!

  • Awesome!  Glad to hear that it worked for you!  Thank you for posting back your solution...I am sure there are others that will find it usefull too!

  • Michele

    Now that you are an expert in the REST API - would you like to write a blog about retrieving contacts using their email address?

  • Sure.  I will write one up.  If I think it looks good, I will post it! :-)