Bulk update AD/Entra ID records in Dataverse

[pa111]

I have a solution that contains a Dataverse custom table called AllRecords.

The AllRecords table has an Active Directory / Entra ID LookUp column called Assignee.

For several reasons, I have had to delete the Assignee column in my development environment and replace it with a new column called AssigneeV2.

After I deploy the new version of my managed solution into my production environment, the historical data contained in the Assignee column will be deleted, but all data in the other columns will remain for approximately 130,000 rows. I want to update all historical rows in the AllRecords table with the Entra ID GUIDs, into the AssigneeV2 column, so that users can see who the assignee was for historical records.

I have not yet deployed to production, so I can export all data in the production environment before I deploy.

What is the best way to bulk update these rows? My preference is to use a 'low code' method such as a Power Automate flow or XLSX import that I can import directly into the production environment (i.e. outside of the managed solution as this is a once-off update).

Thanks!

[Fubar]

First, if you import a new version of the Managed Solution and it is the source of the Assignee field, and that new version no longer has Assignee in it after importing the Managed Solution it will have deleted Assignee from your target environment (including the data it contains).

If this is the case it is probably safer to do a bit of a shuffle (otherwise you are relying on your export being 100% re-importable and not accidently missing any records etc), e.g. create new Managed Solution 'B' with the new V2 field in it, import it, copy data from old field to new field, import the new version of the old Managed Solution (including the V2 field), delete the 'B' Managed Solution from the target (this is not functional but just to get rid of the managed 'B' layer)

Also, if intending to use Export to Excel, just be mindful that the max number of records in 1 export is 100,000 (default is 10,000, but can be increased to 100K)

[sudipm · Accepted solution]

Hello @pa111 

1. Dont delete the old Assignee column in your dev environment, if you have already done it add it back with exact schema name and datatype as on your current production, this will ensure this field will not be deleted once you bring your managed solution to production.
2. Create a workflow (not power automate) on this table call it "copy assignee to assignee v2" , dont select any triggers like create, update on it, just select it as an ondemand workflow, Ensure it is not user level and org level workflow. 

3. Add the following steps to this workflow
         If Assignee field contains data

                 Copy Assignee to Assignee v2 field

4. Export your solution and import to your production.

5. The assignee field will not be deleted on your production, and a new Assignee v2 field will be added. Old records where assignee field has data will be preserved.

6. Using xrmtoolbox -> Execute workflow on records step -> execute the workflow created in step 2 on all records on production. This will complete your data migration from assignee where its present in historical records to the assignee v2 field.

7. Now you are ready to delete the old field, delete the workflow created in step 2, and then the field, then  export a new managed solution, then import it to production

[pa111]

Thanks for your responses @sudipm and @Fubar.

For the benefit of others looking to bulk update data in Dataverse, our approach was as follows:

• Because we had already deleted the Assignee column in our development (unmanaged) environment, we re-created it using the same schema name and relationship (with the Entra ID table)
• This means we had two columns: AssigneeLegacy (with the original schema name), and AssigneeV2
• We then exported it as a managed solution into our test environment, and verified that the historical data was still present in the AssigneeLegacy column
• Using XrmToolBox, we created an XML script and used the Bulk Data Updater tool to copy the Entra ID GUIDs from AssigneeLegacy to AssigneeV2
• We will then:
  • Deploy the managed solution to our production environment
  • Use Bulk Data Updater to copy production data from AssigneeLegacy to AssigneeV2
  • Once that is complete and the data verified, we will delete the AssigneeLegacy column in our development (unmanaged) environment and deploy that up to higher environment

Some of the lessons we learnt are:

• XrmToolBox is quite powerful and allows you to manage your Dataverse tables quite efficiently, with minimal coding knowledge required (you can create an XML script using dropdown menus and the logic is similar to writing SQL statements or using filter and sorting criteria in an Excel table)
• It is easier to move data between fields in Dataverse than updating data via bulk uploads

Thanks again!