Get Items from SharePoint is raising this error :- "The attempted operation is prohibited because it exceeds the list view threshold."

(0) Share

Report

Posted on by johnjohn123

3,506

I have this Get All Items where i define the threshold to be 100,00 as follow:-

now on runtime >> i will get this error , any advice how to fix this error?

The attempted operation is prohibited because it exceeds the list view threshold.

any advice please?

Categories:

Building flows

I have the same question (0)

All responses (23)

Answers (0)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

Set your threshold to something slightly smaller and specify a top count in the Get Items. When pagination is enabled Top count becomes the page size that is retrieved. Threshold becomes the total number of items that will be retrieved. SharePoint has an administrative limit that kicks in at 100,000. This is normally experienced because if your list gets that large you can't Break security inheritance. But its also the limit on admin batch retrieval of records in on-premises SharePoint. I assume its a carry over from that. In the past the workaround for lists this large was to use indexed columns to retrieve a smaller set of content. MS never supported retrieval of over 100,000 records at a time. SharePoint can store in the millions, but can't retrieve all of them at once.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1 but i am saying that i want 100,000 items only .. which should work for me.. not sure why this is not working?

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

1) Make sure that the fields you are filtering on are Indexed.

2) I still don't think you'll be able to get the full 100,000. I would decrease the Threshold until you get it to work and then keep increasing the threshold til it stops working. It may work at 99,999 but fail at 100,000

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1 I already have all the columns defined in the filter and in the order inside the list index. and i used top count =100 and threshold to 99999 but still i am getting the exact same error. i think sharepoint api has a bug is thaat if we define 2 filters and the first filter return more than 5000 the whole query will fail... not sure if this is the reason.. is there any workaround?

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1 now i removed the Order by fields and the Get Item worked well .. not sure why..

Also i can define one Order By field such as "DesignTypeOrder asc" but if i define 2 Order By fields i will get the threshold error again.

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

You can't supply more than one Orderby field in the get items.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1 so are there any workarounds?

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

To the best of my knowledge that is a limitation of the way that OData OrderBy queries work.

Was this reply helpful? Yes No
johnjohn123 3,506 on at

Like (0)

Report

@Pstork1 can not i for example build an array of the GetItems results without any Ordering then apply the order for the array?

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

Yes, but the Sort() function only supports sorting by one column in the array.

Was this reply helpful? Yes No