After users enter in data in Microsoft Dynamics 365 for Finance and Operations you often need to process the data. In this article you will learn how to update data in D365 using x++. There are several different ways to update data. And there are several important things you need to know in order to successfully update data through code.
Data Update On Forms
The start, the most common place to update data in D365 is on a form. When a control on a form is tied to a Data Source and a Data Field, the system will automatically update the underlying table when the value is changed.
This is incredibly useful. And requires no code. Specifically, a developer simply needs to add the Data Source to form, and set the two properties. Then, once a value is changed, the system will automatically update the underlying field when the Save button is pushed. Or, when the user selects a different record on a grid.
That said, while this is the most common way of updating data, in this article I want to explain how to write x++ code that will update data in D365.
Example Use Cases
Often times, code is put the ‘clicked’ method of button. Then, when the button is pushed, the record is processed, and the status field on the record needs to be updated to a new value.
Other times, a user will change the value on a form, and code is added to the modified method to update a field on a related table that is not a Data Source on the current form.
Additionally, code can be added to batch jobs that will create and update many different records. Anywhere x++ code can be written we can write code to update data.
Runnable Class (Job)
For the purposes of this article, we are going to create a ‘Runnable class (job)’. Next, we will experiment with writing code to update data in D365.
First, open Visual Studio and Create a new solution and project. Secondly, right click on the project, and select Add>New Item. Thirdly, select Dynamics 365 Items, on the left, and then select ‘Runnable Class (Job)‘ from the list. Fourthly, enter in a name. Finally, click the ‘Add‘ button to the add the job to your project.
A code editor window will open and show the following code. We will enter our code inside curly brackets of the main method.
class UpdateDataJob
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
//our code will go here.
}
}Update Method
Table Buffer
Most often, when you need to update data in D365, you need to update one record. To start, declare a table buffer variable. A table buffer variable is declared in x++ by writing the name of the table you are working with, followed by a variable name. It is usually a best practice to name your variable the same as the name of your table, unless that variable is already used.
In this example, I want to work with the table CustTable. This table stores customer information.
CustTable custTable;Therefore, I type the table name ‘CustTable’. Then, I type a variable name. In this case I entered ‘custTable’. Note, to follow best practices, variables should start with a lower case letter.
Select A Record
After declaring the table buffer variable, the next step is to select an existing record to modify. Then, store the data into the table buffer variable. This should be done using a select statement or preferably a Find method.
Find methods are very much preferred compared to writing a new select statement. But that is larger topic that I will cover in a separate article. For the sake of getting to call the update method, let’s start with the select statement.
Use A Unique Index
To start, in order to know what fields to filter on to get a single record, we need to check the ‘indexes’ on the table.
For example, let us look at the Find method that exists on the CustTable. In Visual Studio, go to the Application Explorer. Then, in the top bar, type CustTable, and hit enter.
Located the node ‘CustTable’ under AOT>Data Model>Tables>CustTable. Right click on it, and select ‘Open designer‘.
Next, in the window that opens, expand the Indexes node. Right click any node, and select Properties.
Continue to select indexes until you locate the node where the ‘Allow duplicates‘ property is ‘No‘. This index is a ‘unique’ index. This means that the fields in this index must together contain a unique combination of values on every record. And therefore, these fields can be used to find a single record.
In this case, I see that the Index named ‘AccountIdx‘ has the Allow Duplicates property set to ‘No‘ on the CustTable.
Additionally, there exists one field in this index, named AccountNum. Therefore, in order to select a single record I need to filter on the field named AccountNum.
Select Statement
Now that we know a unique index to use, go back to the Runnable Class (Job). Then, enter the following code.
class UpdateDataJob
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
CustTable custTable;
select firstonly custTable
where custTable.AccountNum == "1001";
}
}Above, the code is loading all of the columns of the table CustTable, into a table buffer named ‘custTable’ where the accountNum field is equal to 1001.
ForUpdate
Currently, the system is just reading the data into the table buffer. In order for the system to be able to update data in D365, we need to use the keyword ‘forupdate‘ in our select statement. This keyword will tell the SQL sever database to not allow any other process to update this record until after we are finished.
If other processes were allowed to also update the data while our process was working with it, we might inadvertently set values back to a value before another process intentionally set them. So each process needs to take their turn reading the values, then only updating what they intend to.
Add the forupdate keyword after before the table buffer variable in the select statement. It should now look like this.
CustTable custTable;
select firstonly forupdate custTable
where custTable.AccountNum == "1001";To clarify, the forupdate keyword is required for both updating a record and deleting the record. It is not required for inserting a record. Importantly, if you forget to add this keyword, the system will throw an error when you try to call update.
Calling The Update Method
Without delay, let us actually update data in D365. Add the following code to the job.
select firstonly forupdate custTable
where custTable.AccountNum == "1001";
if (custTable)
{
ttsbegin;
custTable.CustGroup = "80";
custTable.update();
ttscommit;
}In this case, I set the CustGroup field to the value “80”. The current value in my database is “20”.
As can be seen, I called the update() method. First, type the name of the table buffer variable. Secondly, type a period (.). Thirdly, type the method name update. Finally, since the update method does not take any parameters, type an open close parenthesis ().
TTSBegin and TTSCommit
What is TTSBegin and TTSCommit? These are keywords in the x++ language. The TTSBegin keyword should be added before beginning any update or delete code in x++. And the TTSCommit keyword, should be put at the end of any update or delete code in x++. See the Microsoft documentation here. Without these two keywords around your update statement, the system will not save the data. The system will not throw an error, but the record will not get updated in the database.
Balanced TTSBegin and TTSCommit Blocks
There is a lot more I can say about these keywords. But in short, the system only commits the changes to the database when the last ttscommit statement is run. You can nest multiple sets of ttsbegin and ttscommit statements. However, any data changes made inside of them will not take affect until the last ttscommit is run.
As a rule, you need to have balanced ttsbegin and ttscommit statements. This means you need to write your code in a way that the same number of ttsbegin statements run as ttscommit statements. The most common cause of code breaking this rule is when there is a conditional statement that is around either a ttsbegin or ttscommit statement, and not both. I will share more when I explain how to update data in D365.
Run The Job
To demonstrate, run the job to update data in D365. To run this code, right click on the Runnable class (job) object in your project and select ‘Set as Startup Object’. This tells Visual Studio to run this code when the ‘Start’ button is pushed.
Finally, click ‘Start’ in Visual Studio.
In this case the job will run and nothing will be displayed to the screen. But we can use either the table browser or SQL Server Management Studio to check if the record was inserted or not. I can show you that next. Click ‘Stop‘ in Visual Studio after the process has finished running.
Table Browser
There are three ways of running the D365 table browser. Please see my article on how to use the D365 table browser. In this case, I will just right click on the CustTable table in Visual Studio and select ‘Open table browser’.
As expected, I can see that my record was updated in the table.
Also, go to the ‘all customers’ form in D365. You can see that the record is updated there as well.
Microsoft Documentation
Microsoft has some documentation around how to update data in D365. You can see it here.
DoUpdate Method
Similarly, the DoUpdate method exists on every table buffer variable, because it is part of the base class. Equally, it is used for inserting a single record. Nevertheless, it is different than the Update() method.
Calling DoUpdate() will cause the system to not call any code you have written in the overridden ‘update’ method of the table. Additionally, the system will skip any event handler or chain of command methods related to calling ‘update’ on the table. Consequently, it is usually a bad practice to call DoUpdate(). This method should only be used when a developer very specifically needs to skip any logic written in the insert method.
CustTable custTable;
custTable.CustGroup= "80";
custTable.doUpdate();Update_RecordSet
The update_recordset keyword in x++ is used when you need to updates values on many records. It is extremely fast, because it generates a single SQL statement that is sent to the server one time.
I will write a separate article explaining how to use update_recordset in D365 in more detail.
Conclusion
As shown above, it is very easy to update data in D365 using X++. First, we simply select a table buffer with data from an existing record. Ensure that you use the forupdate keyword. Then, call the ‘update()’ method on the table buffer. As a reminder, make sure you have a ttsbegin and ttscommit around the call to update() otherwise it will not work.
In many other programming languages a developer would need to create a SQL connection string. And then generate a SQL update statement for the data and table they are working with. However, in x++ the management of the SQL connection to the database is handled behind the scenes. Likewise, the SQL statement to update the data is similarly generated and run behind the scenes. This allows the developer to stay focused on the data and the business process they are trying to accomplish.
