One of the most exciting features of the X++ language is the select statement. It enables developers to retrieve and manipulate data directly from the database. The retrieved data is stored in table variables, which can then leverage methods to process and work with the data. This approach allows developers to concentrate on implementing functional requirements without spending excessive time on data retrieval and placement.
The Advantages of Using an X++ Select Statement
In many programming languages, preparing data for manipulation requires significantly more effort and code. First, a developer needs to create a class with variables for each field in the table. Next, data is retrieved using a SQL query. Then, additional code is written to map the query results to the corresponding properties in the class object. Only after these steps can the developer work with the data in the high-level language. This process is time-consuming and involves multiple steps.
By contrast, the X++ select statement offers a much simpler and faster alternative. When a developer creates a table in D365 or AX, the system automatically provides a “table variable” or “table buffer” for use in X++ code. This table variable acts as a pre-built class object, with a property for each field in the table. As a result, developers don’t need to manually create a class to handle the data—it’s already available for use.
Moreover, other languages often require one SQL query to fetch data and additional code to populate the table buffer. In X++, the select statement consolidates these tasks into a single step, streamlining the process. Let’s explore an example to see how it works.
Example Select Statement
The simplest way to learn how to use a select statement is by starting with an example. Here’s an example you can check out, which is also available in the Microsoft documentation here.
CustTable custTable;
select * from custTable;
info("AccountNum: " + custTable.AccountNum);To follow along, start by creating a new D365 project and solution in Visual Studio.
Next, add a ‘Runnable Class (Job)’ to the solution. Right-click on the project and choose Add > New Item.
Then, select the Runnable Class (Job) option, give it a name like ‘SelectStatement’, and click Add.
Copy the code into the main method within the code editor window. After pasting the code, your final result should look like this:
In the next three sections, I will break down the code and explain each line in detail.
Define a Table Buffer
In the first line, a table buffer of type CustTable is defined. The table buffer is named custTable. This allows you to work with data from the CustTable in the code.
CustTable custTable;
Basic Select Statement
This line retrieves all the records from the CustTable and loads them into the custTable buffer. The * means “select all fields” from the table. This step populates the custTable with data.
select * from custTable;
This line displays the AccountNum (Customer Account Number) of the first record retrieved from the CustTable. The info() method shows a message in the info log with the AccountNum value. It concatenates the string “AccountNum: ” with the actual value from custTable.AccountNum.
info("AccountNum: " + custTable.AccountNum);Conclusion
This code shows how to fetch data from a table in Dynamics 365 and display it. First, we create a variable to hold the data from the CustTable (customer information). Then, we use the select statement to get all the data from that table. Finally, we display the customer’s account number using the info() method. This makes it easy to retrieve and show data without having to write lots of extra code.