How to update data in D365 F&O

The most usual thing that a developer has to do is to work with data. In our previous blog, we talked about How to insert data in D365 F&O. In this article, we will show some ways to update data in D365 F&O. Based on my experience I think that the best way to learn is through examples. So I will show you how to update data in D365 F&O using some test cases as examples.

Update single record in D365 F&O

First test case: We need to update Target License Plate with a random value on WHSWorkTable where WorkId has a constant value ex: WORKIDTEST001.

Solution: Before writing code, we need to understand which way we will choose to update data in this case. Since we have to update only one record that we can find with the P.K index (WorkId) we can use the find method of WHSWorkTable to select the record and then update data using the update or doUpdate method. If we don’t want to validate data during the update we must call the doUpdate method. For purpose of this article we will use a Job/Runnable Class to write and execute the code.

If we dive into the find() method we will see that inside this method it’s a select that returns a worktable buffer with the first record where WorkId is equal with the first parameter that we have passed on the find method. We can update this buffer only if the second parameter passed in the find method is true. This structure is almost the same in every find method. Usually, for every index, a find method exists in the standard code. If the find method doesn’t exists we can write a find method for this index or we can write the select statement in our method. For creating a find method I hope I will write an article in the future. Below is the screenshot with the second way of updating data, using select statements.

If we take a look at the select statement I have used the forupdate keyword (to allow updating the record) and firstonly keyword (to select only the first record that we find). Usually, we use a select statement without firstonly keyword when we should update more than one record.

Update multiple records in D365F&O

Second test case: We need to update Target License Plate with a empty value on WHSWorkTable only for workTable with status Cancelled.

Solution 1: In this case, we need to write a while select statement that will get all work table records with status canceled and update Target License Plate with an empty value.

This solution will work but it’s not the best solution because it’s not the most performant solution. To build a faster/performant solution we need to use update_recordset.

Update multiple records using Update_RecordSet

Update_recordset enables you to update multiple rows in a single trip to the server. This will make this the most performant solution when we update multiple records.

When we are using x++ SQL statements as update_recordset, insert_recordset, and delete_from it’s not necessary to use the ttsbegin and ttscommit keywords.

TTSBegin and TTSCommit

What is TTSBegin and TTSCommit? These are some keywords in the X++ language that we use when we insert, update, or delete data. The TTSBegin keyword should be added beginning any insert, update or delete transactions in X++. And the TTSCommit keyword, should be put at the end of any insert, update or delete transactions in X++. Without these two keywords in your update statement, the system will not throw an error but the data will not be updated in the database.