Issue in Email Matching to Records (contacts) - how to correct emailSearchBase entry in CRM database

Atul Phatangare 1 Reputation point
2023-01-17T13:10:38.64+00:00

Issue - When we receive an email from a contact eg - John@gmail.com , in CRM , the email "From" in this email is mapped to the contact lets say "John Smith" - Contact Id - 11111 by CRM automatically. For this CRM internally uses an internal table "EmailSearchBase" which is used for mapping of emailaddress to a contact. Please see below email for reference

[https://community.dynamics.com/crm/b/magnetismsolutionscrmblog/posts/dynamics-365-under-the-hood-matching-email-addresses-to-records

Now due to some reason, the EmailSearchBase table has incorrect mapping (or a different email for the same contact when compared with the email from the contact table) like below in our database - not sure of why this is incorrect or different than the contact table.

Below example of two entries / records in EmailSearchBase table -

Record 1 - John@gmail.com - Contact Id = 22222 (this contact id has a different email id like abc@gmail.com in the contact table - so we have a different email id for the same contact in EmailSearchBase and Contact table - ideally both should be in sync or same , but in this case different - so this record is errorneus in the EmailSearchBase table)

Record 2 - John@gmail.com - ContactId = 111111 (this contact has the correct or same email id in the contact table as John@gmail.com - so this data is correct)

So when the email from "John@gmail.com" comes, it is mapped by default by CRM with the incorrect contact id = 22222 since this may be the first record in sequence in the EmailSearchBase table. We want this to map to the correct contact id = 111111.

Now this is because for the contactid = 22222 , in EmailSearchBase table we have incorrect email id as John@gmail.com.

So we have below possible solutions to correct this but not sure of the impact or side effects -

Possible solution 1 (Tried but does not work) - We try to update the EmailSearchBase (EmailSearch) entity from C# code using CRM proxy service to update the emailaddress in EmailSearchBase table for ContactId = 22222 to change the email from John@gmail.com to abc@gmail.com. We tried this but this table is internal to CRM and it does not allow to update this table - it restricts updates.

Note - We cannot change this email from UI - Contact page since due to business logic - this contactid = 22222 is not displayed on UI. Also there may be thousands of records so manual is not an option even if it works.

Possible solution 2 - We update from SQL script directly on the EmailSearchBase table, we update the contactd = 22222 to change its emaill address in this table from "John@gmail.com" to "abc@gmail.com"(to match it with the one from the contact table). But we are not sure if this is a correct way to update the EmailSearchBase table directly from SQL, if there are any other impacts / side effects of modifying directly from SQL script - ideally we should not be updating the CRM tables but we need to correct these entries.

Can we update using SQL script for thousands of records in the EmailSearchBase table to match the emailid in the EmailSearchBase table with the emailid

from the Contact table ?

Appreciate any help for the same.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
23,934 questions
No comments
{count} votes