POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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.

crm-101

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.

 

Importing and Updating Records in Dynamics CRM 2013

 

 

Step 1: Create a new entity titled ‘Account Update’

Importing and Updating Records in Dynamics CRM 2013

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.

Importing and Updating Records in Dynamics CRM 2013

 

 

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.

 

Importing and Updating Records in Dynamics CRM 2013

 

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

 

Importing and Updating Records in Dynamics CRM 2013

 

 

 

Step 3: Import the data and map the information to their corresponding fields. Select import data under Data Management

 

Importing and Updating Records in Dynamics CRM 2013

 

Browse for the update spreadsheet. (Note: your spreadsheet will need to be saved as an XML Spreadsheet 2003).

 

Importing and Updating Records in Dynamics CRM 2013

 

Once you get to this screen you will select Default (Automatic Mapping) and click next .

 

Importing and Updating Records in Dynamics CRM 2013

 

 

 

 

Select the newly created, ‘Account Update’ entity under Microsoft Dynamics CRM Record Types

 

Importing and Updating Records in Dynamics CRM 2013

 

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.

Importing and Updating Records in Dynamics CRM 2013

 

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!

 

Happy CRM’ing!

Avatar for JoeCRM

JoeCRM

Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more businesses and organizations around the world.