Bulk update with distinct values for respective records

(0) Share

Report

Posted on by Davyjones

1,299

Hi Experts,

We are using D365 for sales and we want to update a field in Account. The field to be updated is not the same for every account, there are close to 50 different values from which each account will get one. We have an excel with the GUID of each account and then the field with the respective value for each account, in total we have close to 60,000 accounts to update.

I tried uploading it using the import data wizard and it thinks i am uploading new record with the same GUID and fails saying - Operation failed due to a SQL integrity violation

I tried creating a view and tried updating it using the Excel online feature through vlookup etc its works for certain records and fails for others since it kicks the duplicate detection rules. Updating on excel online does not have the flag where we can allow duplicates which we have with the upload method. Seems like we could remove duplicate detection rules and use the Excel online method to update.

But this feels very odd, where we have the primary identifier of the record but not able to do a simple update on a field and wanted to see if there are other way to do it OOB. The other way i can think of is to write a C# code to update it but that feels like a lot of work to just update one field when we also have the GUID and no way OOB to get there.

Please help, Thanks in advance for your response.

All responses (3)

Answers (1)

Davyjones 1,299 on at

Like (0)

Report

RE: Bulk update with distinct values for respective records

Hi Leah,

Thanks for your response, yes the above way will work and i also found another way through on of the tools in XRMToolBox called Dataimport ++. I have been using Dynamics since the version 2011 and very surprised to find that we do not have an easy data import feature built in, there was a way of importing Dynamic worksheet when we exported as templates but i think that has been overcome by the Excel online feature but its kicking the duplicate detection rule, which we were able to circumvent when using the old way.
Verified answer

Leah Ju Microsoft Employee on at

Like (1)

Report

RE: Bulk update with distinct values for respective records

Hi Davyjones,

You can create one flow to achieve your goal, and you need use 'Excel Online (Business)' connector to get and update data in the excel.

https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/

Steps:

Create EXCEL with an account that has access to CRM data, select all data to create table in the excel.

Go to Power Automate to create one instant flow.

List rows present in a table(Excel):

Then you can update table with value from excel.
Suggested answer

XM-22040801-0 11 on at

Like (0)

Report

RE: Bulk update with distinct values for respective records

Hi,

A simple and reliable way to do mass updates is to use SSIS with Kingway Soft components: www.kingswaysoft.com/.../ssis-integration-toolkit-for-microsoft-dynamics-365

The component can be used for free if it is run from Visual Studio.