Its quite common for businesses to want to synchronize data between different applications. One method is to periodically do bulk data loads where the entire table is exported from AX and then imported into the other application, this is quite inefficient as usually not much of the data has changed and changes in AX are not available in the other application until the batch job has run.
A better method is to track changes to records (inserts, updates & deletes), then only export what has changed. There are a few different methods of doing this in AX which have different advantages and disadvantages.
For these examples im going to assume that we want to export customer information including the primary address. Changes to Customers (CustTable) and also changes to the primary address of the customer (LogisticsPostalAddress) will need to be tracked.
Using database change tracking (AX2012 R3)
Make sure change tracking is enabled for the database in SQL server. You can configure how long the changes are stored for. The default is 2 days.
Run a job in AX to enable change tracking for the CustTable. The script also creates SQL triggers on the LogisticsPostalAddress table that mark the CustTable record as being modified whenever an address is updated. The job needs to run on the server side. If you are getting permission errors then create a menuItem linking to this Job and set the "RunOn" property to "Server".
When making changes to changeTracking you need to provide a string field called "Scope", if you need to disable change tracking later and delete the triggers then you can call the disableChangeTracking() and deleteTouchTriggers() methods passing in the same scope.
If you already have enabled change tracking on some tables through other methods then it is important to be aware that the AifChangeTrackingConfiguration::enableChangeTrackingForQuery() command will disable change tracking on all tables that have not been enabled through the AIF classes. This includes the tables that have had change tracking enabled for the AX Retail Sync so it looks like a bug from Microsoft. When change tracking is enabled through the AIF class it records the change tracking enabled tables in the AifSqlCdcEnabledTables table. It then calls the SP_ConfigureTablesForChangeTracking stored procedure which synchronizes the change tracking property of all tables with the configuration stored in the AifSqlCdcEnabledTables table. If you want to prevent ChangeTracking from being disabled on tables then comment out the following lines in the SP_ConfigureTablesForChangeTracking stored procedure:
--IF (@CTENABLED = 1 AND @CTREQUIRED = 0)
--BEGIN
-- EXECUTE ('ALTER TABLE ' + @TABLENAME + ' DISABLE CHANGE_TRACKING;');
--END
--ELSE
The below job enables change tracking and creates the touch triggers:
server static void enableChangeTracking(Args _args)
{
Query query;
QueryBuildDataSource qbds;
AifChangeTracking changeTracking;
AifChangeTrackingTable ctTable;
AifChangeTrackingScope scope = "CustTableExport";
;
//Need to be run on server side
// If you are getting a error "Permission failed" then create a menuItem linking to this Job and
// set the "RunOn" property to "Server"
new AifChangeTrackingPermission().assert();
// Build the query, linking from custTable to address. Filters are not important at this stage
query = new Query();
query.name(scope);
qbds = query.addDataSource(tableNum(CustTable));
qbds = qbds.addDataSource(tableNum(DirPartyTable));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(DirPartyLocation));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(LogisticsLocation));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(LogisticsPostalAddress));
qbds.relations(true);
// enable change tracking on CustTable
AifChangeTrackingConfiguration::enableChangeTrackingForQuery(scope, query);
// Create triggers on child tables to mark records on the parent table as touched
AifChangeTrackingConfiguration::createTouchTriggersForQuery(scope, AifChangeTrackingTriggerType::AfterInsert, query);
AifChangeTrackingConfiguration::createTouchTriggersForQuery(scope, AifChangeTrackingTriggerType::AfterUpdate, query);
// Revert the permission assert
CodeAccessPermission::revertAssert();
}
Triggers on address table that mark custTable as being touched
When change tracking is enabled, SQL server will record any inserts, updates or deletes to records in CT enabled tables. Because SQL server is not aware of companies or partitions all changes to the tables are recorded. Each time a record is changed in AX, on the ttscommit SQL server will store the changeOperation, changeVersion and the primary key for that record. When ax retrieves the changed records it will join the changeTracking table to the physical table using the primary key, then it will only return the RecId for records that have changed.
To get a list of recId's that have been touched we need to pass in a from DateTime telling ax how far to look back. You cannot specify a DateTime earlier than when change tracking was enabled, so be careful if you have only just enabled change tracking. For a batch job that exports changes you will need to keep a track of the last export datetime so you can use it as the from datetime during the next run.
The changeTrackingTable will contain recId's for all companies and all partitions so its important to join it back to the source table.
The below job will output customer id's and addresses that have changed.
static void getCustomerChanges(Args _args)
{
CustTable custTable;
LogisticsPostalAddress logisticsPostalAddress;
Query q;
QueryBuildDataSource qbds;
QueryRun qr;
Query queryCustTable;
AifChangeTracking aifChangeTracking;
utcDateTime dateTimeFrom;
AifChangeTrackingTable changeTrackingTable;
;
// Change the date based on what is needed
// Currently -10 mins - you cannot specify a range earlier than when you switched on change tracking
dateTimeFrom = DateTimeUtil::addMinutes(DateTimeUtil::getSystemDateTime(), -10);
new AifChangeTrackingPermission().assert();
queryCustTable = new Query();
queryCustTable.addDataSource(tableNum(CustTable));
aifChangeTracking = AifChangeTracking::construct(queryCustTable);
aifChangeTracking.getChanges(dateTimeFrom, changeTrackingTable);
q = new Query();
qbds = q.addDataSource(tableNum(AifChangeTrackingTable));
qbds = qbds.addDataSource(tableNum(CustTable));
qbds.addLink(fieldNum(AifChangeTrackingTable, KeyField_RecId), fieldNum(CustTable, RecId));
qbds = qbds.addDataSource(tableNum(DirPartyTable));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(DirPartyLocation));
qbds.addRange(fieldNum(DirPartyLocation, IsPrimary)).value(SysQuery::value(NoYes::Yes));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(LogisticsLocation));
qbds.relations(true);
qbds = qbds.addDataSource(tableNum(LogisticsPostalAddress));
qbds.relations(true);
qr = new QueryRun(q);
qr.setCursor(changeTrackingTable);
while(qr.next()){
changeTrackingTable = qr.get(tableNum(AifChangeTrackingTable));
custTable = qr.get(tableNum(CustTable));
logisticsPostalAddress = qr.get(tableNum(LogisticsPostalAddress));
info(strFmt("%1, %2, %3", changeTrackingTable.KeyField_RecId, custTable.AccountNum, logisticsPostalAddress.Address));
}
CodeAccessPermission::revertAssert();
}
Modify some customer information and run the job:
Note that it is also possible to query change tracking using the changeVersion instead of passing in a fromDateTime. The changeVersion is a global number in sql which is incremented every time a record is modified for a change tracked table. Using the Master Data Management class in ax, MdmChangeTracking.getChanges(changeVersion) you can pass in the from changeVersion and all changes since that change version will be returned in a tempDB table, the getCurrentVersion() method will return the changeVersion of the most recent change that was fetched and this can be recorded to be used during the next call to getChanges(). If you pass in a changeVersion of -1 then the entire table will be returned.
Using ModifiedDateTime field (All versions of AX)
Enable the modifiedDateTime property of all the tables you want to track changes to. This is usually already set to enabled for common tables.
Create an index for each table that you will be filtering the ModifiedDateTime by. The index should have the ModifiedDateTime as its first field.
A select query can get you all the records that have changed within a DateTime range.
CustTable custTable;
DirPartyLocation dirPartyLocation;
LogisticsLocation logisticsLocation;
LogisticsPostalAddress logisticsPostalAddress;
FromDateTime fromDateTime = datetimeUtil::addDays(datetimeUtil::getSystemDateTime(), -1);
;
while select custTable
join dirPartyLocation where
dirPartyLocation.Party == custTable.Party &&
dirPartyLocation.IsPrimary == NoYes::Yes
join logisticsPostalAddress where
logisticsPostalAddress.Location == dirPartyLocation.Location &&
(custTable.modifiedDateTime >= fromDateTime ||
dirPartyLocation.modifiedDateTime >= fromDateTime ||
logisticsPostalAddress.modifiedDateTime >= fromDateTime)
{
info(strFmt("%1, %2", custTable.AccountNum, logisticsPostalAddress.Address));
}
This only tracks Inserts and Updates, Deletes will need a different method to track changes.
Using ax table triggers (All versions of AX)
Another approach is to use the table methods to log changes into a queue that can be later exported. Keep in mind that overriding the insert and update methods on tables will cause any set based operations to failover to record by record operations.
First create a new table to keep a track of changed records. The example below shows a generic one that can be used to track changes to any table in the system.
Next, override the Insert, Update and Delete methods for the tables that need to be tracked.
For example the following code is added to the CustTable.Update() method to log changes to the custtable into the queue table.
exportQueue.clear();
exportQueue.Action = AifDocumentOperationType::Update;
exportQueue.RefRecID = this.RecId;
exportQueue.RefTableId = this.TableId;
exportQueue.Identifier = this.AccountNum;
exportQueue.insert();
For child tables that link back to the main table, the method should only insert records linked to the main table as shown int the logisticsPostalAddress.update() method below. The "action" that is updated on queue for the parent table should always be "Updated" if called from the child tables "Insert" or "Delete" methods.
while select dirPartyLocation where
dirPartyLocation.Location == this.Location &&
dirPartyLocation.IsPrimary == NoYes::Yes
join custTable where
custTable.Party == dirPartyLocation.Party
{
exportQueue.clear();
exportQueue.Action = AifDocumentOperationType::Update;
exportQueue.RefRecID = custTable.RecId;
exportQueue.RefTableId = custTable.TableId;
exportQueue.Identifier = custTable.AccountNum;
exportQueue.insert();
}
To get the changed records, join the Queue table back into the query for customer and address, while the records are being read they can also be deleted from the queue.
static void getCustomerChangesTRG(Args _args)
{
SWM_ExportQueue exportQueue;
CustTable custTable;
DirPartyLocation dirPartyLocation;
LogisticsLocation logisticsLocation;
LogisticsPostalAddress logisticsPostalAddress;
FromDateTime fromDateTime = datetimeUtil::addDays(datetimeUtil::getSystemDateTime(), -1);
;
ttsbegin;
while select forUpdate exportQueue
join custTable where
custTable.RecId == exportQueue.RefRecID &&
custTable.TableId == exportQueue.TableId
join dirPartyLocation where
dirPartyLocation.Party == custTable.Party &&
dirPartyLocation.IsPrimary == NoYes::Yes
join logisticsPostalAddress where
logisticsPostalAddress.Location == dirPartyLocation.Location
{
info(strFmt("%1, %2", custTable.AccountNum, logisticsPostalAddress.Address));
exportQueue.delete();
}
// Additional query to export any records that have been deleted:
while select forUpdate exportQueue where
exportQueue.TableId == tableNum(custTable) &&
exportQueue.Action == AifDocumentOperationType::Delete
{
info(strFmt("%1, %2", exportQueue.Identifier, "Deleted"));
exportQueue.delete();
}
ttscommit;
}
Conclusion
There are a few other methods that can be used that I have not mentioned here. The final question though is which method is best? I prefer the ModifiedDateTime tracking method because it requires the least amount of changes to the system.
