Microsoft Dynamics 365 | Integration, Dataverse...

Duplicate detection based different phone number formats

(0) Share

Report

Posted on by EBMRay

903

Hello everyone,

In the contacts entity I have around 8.5k rows as duplicate records based on the contact's mobile phone numbers in different formats as below:

+971 05 123 123 123
97105123123123
9.7105123123123
05123123123
551111111
551111111

I found a great tool on xrmtoolbox which is deduplicator that can be found here and using this tool it exported all the duplicates including ContactId, mobilephone, and fullname.

I want to delete all the duplicate records found on the excel sheet that was exported, so what is the best way of performing this delete operation?

I thought about a power automate cloud flow that checks for the contactid in that sheet and deletes it directly from the contacts entity.

Creating a view using Advanced find will it be possible to filter the duplicates based on different phone numbers format then I can perform bulk delete for all?

Finally, is there any other 3rd party tool that can merge the data automatically on a daily basis if any duplicate is found?

Any help is highly appreciated.

I have the same question (0)

All responses (10)

Answers (0)

Ellis 25 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi EBMRay,

Im afraid there is no such tool that can deal with this situation.

Option1: develop a console program to handle this base on your rules.

Option2: Export the data and mark the data to delete in excel. then import it to d365 and delete them.

Was this reply helpful? Yes No
Suggested answer

Nya 29,060 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi,

I think the only way to essentially solve your problem is to FORMAT your contact's phone number.

Here is a code snippet which can format the phone numbers as (xxx) xxx-xxxx and you can adjust it based on your request.

function FormatPhoneNo(executionContext) {
try {
var formContext = executionContext.getFormContext();
var nvsField = formContext.getAttribute("telephone1").getValue();
var nvsTmp = nvsField;
if (typeof(nvsField) != "undefined" && nvsField != null) {
nvsTmp = nvsField.replace(/[^0-9]/g, "");
switch (nvsTmp.length) {
case 10:
nvsTmp = "(" + nvsTmp.substr(0, 3) + ") " +
nvsTmp.substr(3, 3) + "-" + nvsTmp.substr(6, 4);
break;
default:
alert("Phone must contain 10 numeric digits.");
break;
}
}
context.getEventSource().setValue(nvsTmp);
} catch (err) {
alert(err.message);
}
}

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi Nya,

Thank you for your reply and for providing an example.

I will take into consideration validating the format number while the users fill the form based on the code provided.

However, for the duplicate records data that are already formatted wrong in the contacts what's is the best way to do a bulk clean?

Awaiting your response.

Was this reply helpful? Yes No
Suggested answer

Nya 29,060 on at

Like (1)

Report

RE: Duplicate detection based different phone number formats

Hi,

It is recommended to format them in bulk and then set a duplicate detection for Contact entity.

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hello Nya,

What is the best option to format the existing phone number data in bulk?

I already configured a duplicate detection rule for Contact but it's very limited.

Thank you!

Was this reply helpful? Yes No
Community Member on at

Like (2)

Report

RE: Duplicate detection based different phone number formats

Hi

I would simply create a temp field on the contact and than update all these 8.5k contacts with the same text on the temp field and then run a bulk delete job using the temp field in the query.

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi ,

I also have a similar idea in my mind but what is the tools should be used to perform a bulk update to that temp field for all contacts?

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report

RE: Duplicate detection based different phone number formats

Hi,

that depends on what you have in your Excel file from the XRM Tool. You should be able to use the standard import (Import from Excel) function from Dynamics. Add the Temp column to the Excel and then map it to your new field. There is a duplicate detection in the import which should make sure that you overwrite the existing contacts. Do you have a Sandbox to try the import first?

Was this reply helpful? Yes No
EBMRay 903 on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi Axel,

In the excel sheet, I have the contactid and the mobilenumber of the duplicated records.

And yes, I am already using a Sandbox environment for the development process.

So the action that should be taken is to :

1. add a temp field to the duplicated records in the Excel file from XRM Tool.

2. Map the temp field that I filled in Excel with the temp field for the contacts entity.

How the import tool will detect that those are duplicates since I cannot create a view for those contacts in CRM to bulk update the temp field with the same text that I filled in the Excel file exported from the XRM tool?

Thanks!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Duplicate detection based different phone number formats

Hi

I get your point regarding the import job. When using the CSV import the system will create new records or not update due to duplicate detection. Ach why not be simple once in a while. Exporting all contacts would be to much, or? Than you could do a Vlookup in Excel and remove all unneeded contacts from the XML export file.

Sorry for not being able to present a solution.

Was this reply helpful? Yes No