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

Опции темы Поиск в этой теме Опции просмотра
Старый 04.11.2017, 01:13   #1  
Blog bot is offline
Blog bot
24,234 / 809 (75) +++++++
Регистрация: 28.10.2006
powerobjects: Using Version Number to Track Changes in Dynamics 365

Every entity in Dynamics 365, both OOB and custom, has a field named [Version Number]. It can’t be seen from the UI, as it is a system field, but it can be seen from when querying the database or when fetching data using FetchXML. This field is used by the system to track changes in a single record, and can be leveraged when doing data migrations/integrations to check if a record has changed from its source.

What is Version Number?

At the database level, Version Number is a rowversion data type (sometimes called timestamp).

Some key details from TechNet:

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. This counter is the database rowversion. This tracks a relative time within a database, not an actual time that can be associated with a clock. A table can have only one rowversion column. Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column.

So, to summarize, each time any field on this record gets updated, Version Number will change (tick up an increment).

How to track changes with Version Number

In the context of a one-way integration from one Dynamics 365 to a staging database, version number could be leveraged using SSIS by:

From the Source to Staging Table
  • Check to see if the record exists in staging by comparing uniqueidentifier
  • If record doesn’t exist, create the new record in staging table
  • If record exists, compare Version Number
    • If Version number is same, no action is needed (nothing changed)
    • If version number is different, delete existing record in Staging and recreate with new attributes.
A dataflow might look like this:

Here, we stage with some records including the Version Number (account in this example). From there we do a lookup in our staging table, matching the accounted to see if it exists and passing the version number stored in the staging DB. If there is no match, we create the record. If there is a match we compare the version number from the source with staging. If these values are different, we delete the current record in staging, and recreate the record with what was queried from the source.

A thing to note, when querying the data directly from on-prem SQL, version number will be a binary data type and must be cast to a string to compare the values in the conditional split. However, if the data is being queried using FetchXML this value will be cast as an integer, but the functionality works the same way.

Why you would use Version Number

There are two main reasons why version number might be a good choice to track these changes. First, because it works at the database level, the version number will change even when a record is updated using a direct SQL update (not recommended or supported, but we’ve seen it happen none the less). Other values, such as modified on, will only update at the software level. Using version number will allow you to catch all changes, even unsupported ones.

The second reason is the simplicity of development. Most people here tend to think that development is simpler using this method, requiring you to log less information in auditing (much as modified on) allowing for more rapid and streamlined development.

Itching to learn more? Explore our blog!

Happy Dynamics 365’ing!

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
powerobjects: Restore from a Dynamics 365 Online Instance Backup Blog bot Dynamics CRM: Blogs 0 23.10.2017 20:13
waldo: Microsoft Dynamics NAV 2018 / Dynamics 365 “Tenerife” Blog bot NAV: Blogs 0 23.10.2017 07:13
stoneridgesoftware: How to Integrate Power BI with Dynamics 365 for Financials Blog bot DAX Blogs 0 01.04.2017 02:17
jaestevan: Microsoft Dynamics 365 for Operations Blog bot DAX Blogs 0 02.11.2016 01:11
Platform updates overview - 3.70.B - NAV2009 R2 Blog bot Dynamics CRM: Blogs 0 07.02.2011 22:06
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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