Loading...

X++ While Select Statement in Dynamics 365 Finance and Operations

X++ While Select Statement in Dynamics 365 Finance and Operations

In Dynamics 365 Finance and Operations, the ‘select’ statement is commonly used to retrieve data from tables in the application's database. 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.

Read More : X++ Select Statements

Read More  : X++ Select Statement with Where Clause

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.

Difference between While select and Select Statement

In the previous article we already read that a select statement can be used or only can return one record and store the results in the table buffer. But in many situations, we need to retrieve many records on a single select statement, in these cases we need to use a while statement, a ‘while select’ statements are used to find one or more records.

Here you can see one simple select query example which select the first record of VendTable and store into the table buffer vendTable.

internal final class SelectQueryExample

{ 

   public static void main(Args _args)

   {

       VendTable vendTable;

       select * from vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

       select vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

}

}

Above both select queries retrieve one record as shown in the below screenshot.

X++ While Select Statement in Dynamics 365 Finance and Operations

X++ Next Keyword

We have a requirement to retrieve more than one vendor account number, Microsoft introduced a new keyword called ‘Next ‘Keyword. Which we can use multiple records to retrieve from a table buffer,

Syntax of Next keyword,

You can see the below snippet example to understand the use of the Next keyword, 

internal final class SelectQueryExample

{ 

   public static void main(Args _args)

   {

       VendTable vendTable;

       select * from vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

       select vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

       next vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

       next vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

       next vendTable;

       info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

}

}

Here we need to write the separate lines of codes to retrieve the multiple number of vendor Account Number from a single table buffer. So, if we need to retrieve ‘n’ number of Vendor Account Number, we need to write ‘n’ number of lines by using next keyword.

But this is not practical, if we have thousands of records, we are not able to write thousands of lines of codes with next keyword. 

In these cases, we are using while statement.

While Select Statement

In Dynamics 365 Finance and Operations, the "while select" statement is commonly used in X++ programming language to iterate through records in a table or a view and execute a block of code if a specified condition is true. This statement is often used for data manipulation and processing.
Like a select statement, x++ code is written to specify what records should be retrieved from the database. However, a while select statement should be used when the number of records being retrieved.

So, you can rewrite the code like this,

internal final class SelectQueryExample

{ 

   public static void main(Args _args)

   {

       VendTable vendTable;

       while select vendTable;

{

        info(strFmt("Vendor Account Num = %1", vendTable.AccountNum));

        }

}

}

In this example you can see, we have declared one table buffer vendTable, In the next line, the system starts by looking at the query part of the select statement. Here before the select statement we have written the keyword ‘while’.

In this case: ‘while select vendTable. This tells the system to retrieve every record in the table named VendTable. Next, at the start of the curly brace, the system will take all the values in the first record retrieved and load them into the vendTable table buffer variable. After that, the ‘info(strFmt("Vendor Account Number = %1", vendTable.AccountNum));’ line of code will print out the sales order number to the screen. Finally, the system will see the ending curly brace, and jump back to the starting curly brace.

While select statement with where clause

In Dynamics 365 Finance and Operations, the "while select" statement is commonly used in X++ programming language to iterate through records in a table or a view and execute a block of code if a specified condition is true. This statement is often used for data manipulation and processing.

You can use a where clause with while statement , which will retrieve the records only if a specified condition is true. If the specific condition is false, system will not retrieve any records. While select statement with where clause is generally used to retrieve multiple records which are satisfying a specific condition.

internal final class SelectQueryExample

{ 

   public static void main(Args _args)

   {    

while select AccountNum, Name, Balance from CustTable

    where CustTable.Balance > 1000

{

    info("Account Number: " + CustTable.AccountNum);   

info("Customer Name: " + CustTable.Name);   

info("Balance: " + CustTable.Balance);

}

}

}

In this example:

  • while select: Initiates the loop.
  • AccountNum, Name, Balance: Specifies the fields to be selected from the "CustTable."
  • from CustTable: Specifies the table from which records are selected.
  • where CustTable.Balance > 1000: Sets the condition that only records with a balance greater than 1000 will be selected.
  • { //code to be executed for each selected record }: The block of code inside the curly braces will be executed for each record that meets the specified condition.This example selects records from the "CustTable" where the balance is greater than 1000 and displays the account number, customer name, and balance for each selected record.Using the "while select" statement with a "where" clause allows you to process a subset of records based on specific conditions, providing flexibility in handling data in Dynamics 365 Finance and Operations.

Conclusion

After reading this article, i expect you can get the basic idea about 'x++ select statement' and 'x++ while select statement', for writng efficient codes to retrive many records in a single statement with a condition or without a condition , you can use while select statement. if this article is useful, try to share your friends and social media platform. Happy coding with d365snippets.

Published on:

Learn more
D365 Snippets
D365 Snippets

Share post:

Related posts

Create a data maintenance strategy for Dynamics 365 finance and operations data (part two)

Welcome to part two of our series on creating a data maintenance strategy for Dynamics 365 finance and operations data. In this installment, w...

27 days ago

Create a data maintenance strategy for Dynamics 365 finance and operations data (part one)

In this two-part series, you'll learn how to create an effective data maintenance strategy for your Dynamics 365 finance and operations data. ...

28 days ago

Performance testing using JMeter in D365 Finance and operations.

To use JMeter for load testing Dynamics 365 Finance, you’ll want to set up your test plan to simulate the expected user behavior and loa...

1 month ago

Introduction to Copilot in Microsoft Dynamics 365 Finance and Operations: DCAI002

Amidst the buzz and discussions surrounding Copilot and AI capabilities, I felt compelled to delve into their functionalities specifically fro...

2 months ago

Customer and vendor balances netting for Dynamics 365 Finance and operations.

Netting customer and vendor balances in Dynamics 365 Finance and Operations (D365 F&O) involves consolidating the amounts owed to and from...

2 months ago

Inventory value report in Microsoft Dynamics 365 Finance and Operations: Part-9

Welcome to the next part in the Inventory Valuation Series within Microsoft Dynamics 365 Finance and Operations. After exploring multiple valu...

2 months ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy