Dynamics 365 Under the Hood - Matching Email Addresses to Records

Roshan Mehta, 22 March 2018

For those of you tracking emails in the Outlook Client, or using the Email Router or Server-Side Sync to copy emails into your CRM system, you may have wondered how Dynamics 365 matches email recipients to different records. This blog provides an overview of what happens under the hood.

The EmailSearchBase Table

The Dynamics 365 database contains a table called EmailSearchBase, which is used to match email addresses to records in your system. This makes it easier and faster for the system to find matching records based on an email address, by looking at a single table, rather than sifting through all Accounts, Contact, Leads, Queues, Users etc, and any custom entities that are enabled to “Send Email”.

The table contains the following information:

EmailAddress

Self-explanatory, it’s just an email address.

ParentObjectId

The unique identifier (GUID) which represents the record in your system that holds the email address.

ParentObjectTypeCode

A number representing the entity for the record in your system that holds the email address. For example, 1 represents Accounts, 2 represents Contacts.

EmailColumnNumber

A number representing the field that was updated. Note that this differs per entity.

How the EmailSearchBase Table is Maintained

The EmailSearchBase table is automatically maintained by the system, when the following actions occur:

•    An email address is entered into an email-enabled field for an email-enabled entity. Examples of email-enabled fields include emailaddress1, emailaddress2, emailaddress3, while email-enabled entities include Account, Contact, Lead, Queue, User. Note that you can create your own email-enabled entities if you like.

•    An email address is cleared from an email-enabled field for an email-enabled entity.

•    An email-enabled record with an email address is deleted.

To understand this better, let’s look at a few examples.

Email Address added to emailaddress1 for an Account

If a user creates a new Account and enters test@test.com for emailaddress1, the following information will be stored in the EmailSearchBase table.

•    EmailAddress: test@test.com

•    ParentObjectId: {57EDE396-A295-4CF7-829B-ABC0F7AB5838}

•    ParentObjectTypeCode: 1

•    EmailColumnNumber: 42

If an email is sent from test@test.com to a CRM user or Queue, the system will look for any records in the EmailSearchBase table where EmailAddress = test@test.com, and will show the Account in the “From” field. The details of the CRM user or Queue will also exist in the EmailSearchBase table, and will be displayed in the “To”, “CC”, or “BCC” fields.

Email Address changed for emailaddress1 for an Account

If emailaddress1 is changed from test@test.com to myemail@test.com, the existing EmailSearchBase row is updated.

Email Address removed from emailaddress1 for an Account

If test@test.com is removed from the emailaddress1 field on an Account, the existing EmailSearchBase row will be deleted.

Delete an Account

If an Account is deleted, all rows in the EmailSearchBase table are deleted, based on the ParentObjectId. Note that there are no changes if the Account is deactivated. This could be problematic if you have Users who leave the company, and their User record is disabled.

To Sum Up

You don’t need to worry about the EmailSearchBase table – it’s managed automatically as mentioned above. What you do need to worry about is ensuring that your email address data is accurate and up to date, so tracked emails truly reflect every recipient involved.