How To Insert Data In D365

Of the of the most important things to know how to do in Microsoft Dynamics 365 for Finance and Operations is to work with data. In this article you will learn how to insert data in D365 using x++. There are several different ways to insert data. Therefore, understanding when to use each one is very important. Additionally, learn how each way of inserting data works.

Where To Put The X++ Code

Before beginning, we need to know where to put the code to insert data in D365. The code can be put in a lot of different places. First, forms with data sources automatically call the code needed to insert and update data. So while this is the most common way of inserting data, in this article I want to explain how to write x++ code that will insert data in D365.

Another common example, is to put code in the ‘clicked’ method of button. Additionally, code can be added to methods on classes, and that code can be called from anywhere. Including batch jobs. Anywhere x++ code can be written we can write code to insert data.

For the purposes of this article, we are going to create a ‘Runnable class (job)’ so that we can experiment with writing this code.

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 InsertData
{
    /// <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.
    }

}

Insert Method

Table Buffer

In the most common example of how to insert data in D365, you need to insert 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 InventItemGroup. This table stores item group information.

InventItemGroup inventItemGroup;

Therefore, I type the table name ‘InventItemGroup’. Then, I type a variable name. In this case I entered ‘inventItemGroup’. Note, to follow best practices, variables should start with a lower case letter.

Populate Data – Unique Index

After declaring the table buffer variable, the next step is to populate the table buffer variable with data.

To start, I should check the ‘indexes’ on the table. And find 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 records should be set to something.

In the case of the InventItemGroup table, I can see there exists an index named ‘ItemGroupIdx’. And in that index there is a field named ‘ItemGroupId’. This tells me I need to set this field. And it needs to be a unique value.

In this example, set the ItemGroupId field to be whatever you would like. I set mine to “Puzzles”. The code should look like this.

class InsertData
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        InventItemGroup inventItemGroup;
        inventItemGroup.ItemGroupId = "Puzzles";
    }

}

Populate Data – Mandatory Fields.

In addition to the fields that are part of the unique index on a table, there may be other fields that are ‘mandatory’ to be set. If you do not set these fields, you will get an error if you try to insert the record. To check for mandatory fields, select each field on the table, and look at the properties window. Locate the Mandatory property, and see if the value is set to Yes.

If the value is set to Yes, then you must also set those fields to a non blank value. In this example the only field on the InventItemGroup table whose Mandatory property is set to ‘Yes’, is the ItemGroupId. And we have already set this field. So we do not ‘need’ to set any other fields.

Calling the insert method

Finally, now that we have a table buffer variable that is declared and populated with data, we can call the ‘insert’ method. The ‘insert’ method is a method that exists on every table buffer automatically. For those of you who like to think a little deeper, the method is on the ‘base’ class that used to define every table buffer.

The core underlying code that is in the insert method cannot actually be us. However, we know what it does. The system uses the table buffer variable and the values we have set. It generates and runs a SQL statement that inserts a record into the corresponding table with those values.

Usually, in other coding languages you would have to write this sql statement yourself. However, in x++, this code is already handled for the developer, which is really great! We just have to focus on setting the values, and then calling the insert() method.

See the updated example:

class InsertData
{
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {
        InventItemGroup inventItemGroup;
        inventItemGroup.ItemGroupId = "Puzzles";
        inventItemGroup.insert();
    }

}

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 InventItemGroup table in Visual Studio and select ‘Open table browser’.

As expected, I can see that my record was inserted into the table.

Are Transaction Blocks Needed?

Although this worked, if you are an experienced developer you might be wondering why I did not include a ttsbegin and ttscommit around my code. The quick answer is that for an insert statement, they are not actually needed. They are however required for update and delete statements. Otherwise the record will not be updated or deleted.

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++. They are often also used around insert statements. See the Microsoft documentation here. Here is an example.

    Custtable custTable;
 
    ttsBegin;
     
    select forUpdate custTable where custTable.AccountNum == '4000';
    custTable.NameAlias = custTable.Name;
    custTable.update();
     
    ttsCommit;

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.

Fields Set Automatically

You may notice, there are a few fields on the table that are set automatically for us by the system. This is really nice. And saves us time every time we write code to insert data in D365.

• RecId – This is a field that is unique across the entire table, and I believe also the entire database. In this way, you always have a unique identifier, on every table. The system generates the unique value, and sets it on the table, and table buffer when the record is inserted. We can tell if a table buffer has been inserted, by checking the value of the RecId, and seeing if it is not zero.
• DataAreaId – Behind the scenes the DataAreaId stores the legal entity, or company code. This is a value that is up to 4 characters long. And depending on which company you are currently logged into, this will determine which vales goes into this field. This effectively allows you to have one database, but multiple segmented systems of data within it. There are some tables that do not have a DataAreaId field. The data in these tables are shared across all companies.
• Partition – Partition is not something you really have to understand anymore. The same value will be inserted into every record. It was designed to allow multiple D365 systems to exist on the same server. But I believe now every system has their own server.
• CreatedDateTime – When this property is set to Yes on the table, the system will set this field to the current date time when the record was inserted.
• CreatedBy – When this property is set to Yes on the table, the system will set this field to the current user logged in when the record was inserted.

Microsoft Documentation

Microsoft has some really great documentation around how to insert data into D365. I recommend you take a look for further information.

DoInsert Method

Similarly, the DoInsert 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 Insert() method.

Calling DoInsert() will cause the system to not call any code you have written in the overridden ‘insert’ method of the table. Additionally, the system will skip any event handler or chain of command methods related to calling ‘insert’ on the table. Consequently, it is usually a bad practice to call DoInsert(). This method should only be used when a developer very specifically needs to skip any logic written in the insert method.

        InventItemGroup inventItemGroup;
        inventItemGroup.ItemGroupId = "Puzzles";
        inventItemGroup.doInsert();

Insert_RecordSet

The insert_recordset keyword in x++ is used when you need to copy multiple records from existing tables to a destination table. 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 insert_recordset in D365 in more detail.

InsertDatabase

The insertDatabase method on a RecordInsertList and RecordStortedList object is another way of inserting multiple records at the same time. And using a single trip to the database.

Each table buffer is essentially added to a ‘list’ of records that is stored in the RecordInsertList variable. Then, finally insertDatabase is called. The system will take all of the records you have put in this list and insert them into the database at that time.

One option is to write a while loop. And inside the loop, populate a table buffer, then call the insert method. This however is sends data to the database for every time through the loop. Using insertDatabase sends all this information after it is collected. And makes fewer trips to the database. While the resulting inserted data in the same, the performance of this approach is much faster.

I will write an additional article explaining how to use insertDatabase in D365 in more detail.

Conclusion

As shown above, it is very easy to insert data in D365 using X++. We simply populate a table buffer, ensuring we at least set the mandatory fields. Then call the ‘insert()’ method on the table buffer. In many other programming languages a developer would need to create a SQL connection string. And then generate a SQL insert 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 insert 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.