Database Manipulation in Microsoft Dynamics 365 for Operations

Data manipulation in Microsoft Dynamics 365 for Operations (MsDyn365) empowers developers with the tools to insert, update, and delete data elements within a database. This article explores a familiar business decision tool, the Magic 8 Ball^®.  The Magic 8 Ball (made by Mattel) is a mysterious device that provides solutions to all of life’s questions with a simple shake of the ball. Beneath the murky blue netherworld, there are many more choices than expected.

The first operation programmatically inserts the Magic 8 Ball choices for selection into a table. In this section, you have the opportunity to manipulate two fields in a table by adding rows. 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.  In this final area, it is important to select 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.

The setup for data manipulation in Microsoft Dynamics 365 is straightforward: we need a table that we can manipulate. We are creating a table with two fields: one to hold the answer (so we will use Name, as it is a string with 60 characters) and another that will be an integer to easily identify the answer selection.

Create the Table

1. Open Visual Studio as an Administrator and create a package, model, and project to contain your objects. I used CrazyLucky and CrazyLuckyProject for the names.
2. Right-click the project and select Add > New Item.
3. Select Data Model > Table.
4. Enter CrazyLuckyTable for the name and click Add
5. From the Application Explorer, under the Data Types node, expand Extended Data Types.
6. Locate the Name node and drag it to the CrazyLuckyTable
7. Locate the Integer node and drag it to the CrazyLuckyTable
8. Save all and click Build.
9. Ensure that there are no errors. If you have an error, resolve it and rebuild.

The first operation will to be to insert all of the fields. You will notice that we will purposely skip one of the fields so that we can have additional operations for testing. 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.

Insert Statement One

1. Right-click the project and select Add > New Item.
2. Select Code > Runnable Class (Job).
3. Enter InsertOne for the name and then click Add.
4. Enter the code shown below. (This code has been truncated for only the first seven answers due to size.)   
5. Save all and click Build.
6. Ensure that there are no errors. If you have an error resolve and rebuild.
7. Right-click InsertOne and select Set as Startup Object.
8. Click Start on the menu.
9. Once the “done!” message displays, close the browser
10. Click Stop.
11. Right-click CrazyLuckyTable > Open table browser to see the contents.

To continue our insert example, we will add in the missing item from the database, number 6. This works perfectly to insert the record.  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’. We are inserting extra duplicate records at this stage in order to illustrate the selection process for update in the next steps.

Insert Statement Two

1. Right click the project and select Add > New Item.
2. Select Code > Runnable Class (Job).
3. Enter InsertTwo for the name and then click Add.
4. Insert the code shown here.
5. Save all and click Build.
6. Ensure that there are no errors. If you have an error resolve and rebuild.
7. Right-click InsertTwo, and select Set as Startup Object.
8. Click Start on the menu.
9. Once the “done!” message displays, close the browser.
10. Click Stop.
11. Right-click CrazyLuckyTable > Open table browser to see the contents. Note that it will be at the bottom of the list.
12. Run 3-4 times to create more data for operations.

Update

The update of the record takes more logic than a simple insert statement. The key is to match the row that we want to change and then change it without disrupting any other rows. The UpdateOne 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, because you would want to check all rows in the table for possible changes so that you are not left with orphan records. The UpdateTwo 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, which ensures that you are selecting the correct item and isolating it for update.

Update Example One

1. Right-click the project and select Add > New Item.
2. Select Code > Runnable Class (Job).
3. Enter UpdateOne for the name and then click Add.
4. Insert the code shown here. 
5. Save all and click Build.
6. Ensure that there are no errors. If you have an error resolve and rebuild.
7. Right-click UpdateOne and select Set as Startup Object.
8. Click Start on the menu.
9. Once the “done!” message displays, close the browser.
10. Click Stop.
11. Right-click CrazyLuckyTable > Open table browser to see the contents. Note it will be at the bottom of the lis, and only the first record ‘6’ is changed.

Update Example Two

1. Right-click the project and select Add > New Item.
2. Select Code > Runnable Class (Job).
3. Enter UpdateTwo for the name and then click Add.
4. Insert the code shown here.  
5. Save all and click Build.
6. Ensure that there are no errors. If you have an error resolve and rebuild.
7. Right-click UpdateTwo and select Set as Startup Object.
8. Click Start on the menu.
9. Once the “done!” message displays, close the browser.
10. Click Stop.
11. Right-click CrazyLuckyTable > Open table browser to see the contents. Note it will be on the bottom of the list and all records with a ‘6’ are changed.

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”.

Create the Insert statement

1. Right-click the project and select Add > New Item.
2. Select Code > Runnable Class (Job).
3. Enter DeleteNow for the name and click Add.
4. Insert the code shown below  
5. Save all and click Build.
6. Ensure that there are no errors. If you have an error resolve and rebuild.
7. Right-click DeleteNow and select Set as Startup Object.
8. Click Start on the menu.
9. Once the “done!” message displays, close the browser.
10. Click Stop.
11. Right-click CrazyLuckyTable > Open table browser to see the contents. Note it will be on the bottom of the list and all records with a ‘6’ are deleted.

Conclusion

You are now empowered to manipulate data in MysDyn365. Through the use of the Magic 8 Ball, we walked you through the setup so that you can use the insert, update, and delete functions to change data.  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.  In a later article, we will create a class that selects an answer based on a random seed and executes the Magic 8 Ball.

For more information, I recommend that you attend our Dynamics AX or Dynamics 365 training in person or on-demand online to learn more.

Plus keep an eye out for more articles that dive into the technical aspects of using X++ code.  Visit academy.rsmus.com for more information and training materials that will cover this and hundreds of other topics for Microsoft Dynamics AX2012 and Microsoft Dynamics 365.

By Shaun McMikle for RSM

Shaun McMikle