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
Category: Query
AX / D365FO – How to add empty ranges in query
I'm setting up a QueryRange and found that if I pass my filter field an empty string the query doesn't run the range. I would have expected all records where the range field is empty to be shown but not… How can I solve this problem? Easy!! Instead of using an empty string I use … Continue reading AX / D365FO – How to add empty ranges in query
AX / D365FO – GroupBy on Lookup problem
I have a custom lookup with a GroupBy inside Query statement First time, when the is nothing choosen the group by works fine. If I choose something from list and again want to change it, now the group by is lost. To solve the issue simply use sysTableLookup.parmUseLookupValue(false) like show in below code public void … Continue reading AX / D365FO – GroupBy on Lookup problem
AX / D365FO – Group by and Aggregate functions on select statement
Thiis simple code shows how to do a Query using a select statement with GROUP BY clause and Aggregate functions like COUNT, SUM, AVG, etc.. CustTable custTable; while select count(RecId), CustGroup from custTable group by CustGroup order by custTable.RecId desc { info(strFmt("%2 - %1", custTable.RecId, custTable.CustGroup)); }
AX / D365FO – Disable Auto execute Query on form startup
Do you have a form that takes a long time to open?This is because when it opens, the ExecuteQuery command is immediately executed, which executes the query on the database. If the query is very slow you will have to wait a long time. To solve the problem you can disable the automatic execution of … Continue reading AX / D365FO – Disable Auto execute Query on form startup
AX / D365FO – “NOT EXISTS” join on an AOT Query
If you need to create a relation between 2 tables using a NOT EXISTS JOIN on an AOT Query you can use the join mode property
AX / D365FO – OR Condition in a Query Range object
Do you want to add an OR Query condition like (Field1 > 0 or Field2 >0) inside a complex AX SQL Statement using a Range object? Follow these steps : For this example I will use InventSum Table Add a range in the Data source Name it PhysicalInvent (no matter what's the name it's just … Continue reading AX / D365FO – OR Condition in a Query Range object
AX / D365FO – FIlter an AOT Query by multiple Enum types fields
Are you creating an AOT Query and want to create a filter condition by multiple ENUM types field? In the AOT query you can do it by using a data source Range object and separate value by comma In the value field property just insert the numeric or string value of the the ENUM fields. In this example I … Continue reading AX / D365FO – FIlter an AOT Query by multiple Enum types fields
AX / D365fo – Filter by Enum value in an AOT Query
Are you creating an AOT Query and want to create a filter condition by an ENUM field? In the AOT query you can do it by using a data source Range object In the value field property just insert the numeric or string value of the the ENUM field. In this example I used the string value … Continue reading AX / D365fo – Filter by Enum value in an AOT Query
AX / D365FO – Use Wildcards (..LIKE SYNTAX..) in AOT Query objects
Are you creating an AOT Query and want to use a wildcard filter? An example could be that you want to filter all the items that starts with a specific word. In an ideal world you would use the SQL keyword "LIKE". In the AOT query you can do it by using a data source … Continue reading AX / D365FO – Use Wildcards (..LIKE SYNTAX..) in AOT Query objects
AX / Dynamics 365 For Finance and Operations blog 