X++ Select Statements Dynamics 365 Finance and Operations

D365FO

• D365FO
• X++
• Currency
• Dynamics

In Dynamics 365 Finance and Operations (previously known as Dynamics AX), you can use the X++ programming language to write custom logic and queries. The SELECT statement is commonly used to retrieve data from tables in the application's database. Here's the basic syntax for a SELECT statement in Dynamics 365 Finance and Operations:

A select statement allows developers to fetch and manipulate data from a database. The data is loaded into table variables. Finally, these table variables can have methods where code be added to work with the data. Unlike other programming languages , writing select statements using x++ select statement is a very fast and easy.

In Microsoft dynamics 365 finance and operations , while creating a table, system automatically allows the user to use the table variables or table buffers with a set of table properties including field names. Therefore, there is no need for a developer to manually create a class to work with the data in this table. The system essentially has created one for your use already.

Example :

Here is one simple example which helps you to learn concept more easily. 

internal final class SelectQueryExample
{

   public static void main(Args _args)
   {
      CustTable custTable;
      select * from custTable;
      info("AccountNum: " + custTable.AccountNum);
   }
 
}

Steps for writing An X++ Select Statement

1. Define a Table Buffer

Here in the first line, a table buffer of type ‘CustTable’ is defined, with the name ‘custTable’. Where CustTable is the type of Table and custTable is the name of the table variable. 

2. Basic Select Statement

Once you created a table variable or table buffer, you can easily acces the data from the table by using the select  keyword, this is similier to SQL select query statement. Initially the table buffer will be empty, but after writing the select * from custTable statement, table buffer that should be populated with the data from CustTable.

3. Retrieve required table field

Once table buffer is popualted with the table data, you can fetch required field values by using field names. 

In our example while using the x++ code custTable.AccountNum;, system retrieve the account numbers from the table buffer custTable which was already popluated.

To follow along yourself, create a new D365 project and solution in Visual Studio.

Next, add a ‘Runnable class (Job)’ to the solution. Specifically, right click on the project, and select Add>New Item. Then, select the ‘Runnable Class (Job)’ item. Enter a name, such as ‘SelectStatement’. Finally, click the ‘Add’ button.

Once you created a Runnable Class (Job), copy and paste the above code in between the curl brackets of main method as shown below image.

Similier way , you can access other CustTable fields by usng the filed names.

Conclusion

In Microsoft dynamics 365 using x++ query statements , it is easy to manipulate data of a table, in other programming languages we need more lines of codes to manipulate the table data. This article will be helpful for beginners to understand the concepts of table buffer and x++ query statements especiall select query statements. In the next articles we will read more about select queries by using where clause, Field List, Firstonly etc. Hope this article is helpful. Happy cosing with d365snippets.

Some useful queries

What is the select statement in x++?

What is the difference between select and while select?

How do you write an X++ select statement?

What is the purpose of the select statement in access?