crmtipoftheday: Tip #1041: Make Dynamics 365 data import less rigid
Kevin Altman emailed firstname.lastname@example.org with the following question about data import in Dynamics 365:
Users want to perform updates to existing data in CRM but the export for update/re-import process is too rigid as data often changes between the export and re-import, blocking the import (I guess CRM forces you to have no changes to re-import). Are there any workarounds?First, a few details about how the data import works. When you export to Excel, you will notice that columns A-C are hidden.
Unhide columns A-C, and you will see three columns where the header name begins with (Do not modify). If you have read tip of the day for long, you know that we like to dare greatly, so we are going to modify the do not modify columns (we also removed the tags from our mattresses).
So what are these hidden columns?
A: The GUID of the record:this is how CRM knows what record to update. If it used the record name, it would potentially hit duplicates and not know what to do. By using the unique identifier for the record, it precisely matches to the correct record. This is also how import utility and immersive Excel know when a line is an update vs. a new record to create–if there is no value in column A, it will create a new record. This is also a field that you can use to trick import to updating records that are not included in the export.
B: Row checksum: a hash that Dynamics 365 uses to see if the record has changed. That way it only updates the records that have changed, while ignoring the rest.
C: ModifiedOn: This is the important one for our example. This is the modified on date of the record in CRM. When importing, Dynamics 365 compares the date in column C with the modified on date of the record. If they are different, the import will fail for that row. This is there for a reason–if you export and wait 3 months to import, and users have subsequently made changes to the record in CRM, if it didn’t compare the dates, newer data could get overwritten by older data from the import file.
Let’s say you have a busy CRM environment where system processes and data integrations are frequently updating records. You want to do a data cleanup of your contact data. You export out your active contacts to Excel, and you have your worker bees start cleaning up the data.
If your environment is active, chances are that at least some of the records will fail to import because their modified on has changed in CRM before your data cleansers have finished their cleanup. Modified On changes if anything with the record changes, sometimes if someone saves a record without changing anything, or if someone walks in the vicinity of the record and sneezes. So even if none of the fields in the export file have changed, the record will not update because the modified on date of the record in CRM is different than the date in column C of the spreadsheet.
So how can we get around this?
Note–the following solution is not supported (as Microsoft has told you not to modify these columns) but it works and has been tested by the author of this post. He is not responsible if you mess your data up though).
So we exported our contact data out, cleaned it up, and now we want to reimport it. Here is one approach to avoid the date restriction
Photo by Sam Mgrdichian on Unsplash
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|crmtipoftheday: Tip #1022: Finding your least active Dynamics 365 users||Blog bot||Dynamics CRM: Blogs||0||14.11.2017 23:12|
|crminthefield: Podcast and Overview: Microsoft Dynamics CRM 2011 Update Rollup 16||Blog bot||Dynamics CRM: Blogs||0||23.01.2014 03:15|
|atinkerersnotebook: Walkthrough & Tutorial Summary||Blog bot||DAX Blogs||1||09.09.2013 09:11|
|Platform updates overview - 3.70.B - NAV2009 R2||Blog bot||Dynamics CRM: Blogs||0||07.02.2011 22:06|
|Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM Online Data Import tool||Blog bot||Dynamics CRM: Blogs||0||05.06.2009 05:10|
|Опции темы||Поиск в этой теме|