Sharepoint - CAML query limitation of values in IN operator
It sure looks so from your story, but isn't there a quite easy workaround for this?
<Where>
<Or>
<In>
<FieldRef Name="ID" />
<Values>
<Value Type="Number">1</Value>
<Value Type="Number">2</Value>
.
.
.
<Value Type="Number">499</Value>
</Values>
</In>
<In>
<FieldRef Name="ID" />
<Values>
<Value Type="Number">500</Value>
<Value Type="Number">501</Value>
.
.
.
<Value Type="Number">999</Value>
</Values>
</In>
</Or>
</Where>
and so on, with more nesting if over 1000 and so on.
Can't comment due to low rep so posting as an answer, purists forgive.
Breaking a 500 element In into several nested Or tags does not work, to my despair. The limit is 500 elements hard apparently.
I have tried breaking using a limit of 500 elements, 400, 300, 200, 100, and the SPServices query failed, returning: 500 Internal Server Error with the following responseText:
Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.
Trying to update 500 elements at most (with a single In tag or breaking using nested Or per 400, 300, 200, 100 elements) works. Then the query returns 200 success. To my knowledge there should also be some limit of nested Or tags.
I'm using: SPServices 2014.01, jQuery 1.11, SharePoint 2013:
$().SPServices.SPUpdateMultipleListItems({
listName: "some list display name",
CAMLQuery: CAMLQuery,
valuepairs: [["static name of the field", 1]],
completefunc: function (xData) {
if (xData.status !== 200) {
console.log("failure");
}
}
});
Example query 5 * 100 + 1 that fails since there are 501 elements:
<Query>
<Where>
<Or>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
100 times <Value Type='Lookup'>2688</Value>
</Values>
</In>
<Or>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
100 times value
</Values>
</In>
<Or>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
100 times value
</Values>
</In>
<Or>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
100 times value
</Values>
</In>
<Or>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
100 times value
</Values>
</In>
<In>
<FieldRef Name='ID' LookupId='TRUE' />
<Values>
<Value Type='Lookup'>3089</Value>
</Values>
</In>
</Or>
</Or>
</Or>
</Or>
</Or>
</Where>
</Query>