cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
andreapasutti
Nintex Newbie

CAML Query for 2 Values of a Multi-Select Field

Jump to solution

Hi.

I have a CAML query where I want to look at an approver list and find the ID based on a set of criteria. The query looks at the NextNumber being greater than the current number, it looks for a match on the specific BizUnit and Area OR for a BizUnit or Area of "ALL". Both BizUnit and Area are multi-select lookup columns. Finally, and this is where my query errors, it looks for either the specific RequestType OR the RequestType of "ALL". The difference is that the RequestType is a multi-select choice column.

I have bolded and colored the part of the query that is failing. From what I have read, I don't want to use "Contains" and that it should work with "Includes".

<Query>

  <Lists>

    <List ID="{INTERNAL LIST ID}" />

  </Lists>

  <ViewFields>

    <FieldRef Name="ID" />

  </ViewFields>

  <Where>

    <And>

      <And>

        <And>

          <And>

            <Or>

              <Eq>

               <FieldRef Name="BizUnit"  />

                <Value Type="Lookup">ALL</Value>

              </Eq>

              <Eq>

                <FieldRef Name="BizUnit"  />

              <Value Type="Lookup">{ItemProperty:BizUnit}</Value>

              </Eq>

            </Or>

            <Or>

              <Eq>

                <FieldRef Name="Area"  />

                <Value Type="Lookup">ALL</Value>

              </Eq>

             <Eq>

                <FieldRef Name="Area"  />

                <Value Type="Lookup">{ItemProperty:Area}</Value>

              </Eq>

            </Or>

          </And>

            <Or>

          <In>

            <FieldRef Name="RequestType"  />

            <Values>

            <Value Type="MultiChoice">ALL</Value>

              <Value Type="MultiChoice">{WorkflowVariable:RequestType_text}</Value>

            </Values>

          </In>

            </Or>

            </And>

            <Gt>

              <FieldRef Name="NextNumber"  />

              <Value Type="Number">{WorkflowVariable:NextNumber_text}</Value>

            </Gt>

          </And>

          <Eq>

            <FieldRef Name="Active"  />

            <Value Type="Choice">Active</Value>

          </Eq>

        </And>

      </Where>

      <OrderBy>

        <FieldRef Name="NextNumber" Ascending="true" />

      </OrderBy>

    </Query>

Why doesn't this work? Any help would be appreciated.

0 Kudos
Reply
7 Replies
jackgelo
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

Hi,

have you tried to remove the OR condition just outside the IN operator in the red bolded text? In my opinion you don't need it and it asks for two condition in it, while you have just one - the IN operator)

Giacomo

Reply
andreapasutti
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

Yes, I have tried it both ways:

          </And>

          <In>

            <FieldRef Name="RequestType" />

            <Values>

              <Value Type="MultiChoice">ALL</Value>

              <Value Type="MultiChoice">{WorkflowVariable:RequestType_text}</Value>

            </Values>

          </In>

        </And>

This query "works" in that it doesn't throw an error, but it only returns the value with the RequestType = "ALL". I need it to either return "ALL" when no criteria is selected or else the value when the specific criteria is met.

0 Kudos
Reply
jackgelo
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

I've just tried this:

<Or>
   <Eq>
      <FieldRef Name="RequestType" />
      <Value Type="MultiChoice">ALL</Value>
   </Eq>
   <Eq>
      <FieldRef Name="RequestType" />
      <Value Type="MultiChoice">{WorkflowVariable:RequestType_text}</Value>
   </Eq>

</Or>

and it returns to me all elements where I've selected ALL or the value I have in my variable (if I've selected also other options in addition to mine, the elements are returned as well)

0 Kudos
Reply
andreapasutti
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

I have tried that configuration and just tried it again and get this error message:

The execution returned an unexpected error.
Cannot complete this action.

Please try again.

My CAML right out of the query:

            </Or>

            <Or>

              <Eq>

            <FieldRef Name="RequestType" />

              <Value Type="MultiChoice">ALL</Value>

              </Eq>

              <Eq>

            <FieldRef Name="RequestType" />

              <Value Type="MultiChoice">{WorkflowVariable:RequestType_text}</Value>

              </Eq>

            </Or>

        </And>

When I delete the CAML code in orange and run it, I get a result. I can see how this code is supposed to work, but it's not working. I just confirmed that the other 2 fields are multi-select lookup fields (BizUnit and Area) and RequestType is multi-select choice. It should work. What am I missing?

0 Kudos
Reply
jackgelo
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

Hi,

is it correct that the field name is different in the two Eq condition?

0 Kudos
Reply
andreapasutti
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

No, I'm sorry. That was a typo. I'm looking for the ID based on 2 values in one field "RequestType". Either "ALL" or the specified value that the submitter has made.

I've tried Includes and Contains and although the code is configured correctly and doesn't error, in all scenarios it only returns the ID for ALL.

I'm stumped. This should work.

0 Kudos
Reply
andreapasutti
Nintex Newbie

Re: CAML Query for 2 Values of a Multi-Select Field

Jump to solution

I got tired of trying to figure it out, so I cheated and changed the column type to Lookup because I know that code works.

0 Kudos
Reply