Dynamics Ax 2012

My Presentation on Dynamics AX 2012 Development Introduction.

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.

Inside database logging required table will find with the help of label of function. For example if  we want to enable logging on table name Customer then we can find customer in table with label “Customer”. You can find the label of table by exploring the property of by selecting table.

Let’s 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.

 

After that Database base logging Wizard will start

 

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.

 

 

 

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

 

 

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.

 

 

 

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

 

 

 

All those table where “Save per company “ option enable, all log files will be stored in sysDatabaselog with respect to their legal entities where the insert update or delete occurs. All those tables where stored data gobally in “Dat” legal entities.  Logging records will be found in “Dat” company.

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.

 Insert Case:

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);

 

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

break;

 

 

}

 

}

 

Update case will return three containers inside Data field of sysDataTable for each record, first one describe the field Name, second container returns the new value and third container will return old values. like

 

while SELECT sysDataBaseLog

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

{

selectedField = “RollNumber”;

fieldName=””;

fieldValue = “”;

tableRecord = (sysDataBaseLog.Data);

 

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

{

// fields name in container.

recordInstance =  conpeek(tableRecord, idx);

fieldName = conpeek(recordInstance, 1);

 

// new values in container in same sequence as first container describe.

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

// Old set of value in same sequence.

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

}

Delete case:

Like Insert Delete case: you can find 2 containers first one contains fields name and second one contains the delete value in same sequence. But very important you can get recId of delete record as follow.

 

info(int642str(sysDataBaseLog.LogRecId)); complete code will be look like

 

 

 

while SELECT sysDataBaseLog

 

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

 

{

 

 

 

fieldName=””;

 

fieldValue = “”;

 

tableRecord = (sysDataBaseLog.Data);

info(int642str(sysDataBaseLog.RecId));

info(int642str(sysDataBaseLog.LogRecId));

 

 

 

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

 

{

 

recordInstance =  conpeek(tableRecord, idx);

 

fieldName = conpeek(recordInstance, 1);

filevalue=conpeek(recordInstance, 2);

 

info (fieldName + ” Value : ” + fieldValue);

 

 

 

//}

 

 

 

}

 

 

 

 

 

 

 

 

In Dynamics Ax there is structure known as containers. Here are some points for Containers

• They are value based structure. So no class based object stored on it.
• They are similar to linked list or array like structured.
• Their size fixed at time of declaration. When you insert update or delete any value from container, a new container is created.
• They always copied by value.
• Containers are base 1 not like array based zero.

There is question arises why we use temporary tables when have containers . These containers which can stored almost very values data type value.  While there are lot of functions available for containers. Answer is that on temporary temple we can set indexes on fields and by this way we can fetch data much faster. Although data stored in container is sequentially but on insertion  a new copy is generated which is  performance over head. When data is increases in container, container starts to be heavy.  Similar when you passed temporary table to any method it passed by reference. But when we passed container to method, a new copy is generated and used in method. Container will used only when fewer values will be manipulated. In the case of larger set of data, we have to use temporary tables.

 

Declaration of containers.

container  firstContainer;

container  secondContainer = [3,"Ali","RAza"];

 

Read value from container.

The value from container can be read with conpeek function,  this method take two parameter, first one is container  and second parameter is for getting index. The conPeek function read value of any type so and read for any type.

 


_value  = conPeek(secondContainer,3);
 

 

Insert value:

 

There are two functions used for insert a value in container, conPoke and  conIns

 

ConIns

 


secondContainer=conIns(secondContainer,2,"Zaidi");

 

 

conPoke:

 

secondContainer=conpoke(secondContainer,2,”test");

 

There difference between conPoke and conIns is that conIns, insert a new value at location, and rest of value is shifted to one next Index. conPoke replace the value at insert location for example if

 

container  secondContainer = [3,”Ali”,”RAza”];
then conIns secondContainer=conIns(secondContainer,2,”Zaidi”);  will create new conainter like as

[3,”Zaidi”,”Ali”,”Raza”];

While conPok

secondContainer=conpoke(secondContainer,2,”test”);

will create a [3,”Zaidi”,”Ali”,”Raza”];

 

[3,”test”,”RAza”];

 

 

 

Removing the value from Container:

 

 

Condel function is used to remove the value from container.

secondContainer =conDel(secondContainer,2,1);

conNull function is used to clear all the value from container as

secondContainer=conNull() ;

 

Length of container is calculated as

int _lenght= conLen(secondContainer);

confind:

 

This method find the index the value which required to search, if value is not found zero will be return

 

_found =conFind(secondContainer,"RAza");

info(int2str(_found));

_found =conFind(secondContainer,"Abc");

 

 

 

Some important Global class functions for Container

 

 

Con2Str:

This method return container values as comma sparated string.

_value = con2Str(secondContainer);

 

Con2List:

This function converts the container into list.

List _myList=con2List(secondContainer);

 

 

Loop through container:

 

 

Usually we have to loop through the container.

 

 

_lenght = conLen(secondContainer);

for (counter =1; counter <=_lenght; count++)

{

_value=conPeek(secondContainer,counter);

info(_value);

}