Search code examples
acumatica

How can I add more than two ORs to a Where2 clause in a PXSelect?


I have a selector where I'm using the Where2 operator. due to the fact that I needed two nested WHEREs, ORed together - as follows... I've tried adding it and I cannot get it to work. I'm thinking that the Where2 only allows two nested OR conditions:

Where2<  Where<  xvwProjects.usrContractEntityType, Equal<Constants.investor>,
         And<    xvwProjects.usrBranchID, Equal<Current<GLTran.branchID>>,
         And<    AssistantController.xTACOpenSourceDetail.accountLookup, Equal<Current<GLTran.accountID>>>>>,
Or<      Where<  xvwProjects.usrContractEntityType, Equal<Constants.investment>,
         And<    AssistantController.xTACOpenSourceDetail.accountLookup, Equal<Current<GLTran.accountID>>>>>>,...

The above works fine. My question is, how can I add another OR group to this as follows:

Where2<  Where<  xvwProjects.usrContractEntityType, Equal<Constants.investor>,
         And<    xvwProjects.usrBranchID, Equal<Current<GLTran.branchID>>,
         And<    AssistantController.xTACOpenSourceDetail.accountLookup, Equal<Current<GLTran.accountID>>>>>,
Or<      Where<  xvwProjects.usrContractEntityType, Equal<Constants.investment>,
         And<    AssistantController.xTACOpenSourceDetail.accountLookup,Equal<Current<GLTran.accountID>>>>>>, 
Or<      Where<xvwProjects.nonProject, Equal<True>>>,

Or, if that can't be done, which Search do I use (I'm currently using Search5 because I need a join, a where, and an aggregate)? Is this something than can be done in BQL? What would the format be?


Solution

  • I may have the syntax a little off in the following, but you just nest Where2's.

    Where2<  Where<  xvwProjects.usrContractEntityType, Equal<Constants.investor>,
             And<    xvwProjects.usrBranchID, Equal<Current<GLTran.branchID>>,
             And<    AssistantController.xTACOpenSourceDetail.accountLookup, Equal<Current<GLTran.accountID>>>>>,
    Or<
            Where2<
                 Where<  xvwProjects.usrContractEntityType, Equal<Constants.investment>,
                 And<    AssistantController.xTACOpenSourceDetail.accountLookup,Equal<Current<GLTran.accountID>>>>>>, 
            Or<      Where<xvwProjects.nonProject, Equal<True>>>
            >,
    

    Since an Or condition means any of them will return true, you basically are saying "Where<A or >". The Where basically sets the grouping in SQL, so you are saying Where2<Where A, or Where2<Where B, or Where2<Where C, or...