Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / OR condition between ...
Microsoft Dynamics AX (Archived)

OR condition between two ranges in ?AX query

(0) ShareShare
ReportReport
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

  • Mohammad Raziq Ali Profile Picture
    Mohammad Raziq Ali 2,432 on at
    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 Profile Picture
    Bashir Ahmad 5,248 on at
    RE: OR condition between two ranges in ِAX query

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

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    Maqk 844 on at
    RE: OR condition between two ranges in ِAX query

    use query expressions

    www.axaptapedia.com/Expressions_in_query_ranges

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,151 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,963 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans