SharePoint REST API with CAML Query for more than 5000 items
I need to filter a list which has more than 5000 items using SharePoint REST API
One of the columns to filter is a managed metadata column which prevents me from using SharePoint REST API GET Urls
I have to use REST API POST requests with CAML Query in the body to achieve this.
My code looks like below
var viewXml =
{
ViewXml: "<View>" +
"<Query>" +
"<Where><Eq>" +
"<FieldRef Name='RegionTestHidden'/>" +
"<Value Type='TaxonomyFieldType'>" + "North" + "</Value>" +
"</Eq></Where>" +
"</Query>" +
"</View>"
}
function CamlQueryRESTCall(listName, viewXml)
{
var call = jQuery.ajax(
{
url: _spPageContextInfo.webAbsoluteUrl+ "/_api/Web/Lists/getByTitle('"+listName+"')/GetItems(query=@v1)?" +
"@v1=" + JSON.stringify(viewXml),
type: "POST",
dataType: "json",
headers:
{
Accept: "application/json;odata=verbose",
"X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
}
}
);
return call;
}
The problem I am encountering is if i filter based on region South which has more than 5000 items as result, I am getting HTTP Error 500 Internal Server Error with error message List item threshold exceeded.
With the GET requests I can use $top and data.d.__next to load more than 5000 items. But how to do similar logic in POST requests?
I tried including <RowLimit>1000</RowLimit> in the CAML Query but still same error
The columns used in query are indexed btw.
sharepoint-online caml sharepoint-jsom sharepoint-rest-api
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
add a comment |
I need to filter a list which has more than 5000 items using SharePoint REST API
One of the columns to filter is a managed metadata column which prevents me from using SharePoint REST API GET Urls
I have to use REST API POST requests with CAML Query in the body to achieve this.
My code looks like below
var viewXml =
{
ViewXml: "<View>" +
"<Query>" +
"<Where><Eq>" +
"<FieldRef Name='RegionTestHidden'/>" +
"<Value Type='TaxonomyFieldType'>" + "North" + "</Value>" +
"</Eq></Where>" +
"</Query>" +
"</View>"
}
function CamlQueryRESTCall(listName, viewXml)
{
var call = jQuery.ajax(
{
url: _spPageContextInfo.webAbsoluteUrl+ "/_api/Web/Lists/getByTitle('"+listName+"')/GetItems(query=@v1)?" +
"@v1=" + JSON.stringify(viewXml),
type: "POST",
dataType: "json",
headers:
{
Accept: "application/json;odata=verbose",
"X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
}
}
);
return call;
}
The problem I am encountering is if i filter based on region South which has more than 5000 items as result, I am getting HTTP Error 500 Internal Server Error with error message List item threshold exceeded.
With the GET requests I can use $top and data.d.__next to load more than 5000 items. But how to do similar logic in POST requests?
I tried including <RowLimit>1000</RowLimit> in the CAML Query but still same error
The columns used in query are indexed btw.
sharepoint-online caml sharepoint-jsom sharepoint-rest-api
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07
add a comment |
I need to filter a list which has more than 5000 items using SharePoint REST API
One of the columns to filter is a managed metadata column which prevents me from using SharePoint REST API GET Urls
I have to use REST API POST requests with CAML Query in the body to achieve this.
My code looks like below
var viewXml =
{
ViewXml: "<View>" +
"<Query>" +
"<Where><Eq>" +
"<FieldRef Name='RegionTestHidden'/>" +
"<Value Type='TaxonomyFieldType'>" + "North" + "</Value>" +
"</Eq></Where>" +
"</Query>" +
"</View>"
}
function CamlQueryRESTCall(listName, viewXml)
{
var call = jQuery.ajax(
{
url: _spPageContextInfo.webAbsoluteUrl+ "/_api/Web/Lists/getByTitle('"+listName+"')/GetItems(query=@v1)?" +
"@v1=" + JSON.stringify(viewXml),
type: "POST",
dataType: "json",
headers:
{
Accept: "application/json;odata=verbose",
"X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
}
}
);
return call;
}
The problem I am encountering is if i filter based on region South which has more than 5000 items as result, I am getting HTTP Error 500 Internal Server Error with error message List item threshold exceeded.
With the GET requests I can use $top and data.d.__next to load more than 5000 items. But how to do similar logic in POST requests?
I tried including <RowLimit>1000</RowLimit> in the CAML Query but still same error
The columns used in query are indexed btw.
sharepoint-online caml sharepoint-jsom sharepoint-rest-api
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
I need to filter a list which has more than 5000 items using SharePoint REST API
One of the columns to filter is a managed metadata column which prevents me from using SharePoint REST API GET Urls
I have to use REST API POST requests with CAML Query in the body to achieve this.
My code looks like below
var viewXml =
{
ViewXml: "<View>" +
"<Query>" +
"<Where><Eq>" +
"<FieldRef Name='RegionTestHidden'/>" +
"<Value Type='TaxonomyFieldType'>" + "North" + "</Value>" +
"</Eq></Where>" +
"</Query>" +
"</View>"
}
function CamlQueryRESTCall(listName, viewXml)
{
var call = jQuery.ajax(
{
url: _spPageContextInfo.webAbsoluteUrl+ "/_api/Web/Lists/getByTitle('"+listName+"')/GetItems(query=@v1)?" +
"@v1=" + JSON.stringify(viewXml),
type: "POST",
dataType: "json",
headers:
{
Accept: "application/json;odata=verbose",
"X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
}
}
);
return call;
}
The problem I am encountering is if i filter based on region South which has more than 5000 items as result, I am getting HTTP Error 500 Internal Server Error with error message List item threshold exceeded.
With the GET requests I can use $top and data.d.__next to load more than 5000 items. But how to do similar logic in POST requests?
I tried including <RowLimit>1000</RowLimit> in the CAML Query but still same error
The columns used in query are indexed btw.
sharepoint-online caml sharepoint-jsom sharepoint-rest-api
sharepoint-online caml sharepoint-jsom sharepoint-rest-api
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
asked Nov 21 '18 at 12:43
Abdul Hameed
695619
695619
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07
add a comment |
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07
add a comment |
1 Answer
1
active
oldest
votes
This is due to the List view threshold that SharePoint has. See here
It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:
- Go to your SharePoint Farm's Central Administration
- Go to the "Manage Web Applications" under the "Application Management" section
- Select the "Web Application" where you migrate the site
- Click the "General Settings" dropdown and select "Resources Throttling"
- Change the "List View Threshold" to 12000 or something like that.
answered Nov 21 '18 at 17:29
Size_J
1722715
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412306%2fsharepoint-rest-api-with-caml-query-for-more-than-5000-items%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
This is due to the List view threshold that SharePoint has. See here
It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:
- Go to your SharePoint Farm's Central Administration
- Go to the "Manage Web Applications" under the "Application Management" section
- Select the "Web Application" where you migrate the site
- Click the "General Settings" dropdown and select "Resources Throttling"
- Change the "List View Threshold" to 12000 or something like that.
answered Nov 21 '18 at 17:29
Size_J
1722715
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
add a comment |
This is due to the List view threshold that SharePoint has. See here
It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:
- Go to your SharePoint Farm's Central Administration
- Go to the "Manage Web Applications" under the "Application Management" section
- Select the "Web Application" where you migrate the site
- Click the "General Settings" dropdown and select "Resources Throttling"
- Change the "List View Threshold" to 12000 or something like that.
answered Nov 21 '18 at 17:29
Size_J
1722715
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
add a comment |
This is due to the List view threshold that SharePoint has. See here
It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:
- Go to your SharePoint Farm's Central Administration
- Go to the "Manage Web Applications" under the "Application Management" section
- Select the "Web Application" where you migrate the site
- Click the "General Settings" dropdown and select "Resources Throttling"
- Change the "List View Threshold" to 12000 or something like that.
answered Nov 21 '18 at 17:29
Size_J
1722715
This is due to the List view threshold that SharePoint has. See here
It is a threshold and you can change that to fit your needs. This is done in Central Admin like this:
- Go to your SharePoint Farm's Central Administration
- Go to the "Manage Web Applications" under the "Application Management" section
- Select the "Web Application" where you migrate the site
- Click the "General Settings" dropdown and select "Resources Throttling"
- Change the "List View Threshold" to 12000 or something like that.
answered Nov 21 '18 at 17:29
Size_J
1722715
answered Nov 21 '18 at 17:29
Size_J
1722715
answered Nov 21 '18 at 17:29
Size_J
1722715
answered Nov 21 '18 at 17:29
Size_J
1722715
1722715
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
add a comment |
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
Not feasible for me as i dont have tenant admin access for the sharepoint online account. I cant change the threshold limit
– Abdul Hameed
Nov 22 '18 at 4:38
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412306%2fsharepoint-rest-api-with-caml-query-for-more-than-5000-items%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I've encountered with this problem, what you need to do is to create an index to a column. The problem of this is that if you have your list with more than 20.000 items you cannot create the index. Take a look to this post support.office.com/en-us/article/…
– Brank Victoria
Nov 21 '18 at 13:01
@brank like i mentioned in the last line of the question, the columns used in the query are indexed already
– Abdul Hameed
Nov 22 '18 at 4:37
Hi, have you tried to use the search api instead of GetItems?
– Brank Victoria
Nov 22 '18 at 8:34
Search api doesnt accept filtering on managed metadata type columns
– Abdul Hameed
Nov 22 '18 at 9:07