Updated: Aug 6, 2019
The Dynamics 365 data import wizard is a great tool that allows no-code quick data imports to be created by end users without much of technical know-how.
One large shortcoming of the tool is its inability to update existing records unless the Excel document used for import had been generated on the same system that is used for data import. Even if you had a GUID as part of the incoming data set, trying to map it to a record ID would fail on subsequent import as Dynamics will run into a referential integrity issue trying to create a record with the same primary key.
This particular problem forces many teams to use more complex ETL tools for simple imports as soon as there's a need to update an existing record from the outside system, or rely on custom post-processing deduplication/merge scenarios.
This post suggests a workaround that uses a no-code workflow to resolve the update issue.
Let's imagine that we would like to import an Application data set, using a CSV file with these fields:
After the first execution of the import process, our Application entity in Dynamics will have two new records.
Now, Bob's application had been submitted in the 3rd party system and we would like to import Bob's data so that the new status in Dynamics will be updated as well:
If we imported this record as is, we'd get two application records for Bob. Instead, we want to find pre-existing record, based on Application ID, and update it.
The first step to set up this orchestration is to set up self-referential lookup on Application entity. Let's call it Original App:
Next, we'll create a workflow that will fire during Create event of an Application record and will try to resolve Application ID to an existing record.
The second step is to configure the workflow to try and match Application ID of the new record to Application ID of the existing record. If match is found, we want to populate Original App lookup on the new record to the value of matching previously created record.
To do that, we'll use the Attribute Setter step of TCS Tools custom workflow (see https://www.itaintboring.com/tcs-tools/solution-summary/ ).
TCS Tools's Attribute Setter workflow step can find parent record using FetchXML and update a lookup with this value. To set it up requires two things:
1. Create a TCS Lookup Configuration record
This configuration record specifies Fetch query to execute against the record that triggers the worfklow's execution. The values in #...# will be replaced by the actual record's values during execution. Fetch expression finds another Application based on cdr_appid field (like Bob-1) with primary key different from that of the current record.
If result is found, the lookup cdr_originalapp on current record will be pointing to the original application for Bob.
2. Next, we will use this configuration record to set up TCS attribute setter step in the workflow:
When the workflow is activated, importing a record with duplicate App ID will result with two records in the system but the newer record will have its Original App lookup populated:
The next step is to check if Original App field contains data. If it does, update the original application record with fields from the new record.
Add an Update step for 'Original App' lookup and in this step copy Status field from the latest Application record to the Original application record.
Finally, after we have updated the Application with all the latest data, we need to delete the newest record, which is now a duplicate (if Original App field contains data).
Workflow designer doesn't support record delete out of the box. We are going to use the Delete step of Dynamics-365-Workflow-Tools custom workflow activity ( https://github.com/demianrasko/Dynamics-365-Workflow-Tools/tree/master/msdyncrmWorkflowTools ) to delete a record:
And that is it, we are done! We now have a pretty simple, no-code approach to use basic Import wizard and have it update existing records.