Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

Dynamics Ax 2012 : Exploring the database logging on custom table

by

Dynamics Ax 2012 provide the database level logging. You can track every change not only at table level but also at field of any table.

Dynamics Ax 2012 provide the functionality to log insert update and delete functionality on table as well as selected filed of a table. When tracking on any table in dynamics Ax, a new entry will be created in sysDatabaselog table.

Lets we enable logging on our custom “Student” table.

For this purpose you have to go “System Administrator” Module of Dynamics Ax 2012. Under Step tab you will find database node, there you find “Database log” Click on it. Like this.

dabase log

 

After that Database base logging Wizard will start

Wizard

 

Press Next. On next page select “Not Specified” node of tree. All custom table/ user table will be placed under “Not Specified” Node. Dynamics Ax Default tables are placed under their respected module name.

Node

 

 

Expend the not specified node.  And select the student table there.

 

Student

 

Here you can track not only table but also possible you can track one or more fields of specific table. In current example we enable tracking or logging on whole student table.

 

Studentselection

 

Now question is how we can see what will comes in in sysdatabaselog table on insert update or delete on table.  You can view from this link

 

Log view

 

 

Point to be noted, that all the tables where save per company enables, all log files will be appears in their legal entities. Data is stored in table globally or for all legal entities, you can find their log files under default legal Entity “Dat”.

Now we explore the database structure of SysDatabaselog table. This will help us to get the logged information in X++ code.

Followings are some important fields of SysDatabaseLog table.

  1. Table:  This filed contains the integer value for each table. A unique integer number attached to each table in dynamics Ax. This fileds tells us on which table operation is performed.
  2.     Creation Date: This contains the date and time on which entry in sysDatabaseLog is created.
  3. Data:  X++ container type this contains the data.
  4. LoggType: This described insert, update or delete operation is pefromed at the result current entry is done in sysDatabaselog. It is of enum type. DatabaseLogType::Insert

 

 

For example, if database logging is enabled on our student table, we can track all insert, update or deleted record form database log table. For example if we have to pull all inserted today records form sysDatabaselog we can do by following way.

 

SysDataBaseLog sysDataBaseLog;

utcDateTime CurrentDate;

container tableRecord;

container recordInstance;

str selectedField;

str fieldName;

str fieldValue;

int idx;

int tableID;

str  databaseLogSourceCompany;

CurrentDate=DateTimeUtil::newDateTime(today(),str2time(“00:00:00”));

tableID = tableName2id(‘Student’);

 

while SELECT sysDataBaseLog

WHERE sysDataBaseLog.table ==  tableID && sysDataBaseLog.createdDateTime > CurrentDate && sysDataBaseLog.LogType == DatabaseLogType::Insert

{

selectedField = “RollNumber”;

fieldName=””;

fieldValue = “”;

tableRecord = (sysDataBaseLog.Data);

 

for( idx = 1; idx <= conLen(tableRecord); idx++)

{

recordInstance =  conpeek(tableRecord, idx);

fieldName = conpeek(recordInstance, 1);

 

if(strLwr( fieldName ) == selectedField)

{

fieldValue +=” ” + conpeek(recordInstance, 2) + ” , “;

break;

}

 

}

 

}