CAML "NOT IN" query

Question

Is there a way to do something like "NOT IN" behavior in SharePoint 2010? I can easily implement IN behavior like that:

<Where>
   <In>
      <FieldRef Name="ID"/>
      <Values>
         <Value Type="Counter">1</Value>
         <Value Type="Counter">2</Value>
         <Value Type="Counter">3</Value>
         <Value Type="Counter">4</Value>
         <Value Type="Counter">5</Value>
      </Values>
   </In>
</Where>

But is there a way to select all the values that DOES NOT IN Values enumeration?

Here is the USE CASE: I have a Lookup field with AllowMultipleValues = true, and I need to get all items from LookupList, which are not included into Lookup field

Thanks in advance!

score 2 · Answer 1 · answered Jan 02 '14 at 14:32

Starting from SharePoint 2010, there's the NotIncludes element that might work for you. From MSDN:

If the specified field is a Lookup field that allows multiple values, specifies that the Value element is excluded from the list item for the field that is specified by the FieldRef element.

Template:

<NotIncludes>
    <FieldRef Name="Field_Name" />
    <Value Type="Field_Type" />
    <XML />
</NotIncludes>

More reading: NotIncludes Element

score 1 · Answer 2 · answered Apr 30 '15 at 16:44

To get the opposite behavior of 'In', you have to make a nested 'Neq' query. 'NotIncludes' could be substituted for or combined with 'Neq' if you are dealing with a Lookup Field with multiple values.

<Query>
    <Where>
        <And>
            <And>
                <Neq>
                    <FieldRef Name="ID" /><Value Type="Counter">5</Value>
                </Neq>
                <Neq>
                    <FieldRef Name="ID" /><Value Type="Counter">13</Value>
                </Neq>
            </And>
            <And>
                <NotIncludes>
                    <FieldRef Name="children" /><Value Type="Lookup">20</Value>
                </NotIncludes>
                <NotIncludes>
                    <FieldRef Name="children" /><Value Type="Lookup">32</Value>
                </NotIncludes>
            </And>
        </And>
    </Where>
</Query>

If you want more variables then more nesting needs to be done. Have fun.

score 0 · Answer 3 · edited Jun 26 '18 at 11:34

0

I think the Not equal option would be the best way to build this CAML query

Maybe this (didn't test this, so bear with me)

<Query>
   <Where>
     <And>
       <Neq>
          <FieldRef Name="ID" /><Value Type="Counter">1</Value>
       </Neq>
       <Neq>
          <FieldRef Name="ID" /><Value Type="Counter">2</Value>
       </Neq>
       <Neq>
          <FieldRef Name="ID" /><Value Type="Counter">3</Value>
       </Neq>
     </And>
   </Where>
</Query>

You should have a look at the available Comparison Operators

Contains
BeginsWith
Eq, Equal
Neq, Not equal
Gt, Greater than
Lt, Less than
Geq, Greater than or equal to
Leq, Less than or equal to
DateRangesOverlap, Compare dates in recurring event with specified value
IsNotNull
IsNull

edited Jun 26 '18 at 11:34

Jonathan Hall

75,165
16
143
189

answered Jul 20 '12 at 20:02

Eric Herlitz

25,354
27
113
157

4

I don't think that CAML is valid as is - you can't have more than 2 children under AND IIRC. – Ryan Jul 21 '12 at 12:28
Alright, I'll perform some tests and come back with the result when done! – Eric Herlitz Jul 21 '12 at 12:34
Thank you all for the answer, but in case of using operators query becomes a little ugly when the count of possible variants more than 10 (for ex.), by the way, I've solved this problem using logic for now, but I think that it would be great if it will be possible to implement logic somehow... – debug Jul 22 '12 at 21:09

score 0 · Answer 4 · answered Dec 03 '13 at 12:09

The closest I've come is to use <NotIncludes></NotIncludes>, but for some reason is does not work the same way as <In></In>. In <In> I can use <Values>. With <NotIncludes> it looks like you can only specify one value. The rest will have to be <Or>ed in, similar to <Neq>.

CAML "NOT IN" query

4 Answers4