RSMUS.comData 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.
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!”); }
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!”); }
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!”); }
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.
