Empowering Data Manipulation for Microsoft Dynamics AX 2012

By - May 28, 2013

Data manipulation in Microsoft Dynamics AX2012 empowers the developer with the tools to view, insert, update, and delete data elements within a database. This article reviews three active models for data changes. The first operation inserts an item into a table. In this example, you have the opportunity to manipulate two fields in a table by adding a row. The second piece is the update of existing data in the database. You now need to identify the record for change and then update it by changing the contents of one or more fields. Additionally, this section reviews the looping process of the update for multiple records. Lastly, you can delete an item or a list of items from the database. Important to note within this section is the selection of data and data verification before removal. The simple examples in this article are scenarios for data manipulation. If you are manipulating large data frames, always ensure that you have thoroughly tested these processes on smaller data sets in development or testing environments first.

Setup

The setup includes a table and a class. The table is a simple illustration of a list of items that is missing one of the rows.

Common Table

We will be using the following table that has two fields. An answer field, a 40 character string and an answerNumber field, a standard integer.

Empowering Data Manipulation AX2012-pic1

Common Code

There are two common methods that you will create for this example.

Declaration Class

The declaration method declares both the class name and the table buffer that you assign for later use. This could be scoped within the method where our data manipulation statements are kept, however, they can be declared here and used throughout the class.

public class ExaminingUpdate
{
crazyLuckyTable crazyTable;
}

Main Class

The main method instantiates, or creates a working copy of the class for our use. The following is an example.

public static void main(Args args)
{
ExaminingUpdate equ = new examiningUpdate(); 
equ.updatefun();

Insert

Our insert example is a simple process of adding in the missing item from the database, number 6. This works perfectly to insert the record. The issue will arise when you run it again, it will continue to stack in the records.  For this example, run this at least 3-4 times so that we have more than one #6 record with the answer “As I see it, yes”. An important side note is notice the addition of the ttsBegin and the ttsCommit tags before and after the operation. For data manipulation other than viewing a data record, have these transactional blocks set around your changes. This will roll back any changes to the database, maintaining data integrity if something doesn’t work as expected within the block.

public void updatefun()
{
ttsBegin; 
crazyTable.answerNumber = 6;
crazyTable.answer = “As I see it, yes”;
crazyTable.insert();
ttsCommit;
info(“done!”); 
}

Empowering Data Manipulation AX2012-pic2

Update

The update of the record takes more logic than a simple insert statement. The key is to match the one that we want to change and then change it, without disrupting any other rows. As demonstrated below, the Update Example One applies the operation for the very first record that it encounters. This is illustrated by the before looping method and before looping code results table. This is not an optimal solution, as you would want to check all rows in the table for possible changes so that you are not left with orphan or non-updated records. The Update Example Two resolves the issue by looping over the data, changing all possible instances of the matching combination. Also of note, is the change of the select statement with the additional forupdate clause, ensuring that you are selecting the correct item and isolating it for update.

Update Example One – Before Looping

public void updatefun()
ttsBegin; 
select forupdate crazyTable   
where crazyTable.answer == “As I see it, yes”;crazyTable.answer = “As I see it, No”;

crazyTable.update();
ttsCommit;
info(“done!”); 
}

Empowering Data Manipulation AX2012-pic3

Update Example Two – After Looping

public void updatefun()
{
ttsBegin;
while select forupdate crazyTable
   where crazyTable.answer == “As I see it, yes”
{
crazyTable.answer = “As I see it, No”;
crazyTable.update();
}
ttsCommit;
info(“done!”);

Empowering Data Manipulation AX2012-pic4

Delete

The last step of this process is the delete function. You can quickly delete one record, dozens of records, or millions of records. It is very important to test this procedure in a non-production database with a small amount of data to ensure results. The following method with remove all of the items that match “As I see it, No”.

public void updatefun()
{
ttsBegin; 
while select forupdate crazyTable
   where crazyTable.answer == “As I see it, No”
{
crazyTable.delete();
}
ttsCommit;
info(“done!”); 

Conclusion

You are now empowered to manipulate data in Dynamics AX. This article walked you through the setup so that you can use insert, update, and delete functions. Before programming in a test environment, try these simple techniques in a development or sandbox environment with a limited dataset. Once you have mastered the logic and are sure of the results, move up to a test environment with large representative datasets. Only once you are sure of the results and after a full data backup, should you attempt these operations in a production environment as these are powerful tools.

Comments

Comments are closed here.

Receive Posts by Email

Subscribe to the Microsoft Dynamics blog and receive notifications of new posts by email.