PowerObjects Blog 

for Microsoft Business Applications


Importing and Updating Records in Microsoft Dynamics CRM


Importing and Updating Records in Microsoft Dynamics CRM

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.

Updating Records in Microsoft Dynamics CRM

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. Importing and Updating Records in Microsoft Dynamics CRM

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:

Importing and Updating Records in Microsoft Dynamics CRM

And to "View Properties" on the only step:

Importing and Updating Records in Microsoft Dynamics CRM

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.

Importing and Updating Records in Microsoft Dynamics CRM

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.

Importing and Updating Records in Microsoft Dynamics CRM

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.

Happy CRM'ing!

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

15 comments on “Importing and Updating Records in Microsoft Dynamics CRM”

  1. Dear Joe,
    Wow - I have ALWAYS sought this feature, but never got this idea!
    Thanks for providing it!

  2. Good post! You state "you can use check conditions to set the value of that option set based on the value imported into your update entity" I have an entity that has 1) a yes/no option with a source database field containing 0 or 1 and an option set with 87 unique options (government!). How do I use check conditions to set these?
    Thanks,
    Ken

    1. Hi Ken,

      For the 1/0 field, you can actually get it imported without using a check condition. When setting up the import, map your source 1/0 field to the yes/no field on your import entity. It should pop up a box that lets you assign your source values to the destination values. (see picture in link below)

      https://powerobjects.com/wp-content/uploads/2013/08/update_import_comment_image1.jpg

      For the 87 unique options, I am assuming that is not a global option set? If it is not, what you can do is include a large section in your workflow that moves the data to the final entity. So if you have 87 options in your final entity that you are trying to update, you first have to add an option set to your import entity with that many values. Then you put a section in your workflow that checks the value of the field on your import entity, and then has a bunch of “otherwise if” conditions for each different value of your option set. If the field with the 87 options is called “Order”, it would look something like this (with 87 steps instead of 3), with each step setting the correct value on the destination entity. (see picture in link below)

      https://powerobjects.com/wp-content/uploads/2013/08/update_import_comment_image2.png

      Does this help?

      This might not be a great solution if you are going to be modifying the option sets a lot (as you will have to update this workflow every time). You may want to consider using scribe or ssis for your data updates if it gets too much more complex.

      1. Thanks,
        I changed to a Global Option set and followed the logic in your post to update.
        Now I am trying to figure out how to update 1:N relationship. Product has 0 to many patents (same patent can apply to more than one product but expiry date may be different). Excel sheet has ~1000 rows containing Product, Patent, Expiration date. Need to examine each Excel row - either add a new patent, delete (maybe patent was withdrawn this month) or update expiry date. I have Product Entity and Patent Entity. I am guessing the solution includes a PatentUpdate Entity (like above). But workflows don't seem to be able to delete a record. Suggestions?

      2. Does the Order of the option set with 87 choices start with 0 or a 1? And if you have the default set to nothing in the option set (blank if nothing is selected), does that count as the first Order in the list?

  3. Going on 2 hours now. I have tried to understand your tutorial and through trial and error and extreme stubbornness, I have managed to import my Accounts under Account Update but I got lost on the New Process when I clicked "Set Properties" (your image above calls it "View Properties - Image #3 above) and I have this thing on the Set Properties called "Form Assistant" - And I see all the fields I have created but am I suppose to be doing what you show in Image 4 above to this Process / Set Properties screen? Joe, I see your comment below to a user stating the blog "works" - take a newbie or intermediate user of CRM, sit them down in front of your blog (let's assume they have never attempted this or created a process/workflow from scratch) and observe their frustration. UX 101. Love the attempt Joe but needs some User Experience tweaking. I'll figure it out eventually and maybe I'll post a link to the revised Blog for all to share?

    1. Joe, apologies, I should have uploaded a print screen of where I'm stuck! I think part of it has to do with the slight tweaks that have been made to the CRM interface since you wrote this blog. It's such a GREAT blog so I hope you'll updated it. Here is my image:

  4. I've set this up, but think I have some small detail wrong somewhere. When I perform a test import, I get an error saying the lookup value is a duplicate, (the # I am using as a lookup to update the original account). I went back and tried another test and chose to allow duplicates, but still received the same error. And advice? Thanks!

  5. Hi, I like the idea about updating the field of the existing account and if the account isn't in the CRM it suppose to create it from the new entity, I took your idea and implement it on the leads, I've created new entity and import all leads . How do I create workflow that check if the account/leads existing? I would like to check it via email.
    thanks, ilan

  6. Hi, I am trying to do the same for updating Currency(Entity) Exchange Rate(Field). But in Step 2 (Shown above), unable to select the currency entity ( it's not populating in related entity section.) for Update Step. Is there anything specific I need to do for this ?

  7. Hi Joe,
    which relationship type did you create between account and account update entities?
    while creating lookup to account entity you should create a relationship right? I am thinking if 1:N relation would be enough!
    Thanks
    Gopi

PowerObjects Recommends