Microsoft Dynamics AX (Archived)

OR condition between two ranges in ?AX query

(0) Share

Report

Posted on by maram fraij

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

I have the same question (0)

All responses (4)

Answers (0)

Sort by

Suggested answer

Maqk 844 on at

Like
a
(0)

Report
Copy link

Link copied!

use query expressions

www.axaptapedia.com/Expressions_in_query_ranges

Was this reply helpful? Yes No
Community Member on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Bashir Ahmad 5,248 on at

Like
a
(0)

Report
Copy link

Link copied!

queryBuildRange.value(strFmt('((ItemType == %1) || (ItemId == "%2"))',

Was this reply helpful? Yes No
Mohammad Raziq Ali 2,488 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No