Comparing AX 2012 hotfix model lists

When Microsoft started releasing hotfixes for the Dynamics AX 2012 R3 release they made our lives a little harder. If you had just one or two hotfixes load it wasn’t too bad but as eventually you find yourself loading a hotfix that has hundreds of dependant hotfixes and suddenly your model store has a list of models a mile long in it. So, when you’re promoting code from your Development system through to Test and Production you need to be sure that you have all the right hotfixes loaded and with a long list of models in each model store, you can easily make a mistake and miss something.

Continue reading

Old cache files may cause AX 2012 Real Time Service to fail

Recently one of my colleagues ran into a strange issue. When performing some actions on the POS that involved calling AX 2012 via Real Time Service, the call failed. In another environment with the same code base, the code succeeded. Also, the RTS failure happened only on certain calls, other calls against the same server worked correctly (the specific case: retrieving a previous sales order succeeded, but sending a completed return order to AX failed).

The event log on the RTS machine was quite confusing: the error message was “Cannot create a record in System parameters (SystemParameters). The record already exists.” But there was nothing wrong with the system parameters.

I captured the request from the POS to the RTS and wrote a small job in AX to call the respective class/method directly using the same request and this worked fine. Even more confusing.

The solution came after realising that the Real Time Service uses .NET Business Connector to connect to the AOS. From the architectural perspective, the Business Connector is like a regular AX client without a user interface. Recently the AX environment in question had been updated with new code – but the same code worked fine on another machine. Earlier I had seen weird issues on a regular AX client where the solution was to get rid of the user cache files. And this turned out to be the solution for the RTS too. Continue reading

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.