x++ code to define or conditions in querybuildrange for different fields

Finance and Operations/AX Developer Forum
1

Hello,

I need OR condition for different fields in query range.

here is my code.

I have tried to keep different query range object but it is taking AND condition

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

qbr_ContractYr3 = qbds_contractYr.addRange(fieldNum(uerContractYear, EndingDate));
qbr_ContractYr3.value(QueryValue(sysquery::range(fromDt, toDt)));

Even though i tried to keep same query range object it is taking AND condition.

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, EndingDate));
qbr_ContractYr2.value(QueryValue(sysquery::range(fromDt, toDt)));

How will achieve OR condition for different fields in this case please suggest

2

You can use expressions,
msdn.microsoft.com/…/aa893981.aspx

3

The only difference between your two snippets is whether you’re reusing the same variable or not. There is no difference at all in functionality; you’re creating identical ranges in both cases.

If you want to combine two ranges for different fields, you’ll have to use expression in query ranges (which are ugly, but sometimes necessary).

4

Can you please tell me how will i achieve this…actually, i saw this site but i didn’t understand how to use it in my case

5

Can you please tell me how will i achieve this…actually, i saw this site but i didn’t understand how to use it in my case

6

Have you tired it? If so please show us your code.

7

First i am trying with the 1st field…below code i tried

qbr_ContractYr2 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr2.value(strfmt(’((%1.%2 >= %3 && %1.%2 <= %4))’,qbds_contractYr.name(),fieldStr(uerContractYear, BeginningDate),fromDt,toDt));

Its giving me runtime error

Query extended range failure: Right parenthesis expected near pos 75.

8

use this example,

query = new query();
qbds = query.addDataSource(tableNum(CustTrans));

qr = qbds.addRange(fieldNum(CustTrans, TransDate));
qr.value(
strFmt(’(((%1.%2 >= %4) && (%1.%2 <= %5)) || ((%1.%3 >= %4) && (%1.%3 <= %5)))’,
qbds.name(),
fieldStr(CustTrans, TransDate),
fieldStr(CustTrans, DocumentDate),
fromDate,
toDate));

9

When i debugged the code i got this query…it should give me 2 records in lookup…but unfortunately, it is giving me blank look up…i am not understanding now what is the issue in the expression.

SELECT FIRSTFAST * FROM uerContractYear WHERE uerOutboundGuaranteePaymentTable.uerContractYear = uerContractYear.ContractYear AND ((uerOutboundContractId = N’10126’)) AND (((((uerContractYear_1.BeginningDate >= 1/1/2011) && (uerContractYear_1.BeginningDate <= 12/31/2011)) || ((uerContractYear_1.EndingDate >= 1/1/2011) && (uerContractYear_1.EndingDate <= 12/31/2011)))))

10

Can you show us your query?

11

sysTableLookup = SysTableLookup::newParameters(tableNum(uerOutboundGuaranteePaymentTable), _formControl);
sysTableLookup.addLookupField(fieldNum(uerOutboundGuaranteePaymentTable, Description));

queryBuildDataSource = query.addDataSource(tableNum(uerOutboundGuaranteePaymentTable));

queryBuildRange = queryBuildDataSource.addRange(fieldNum(uerOutboundGuaranteePaymentTable, uerOutboundContractId));
queryBuildRange.value(INXL_ContractMarketingBudgetDetailTable.uerOutboundContractID);

queryBuildRange = queryBuildDataSource.addRange(fieldNum(uerOutboundGuaranteePaymentTable, uerAdvanceorGuarantee));
queryBuildRange.value(queryValue(uerAdvanceorGuarantee::Guarantee));

qbds_contractYr = queryBuildDataSource.addDataSource(tablenum(uerContractYear));
qbds_contractYr.addLink(fieldNum(uerOutboundGuaranteePaymentTable, uerContractYear), fieldNum(uerContractYear, ContractYear));
qbds_contractYr.joinMode(JoinMode::InnerJoin);

qbr_ContractYr1 = qbds_contractYr.addRange(fieldNum(uerContractYear, uerOutboundContractId));
qbr_ContractYr1.value(INXL_ContractMarketingBudgetDetailTable.uerOutboundContractID);

fromDt = mkdate(1, 1, str2int(INXL_ContractMarketingBudgetDetailTable.Year));
toDt = mkdate(31, 12, str2int(INXL_ContractMarketingBudgetDetailTable.Year));

qbr_ContractYr1 = qbds_contractYr.addRange(fieldNum(uerContractYear, BeginningDate));
qbr_ContractYr1.value(strfmt(’(((%1.%2 >= %4) && (%1.%2 <= %5)) || ((%1.%3 >= %4) && (%1.%3 <= %5)))’,qbds_contractYr.name(),fieldStr(uerContractYear, BeginningDate),fieldStr(uerContractYear, EndingDate),fromDt,toDt));

sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

12

I don’t see any issue with the expression. Please try by investigating the query results (running it separately in a job)

13

Or maybe you’re designing wrong query. Please simplify your query to contain just the problematic fields, so we don’t have to deal with unrelated code (designing a new table just for this testing would be a good idea), tell us which records you’re expecting to get and that parameters are you using.
If you provide a project that anybody can import and run, you’ll have a good chance that somebody will do it, and it will make sure that we all talk about the same thing.