Enhancing OData Integration with Dynamics 365 FO Virtual Entity Change Tracking
Finance and operations apps have a change tracking functionality option that's known as?row version change tracking. This option enables Microsoft Dataverse to be used for incremental synchronization of data. Change tracking is a prerequisite for several features, such as Data archival, Synapse integration, Copilot, Mobile offline, and Relevance search.
It is important to acknowledge that while the concept of employing change tracking to capture delta changes is not new within the domain of Dataverse, the implementation nuances vary due to the multi-table structure inherent in data entities/virtual entities within Dynamics 365 FO. In my forthcoming example, I will illustrate how this functionality operates based on the consolidation of data from three distinct tables within Dynamics 365 FO.
To utilize this functionality, it is imperative to enable the row version change tracking functionality within Dynamics 365 FO. Please refer to the provided resources detailing the prerequisites and steps for enabling row version change tracking for data entities:
The questions I aim to address through conducting this test are as follows:
To test this functionality, I created a new data entity FMLabCustomerEntity based on the fleet management tables FMCustomer, FMAddressTable and FMCustGroup.
The FMLabCustomerEntity meets the prerequisite of enabling the "Allow Row Version Change Tracking" property.
The FMLabCustomerEntity exhibits several characteristics: it lacks range filters and group-by conditions. Additionally, it consists of three data sources, falling below the threshold of ten. These data sources, namely FMCustomer, FMAddressTable, and FMCustGroup, are all tables, interconnected via outer joins. Moreover, none of the data sources are time state-enabled, nor do they incorporate filter or group-by conditions. Furthermore, there are no virtual fields present within the entity ( https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/data-entities/rowversion-change-track#enable-row-version-change-tracking-for-data-entities)
To comply with the precondition that all tables within a data entity must enable the "Allow Row Version Change Tracking" property, it was observed that three tables had this property set to "No." Consequently, to address this requirement, table extensions were created for each of the three tables, enabling the "Allow Row Version Change Tracking" property by setting it to "Yes," as depicted below.
As illustrated below each table will get a new column, system field of the rowversion type that's named SysRowVersion, will be appended to each table within the data entity.
Following the creation of FMLabCustomerEntity in Dynamics 365 FO, the next step involves enabling FMLabCustomerEntity as a virtual entity within the Dataverse environment. (https://learn.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/enable-virtual-entities)
Make sure Enable change tracking is on for virtual entity Customers (mserp)
In the foreseeable future, the forthcoming functionality of Auto Create/Auto Refresh will significantly streamline the process of publishing Dynamics 365 FO data entities as virtual entities in Dataverse. This advancement will automate the synchronization of any modifications made within Dynamics 365 FO, ensuring seamless reflection of changes within the virtual entity in Dataverse.
?Here is the dataset I will use, the fleet management customer demo data.
Here is a Sample web api request to get the delta data of FMLabCustomerEntity ?virtual entity With the header “Prefer odata.track-changes”.
Upon the initial request, the entire dataset ( 6 customers) relevant to the query is received, as no delta has been applied at this stage. However, the response includes an @odata.deltaLink value, which contains a deltatoken parameter. This parameter is designed to be utilized in subsequent requests to discern the changes that have been implemented since the execution of the initial query.
领英推荐
In response you’ll receive @odata.deltaLink token which have url which you can use to get difference/changes i.e. Create, Update, Delete operation done on FMLabCustomerEntity records.
The @odata.deltaLink URI obtained from the preceding example facilitates the retrieval of changes in the FMLabCustomerEntity. It is advisable to retain the delta token received in the latest call for subsequent use, allowing for seamless inclusion in subsequent requests.
For more information about how to use change tracking to synchronize data between Dataverse with external systems see (https://learn.microsoft.com/en-us/power-apps/developer/data-platform/use-change-tracking-synchronize-data-external-systems)
In the event of a data alteration within any of the tables comprising the Dynamics 365 FO data entity, will the change tracking mechanism trigger for the entire entity?
Considering that the field ‘name’ in FMcustGroup table and ?'AdressLine2' field? in the FMAdrdressTable are absent in the FMLabCustomerEntity? data entity, would modifications made to these fields trigger the transmission of the entire entity?
In the Fleet management demo data, we have 6 customers, and the first 2 customers are connected to customer group = adv_mem_1
Upon modifying the ‘name’ field within the FMCustGroup table for the adv_mem_1 record (note the ‘name’ field of FMCustGroup table is not included in the FMLabCustomerEntity data entity)
I observed that both 2 customers associated with the customer group 'adv_mem_1' were returned in the delta value . Similarly, altering the addressline2 field also prompted the delta value of FMLabCustomerEntity as shown below.
This test demonstrates that any alteration to fields within the tables encompassed by the data entity triggers the transmission of the entire message as a delta change. Notably, changing the ‘name’ field in the FMCustGroup table affected all customers associated with that specific customer group. This potential cascade of changes in the receiving system may raise concerns, especially if the customer information remains unchanged despite the adjustment, such as retaining the same customer group affiliation.
In conclusion, it is conceivable for delta fetch operations to yield unchanged data. While the delta link ensures the retrieval of at least changes, it does not guarantee that all data returned was modified; rather, it commits to presenting alterations at a minimum.
How is Delete operation handled?
Upon deleting a customer named "test customer" from the FMCustomer form, querying the virtual table for delta change will yield the following response.
Deletes are reflected in the response through an @odata.context, indicating a deleted entity with a reason of "deleted," including the unique identifier of the customer.
Data entity row deletions are tracked by using the Dynamics 365 FO AifChangeTrackingDeletedObject table. The ref RECID field shown in the picture above in AifChangeTrackingDeletedObject table lack utility due to referencing deleted records. Therefore, in the receiving system, a practical solution involves storing the GUID , such as "00007538-0000-0000-ef02-000010000000" upon initial receipt of the entire dataset during the first call or upon new customer creation. This approach ensures accurate tracking of deleted customers, as it provides the necessary reference point for identification.
In summary, regarding change tracking in Dynamics 365 FO virtual entities for finance and operations applications, changes are retrieved if the last token falls within the default value of 10 days, as opposed to the 90-day default value utilized for Dataverse tables. However, if a delta link remains unused for an extended period, it may become inactive and fail to function properly.
Certain query options are not supported in Change Tracking Web API requests, notably $filter, $orderby, $expand, and $top. These options are restricted when utilizing the Prefer: odata.track-changes header in a Web API request.
For additional insights into utilizing Change Tracking within the Microsoft Dataverse Web API, you can explore a comprehensive explanation alongside a detailed YouTube video that delves deeply into this topic. https://crmchap.co.uk/exam-pl-400-revision-notes-implementing-data-synchronisation-using-microsoft-dataverse/
I got 2 similar questions in email about the nr of changes made so we can add?$count?to get the number of changes made. https://operations-XXX.crm4.dynamics.com/api/data/v9.2/mserp_fmlabcustomerentities/$count?$deltatoken=19794728%2102%2f19%2f2024%2011%3a15%3a48"
Dynamics AX / Dynamics 365 FO Technical Consultant
1 年Thanks. How this handles large transactions? For example you update Customer1 in session 1 and keep the transaction open. Then you update Customer2 in session 2 and perform Odata change tracking requests.(that should change change tracker pointer and return Customer 2). Then you return to session 1 and commit