When it comes to importing and updating records in Microsoft Dynamics CRM, there are two distinct functions: importing records using the import wizard and updating existing records by exporting to Excel. But what if you have a list of records that are not exported from CRM, and you want them to update CRM data? In this blog, we will discuss just how you might do that with a fancy workaround.
Using built-in functionality in CRM, this solution involves creating an entity and workflow combination. You will import your spreadsheet into the custom entity and then use a workflow to automatically apply your updates to the correct records. Once the tool is built, you can reuse it to quickly update your data.
Before getting into the details, this method works best if:
- You have records coming from the same source repeatedly such as another database or a subscription-based list.
- You have a unique identifier, such as an account number, in your source data, as well as in CRM. If your data does not meet this requirement, this solution will not work.
- You have hundreds (not thousands) of records in your source data. With larger amounts of records, you should run these updates in batches of less than 1000, or ideally, use another solution such as SSIS or Scribe.
- You perform the update during off-peak hours.
An Example of Updating Records
To demonstrate, we'll create a tool to update the address on accounts from a spreadsheet below containing new address information.
Step 1: Create a new entity. Add fields for each column of the spreadsheet with a look-up to the entity you are updating.
In this example, we will call the entity "Account Update" and place fields for Street 1, Street 2, City, State, and Zip, as well as a lookup to the account.
Step 2: Create a new workflow that triggers "on create" of your new entity.
In this case, the data will move from Account Update to Account. The completed workflow looks like this:
And to "View Properties" on the only step:
Step 3: Perform the update
Now that we have created the tool, all we need to do is import our spreadsheet into the Account Update entity, and watch the update go. Two important reminders when doing the import:
1. When performing the actual update, make sure you map the data to your new entity, which in this case is the Account Update entity.
2. When mapping your fields, make sure you're setting the look-up on your update entity to use that unique field on the record you're updating. In this case, I'm setting the account lookup on the Account Update entity to use the Account Number as the key.
Expanding your solution
Once you have built your basic solution, you can include more complex logic depending on your specific challenges.
- Add a check condition to your update workflow to see if the field in the source is blank, and to ignore it if it is. (This allows you to import incomplete data.)
- Option set support: If you did not use global option sets in your original entity, you can use check conditions to set the value of that option set based on the value imported into your update entity. (If you did use global option sets, then you can just map them like you do text fields for this solution.)
- Transformations: If your source data does not exactly match the format in your CRM, you can have your workflow transform the data before it is updated to the destination field.
There you have it—some things to think about when updating records in your CRM.