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.
Note: For the least amount of interruption, you will want to perform the update during off-peak hours.
Begin with some data in a spreadsheet that contains updated account information.
Step 1: Create a new entity titled ‘Account Update’
Create fields for all columns/types of information you wish to update. Also, make sure you create a lookup field titled ‘Account’ or something similar that will be able to search for lookup to the Account entity. In this case, we will need to have fields to match all of my sample data columns (Account Name, Account Number, Street 1, Street 2, City, State, Zip, and Main Phone). Publish all customizations.
Step 2: Create a workflow to automatically sync newly imported data from entity ‘Account Update’ to ‘Account’. Here’s how:
Under Settings/Processes, create a new process.
Configure exactly as shown below. You’ll need to add only one step: update records. Make sure your look-up field that you added (titled “Accounts” here) is selected in this step.
Select properties and for each field you wish to update, select a corresponding Account Update field for it to sync to, as shown. It is important to note that your Account for may look different than the one shown. You can customize the form to show any type of information you choose. Here I have moved all of my edited fields to the top for simplicity sake. Publish all customizations
Step 3: Import the data and map the information to their corresponding fields. Select import data under Data Management
Browse for the update spreadsheet. (Note: your spreadsheet will need to be saved as an XML Spreadsheet 2003).
Once you get to this screen you will select Default (Automatic Mapping) and click next .
Select the newly created, ‘Account Update’ entity under Microsoft Dynamics CRM Record Types
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.
If the update has ran correctly, your account information should match the spreadsheet you imported.
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. Make sure to check out our other blogs for even more Dynamics CRM information!