Microsoft Dynamics AX (Archived)

OR condition between two ranges in ?AX query

(0) Share

Report

Posted on by maram fraij

11,873

I need to build a lookup query such that I need to add two rages on two different fields with OR logical condition

How can I do this?

I try to use the code below but the output is AND logic not OR

common ret;

Query query;

QueryBuildDataSource QueryBuildDataSource,QueryBuildDataSource2,QueryBuildDataSource3;

QueryBuildRange QueryBuildRange,QueryBuildRange2;

SysReferenceTableLookup sysTableLookup ;

query = new Query();

QueryBuildDataSource = query.addDataSource(tableNum(HcmBenefit));

QueryBuildDataSource2 = QueryBuildDataSource.addDataSource(tableNum(PayrollBenefitDetail));

QueryBuildDataSource2.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource2.relations(true);

QueryBuildRange=QueryBuildDataSource2.addRange(fieldNum(PayrollBenefitDetail, DefaultDeductionBasis));

QueryBuildRange.value(queryValue(PayrollDeductionBasis::FixedAmount));

QueryBuildDataSource3 = QueryBuildDataSource.addDataSource(tableNum(PayrollBenefitDetail));

QueryBuildDataSource3.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource3.relations(true);

QueryBuildRange2=QueryBuildDataSource3.addRange(fieldNum(PayrollBenefitDetail, DefaultContributionBasis));

QueryBuildRange2.value(queryValue(PayrollContributionBasis::FixedAmount));

sysTableLookup = SysReferenceTableLookup::newParameters(tableNum(HcmBenefit), _formReferenceControl, true);

sysTableLookup.addLookupfield(fieldNum(HcmBenefit, Benefitplan));

sysTableLookup.addLookupfield(fieldNum(HcmBenefit, BenefitOption));

sysTableLookup.parmQuery(query);

ret = sysTableLookup.performFormLookup();

return ret;

*This post is locked for comments

All responses (4)

Answers (0)

Mohammad Raziq Ali 2,432 on at

Like (0)

Report

RE: OR condition between two ranges in ِAX query

Hi Maram,

Please follow below link to understand how to work with query and ranges.

msdax.wordpress.com/.../working-with-the-query-ranges

Thanks,

Raziq
Suggested answer

Bashir Ahmad 5,248 on at

Like (0)

Report

RE: OR condition between two ranges in ِAX query

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))',
Community Member Microsoft Employee on at

Like (0)

Report

RE: OR condition between two ranges in ِAX query

Hi maram,

How can you place OR logic when you are passing only one range. Let me explain you for upper half of the code.

1) QueryBuildDataSource = query.addDataSource(tableNum(HcmBenefit));

2) QueryBuildDataSource2 = QueryBuildDataSource.addDataSource(tableNum(PayrollBenefitDetail));

3)QueryBuildDataSource2.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource2.relations(true);

4)QueryBuildRange=QueryBuildDataSource2.addRange(fieldNum(PayrollBenefitDetail, PayrollBenefitDetail));

5)QueryBuildRange.value(queryValue(PayrollDeductionBasis::FixedAmount));

1) First you are adding HcmBenefit table to the query (select * from HcmBenefit).

2) Next you are joining another table.(select * from HcmBenefit join * PayrollBenefitDetail)

3) Now you adding relation since QueryBuildDataSource2.relations(true); is true it will fetch table level relation (select * from HcmBenefit join * PayrollBenefitDetail where PayrollBenefitDetail .recid = HcmBenefit .recid)

4) finally in 4th and 5th steps it will add range and value (select * from HcmBenefit join * PayrollBenefitDetail where PayrollBenefitDetail .recid = HcmBenefit .recid && PayrollBenefitDetail.PayrollBenefitDetail = PayrollDeductionBasis::FixedAmount). Since you are adding only one it will consider it as AND logic . If you want OR logic you need to add multiple ranges for example :

QueryBuildRange=QueryBuildDataSource2.addRange(fieldNum(PayrollBenefitDetail, PayrollBenefitDetail));

QueryBuildRange.value(queryValue(PayrollDeductionBasis::FixedAmount));

QueryBuildRange=QueryBuildDataSource2.addRange(fieldNum(PayrollBenefitDetail, PayrollBenefitDetail));

QueryBuildRange.value(queryValue(PayrollDeductionBasis::Check));

then query will be like this

select * from HcmBenefit join * PayrollBenefitDetail where PayrollBenefitDetail .recid = HcmBenefit .recid AND PayrollBenefitDetail.PayrollBenefitDetail = PayrollDeductionBasis::FixedAmount OR PayrollBenefitDetail.PayrollBenefitDetail = PayrollDeductionBasis::CHECK

hope you got it.
Suggested answer

Maqk 844 on at

Like (0)

Report

RE: OR condition between two ranges in ِAX query

use query expressions

www.axaptapedia.com/Expressions_in_query_ranges