Conditional Joins in x++

In this post, we will try to learn how we can apply conditional joins in x++ to cater the complex scenarios in the development of Microsoft Dynamics AX.

Conditional joins in Dynamics AX (x++)
Here is the code to apply joins conditionally in Microsoft Dynamics AX.
query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable), "InventTable");
dsInventItemBarCode = dsInventTable.addDataSource(tableNum(InventItemBarCode), "InventItemBarCode");
dsInventItemBarCode.joinMode(JoinMode::ExistsJoin);

// Add our two ranges
queryBuildRange1 = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
queryBuildRange2 = dsInventItemBarCode.addRange(fieldNum(InventItemBarCode, DataAreaId));
 


No we can play with our query  
Find all records where either the ItemType is Service, or the ItemType is Item and a barcode exists. The join criteria is only applied in the second half of the expression, so all Service items will appear irrespective of whether they have a bar code. Again, this is not possible to achieve using the standard query ranges.

queryBuildRange2.value(strFmt('((%1.%2 == %3) || ((%1.%2 == %4) && (%1.%5 == %6)))',
query.dataSourceTable(tableNum(InventTable)).name(), // InventTable %1
fieldStr(InventTable, ItemType), // ItemType %2
any2int(ItemType::Service), // %3
any2int(ItemType::Item), // %4
fieldStr(InventTable, ItemId), // ItemId %5
fieldStr(InventItemBarCode, ItemId))); // %6

Complex Query Ranges in Dynamics AX

In this article, we will learn how to apply simple and complex ranges in Dynamics AX queries.

We will play with a query having a single datasource in it. Following is the code for adding a query with a datasource.

query = new Query();
dsInventTable = query.addDataSource(tableNum(InventTable));
// Add our range
queryBuildRange = dsInventTable.addRange(fieldNum(InventTable, DataAreaId));
 
Simple criteria

Lets find the record where the value of ItemId field is B-R14. Take note of the single quotes and parenthesis surrounding the entire expression.
queryBuildRange.value(strFmt('(ItemId == "%1")', queryValue("B-R14")));

Find records where the ItemType is Service. Note the use of any2int().
queryBuildRange.value(strFmt('(ItemType == %1)', any2int(ItemType::Service)));
 
Find records where the ItemType is Service or the ItemId is B-R14. Note the nesting of the parenthesis in this example.
queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))', any2int(ItemType::Service), queryValue("B-R14")));
 
Find records where the modified date is after 1st January 2000. Note the use of Date2StrXpp() to format the date correctly.
queryBuildRange.value(strFmt('(ModifiedDate > %1)', Date2StrXpp(01012000)));

Complex criteria with combined AND and OR clauses
We need to find those records where the ItemType is Service, or both the ItemType is Item and the ProjCategoryId is Spares. This is not possible to achieve using the standard range syntax.
Note also that in this example, we are using the fieldStr() method to specify our actual field names and again, that we have nested our parenthesis for each sub-expression.

queryBuildRange.value(strFmt('((%1 == %2) || ((%1 == %3) && (%4 == "%5")))',
fieldStr(InventTable, ItemType),
any2int(ItemType::Service),
any2int(ItemType::Item),
fieldStr(InventTable, ProjCategoryId),
queryValue("Spares")));

Extended Query Range in Dynamics AX

Many developers often stuck while they try to apply range to the dynamics ax query to filter records based on some conditions. We will try to explore the query ranges in this article and will try to play with some examples to learn how we can apply query ranges using x++ to the Dynamics AX queries.

Let's say we want to apply "OR" range on a SAME field then we have 2 ways to do it.

Method 1:


Query q;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('4005, 4006');

The above x++ code will generate following SQL statement.
"SELECT * FROM CustTable WHERE ((AccountNum = N'4005' OR AccountNum = N'4006'))"

Method 1:

qbr.value(strFmt('((AccountNum == "%1")
(AccountNum == "%2"))',
QueryValue('4005'),
QueryValue('4006')));

The above x++ code will generate following SQL statement.
"SELECT * FROM CustTable WHERE ((((AccountNum == "4005") || (AccountNum == "4006"))))"

Let's say we want to apply "OR" range on a DIFFERENT fields


You can use the following x++ code to apply the OR range to the different fields

qbr = qbd.addRange(FieldNum(CustTable, DataAreaId));
qbr.value(strFmt('((%1 == "4000")
(%2 == "The Bulb"))',
fieldStr(CustTable, AccountNum),
fieldStr(CustTable, Name)));

The above code will generate following sql statement
"SELECT * FROM CustTable WHERE ((((AccountNum == "4000") || (Name == "The Bulb"))))"

Note: We have used DataAreaId field above to apply the range however, the actual range is on AccountName and AccountNum field. This means when you use range value expressions you can use any field to obtain range object and use it to insert your range in the query. Using DataAreaId field for this purpose is the best practice.