Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 11.12.2017, 03:41   #1  
Blog bot is offline
Blog bot
24,301 / 817 (76) +++++++
Регистрация: 28.10.2006
crmtipoftheday: Tip #1041: Make Dynamics 365 data import less rigid

Kevin Altman emailed 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
  1. Export a second copy of the data to be imported. In my example I called my second export export2.xlsx. Unhide columns A-C.
  2. To the right of the data table in the original spreadsheet,add a new column and format to the format that matches column C
  3. Click in row 2 of the new column and add a vlookup formula.=VLOOKUP(A2,export2.xlsx!Table1[#Data],3, FALSE)

    This is saying find the value in column A in the table in spreadsheet 2 and return the value from column C. This will get us the current modified on for the record. Copy this to every line of the table in the original spreadsheet (making sure the row number references are correct).
  4. Copy the values in the new column and paste (values only) to column C, overwriting the existing modified on date values.
  5. Remove the new column and hide columns A-C, then save.
You should now be able to import the file and update the records.

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
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 08:16.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.