Change Tracking in AX2012 R3

In AX 2012 R3, Microsoft built a small project (a couple of classes and tables) to expose the SQL Server Change Tracking to AX. In this blog I’m going to walk you through the process of exploiting this feature.

In previous versions of AX2012, Microsoft used to track changes in the the Retail module by creating handlers in the insert, update and delete methods for the tables that need tracking.

In R3, the whole method used for tracking has been changed in the Retail module. AX now uses the power of Microsoft SQL Server Change Tracking to track the changes in AX tables. We can use the same feature for other things within AX, an obvious example would be for building exports that only send changed data. Using Change Tracking means less coding (if no coding at all in some areas). Moreover, this will significantly improve performance as well.

In the following examples I added four jobs to enable change tracking for any change in the products (inventTable and any child table related to it – e.g. InventTableModule). Afterwards, I want to be able to query the tables for the changes (insert and update) that occurred in the inventTable table:

Job 1:

Firstly, to enable tracking on a table, the tracking should be enabled at two levels:

1- The whole database

2- On each table that the user wishes to track.

The following job will enable tracking on the whole database to start with. Then the job will enable the tracking for a query which in return will enable tracking for all the tables under this table.

server static void aaChangeTracking_1_EnableTracking(Args _args) { Query query; AifChangeTracking changeTracking; AifChangeTrackingTable ctTable; ;   //Need to be run on server side new AifChangeTrackingPermission().assert();   info(strFmt("%1", AifChangeTrackingConfiguration::isChangeTrackingEnabled()));   // Run this first to globally enable changeTracking //This should be run once AifChangeTrackingConfiguration::enableChangeTracking(true);   // Here we are loading the query that the webservice uses (I took a wild guess to figure this out) query = new Query("AxdItem"); AifChangeTrackingConfiguration::enableChangeTrackingForQuery("TestScope1", query);   changeTracking = AifChangeTracking::construct(query, "TestScope1", AifChangeTrackingType::SqlChangeTracking);   // Did we enable change tracking? info(changeTracking.isChangeTrackingEnabled()? "true" : "false");   // did we enable change tracking for this specific query? info(changeTracking.isChangeTrackingEnabledForQuery()? "q:true" : "q:false");   // redundant check... but anyways its checking if the specific table has change tracking enabled info(changeTracking.isChangeTrackingEnabledForTable(tableNum(InventTable))? "t:true" : "t:false");   // Revert the permission assert CodeAccessPermission::revertAssert(); }

In the above code, permissions need to be set and reverted in the two lines below:

new AifChangeTrackingPermission().assert();

// Revert the permission assert CodeAccessPermission::revertAssert();

Then the system will enable change tracking by using the enableChangeTracking method. If the value passed to the method was false, then the system will disable the change tracking on the whole database.

To enable the tracking for a query, use the method “enableChangeTrackingForQuery” and pass it the query defined in the previous line. In our case, it’s the AxdItem query that is for the products.

One important thing to mention here is that this job will only run on server side. So, the easiest way to resolve this is to create an action menu item that will run on the server side and point it to this job. Normally, of course, you would place the code into a server side class; we’re using a job here just for convenience while demonstrating how to build the code.

Another thing to mention is that by default the retention period (the period SQL will keep the change tracking data) is two days. This can be increased and decreased based on the user’s preference.

Job 2:

After enabling the tracking in both the database and the tables that we need to track, we need to link the child tables to the parent table. This means that if a change occurred (insert or update) in the child table then the parent table should be touched.

E.g. In our example, I need to make sure that whenever a record in the InventTableModule has been created or updated, the inventTable is to be set as touched. This will help because I can enquire on changes later only on the parent table (e.g. inventTable) rather than checking the changes in all tables related to the inventTable. This will be done by creating triggers in the child tables to touch the parent tables. AX made this a simple process, you just need to define a query that includes all the tables needed then simple pass this to the createTouchTriggersForQuery method:

server static void aaChangeTracking_2_CreateTriggers(Args _args) { Query query; AifChangeTrackingTable ctTable; ;   //Need to be run on server side new AifChangeTrackingPermission().assert();   query = new Query("AxdItem");   AifChangeTrackingConfiguration::createTouchTriggersForQuery("TestScope1", AifChangeTrackingTriggerType::AfterInsert, query); AifChangeTrackingConfiguration::createTouchTriggersForQuery("TestScope1", AifChangeTrackingTriggerType::AfterUpdate, query);   // Revert the permission assert CodeAccessPermission::revertAssert(); }

Job 3:

Tracking the versions will be saved in a table in AX called AifSQLCTVersion. The following code will update this version control table:

static void aaChangeTracking_3_UpdateTrackingVersion(Args _args) { container conVersion; ;   //To record the newest version //There is a standard AX batch job that does this AifSqlCtChangeTracking::recordCurrentVersion(); }

This can be run as a batch job by calling the following batch job: AifChangeTrackingVersionUpdateJob

Job 4:

To find the changes, I created a batch job that will define a query for the inventTable only. Remember in job 2 we created triggers for the child tables to touch the inventTable. So, in this case we don’t need to check the changes in the child tables (e.g. invnetTableModule). we only need to call the inventTable.

In the following example, the user needsto pass the “getChanges” method two parameters:

1- AifChangeTrackingTable temp table (called it “ctTable” – the system will populate this table with the changes and the versions for the changes. it will also populate the recids for the records changed)

2- utcDateTime (This will be the change needed to be tracked since which dateTime)

server static void aaChangeTracking_4_GetChangedTable(Args _args) { Query _query; QueryBuildDataSource _qbds; AifChangeTracking _changeTracking; utcDateTime _dateTimeYesterday; AifChangeTrackingTable ctTable; ;   //Change the date based on what is needed _dateTimeYesterday = DateTimeUtil::addDays(DateTimeUtil::getSystemDateTime(), -2);   new AifChangeTrackingPermission().assert();   _query = new Query(); _qbds = _query.addDataSource(tableNum(InventTable));   _changeTracking = AifChangeTracking::construct(_query);   _changeTracking.getChanges(_dateTimeYesterday, ctTable);   while select ctTable { info(strFmt("%1", ctTable.KeyField_RecId)); }   CodeAccessPermission::revertAssert(); }

And that’s it! The query returns the list of items that have been updated since the specified date and you can use the results of the query in whatever way you need.