AX / D365FO – Using “Not Like” in X++ select statements

If you want to use wild-cards in Ax, you can write a SQL statement with a LIKE keyword SalesTable salestable; select firstonly salestable where salestable.salesId like '123*'; But if you want to use NOT LIKE you can do in 3 different ways : SalesTable salestable; select firstonly salestable where !(salestable.salesId like '123*'); SalesTable salestable, salestableExists; … Continue reading AX / D365FO – Using “Not Like” in X++ select statements

AX / D365FO – Conditional ‘Where’ Clauses in ‘Select’ Statements

Ever found yourself in a situation where you conditionally want to apply a 'where' clause in a select statement in Dynamics AX? Here's how you can do it: There are several scenarios we run into everyday where we are writing a select statement to query data but we only want to apply the 'where' clause … Continue reading AX / D365FO – Conditional ‘Where’ Clauses in ‘Select’ Statements

AX – D365FO – Using Like in select statement in X++

In SQL it is common to use like when querying data.  For example, the following SQL statement queries the CustTable table for all customers that begin with ’12’ select * from CustTable where AccountNum like '12%' It is also possible to use like in X++ syntax.  The following X++ statement queries the CustTable table for … Continue reading AX – D365FO – Using Like in select statement in X++

AX – D365FO – Select TOP n rows in select statement – X++

You can only retrieve 1, 10, 100, 1000 records from a select statement using firstonly syntax (like shown below) while select firstonly100 rent order by PostedDate desc where (rent.PostedDate > td && rent.RentalOrderStatus == z_RentalOrderStatus::Posted) || rent.RentalOrderStatus == z_RentalOrderStatus::Approved { info("orders"); } If you want to get different number of rows you can implement a … Continue reading AX – D365FO – Select TOP n rows in select statement – X++

AX – D365FO – Cross-Company in Select statement in X++ and AOT

You can create a cross-company query by using X++ code. There are two ways to do this: crossCompany keyword on the select statementQuery class methods A cross-company query can also be created under the Query node in the Application Object Tree (AOT). A cross-company query cannot return data for companies that the user does not … Continue reading AX – D365FO – Cross-Company in Select statement in X++ and AOT