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

[johnjohn123]

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?

[Pstork1]

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.

[johnjohn123]

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

[Pstork1]

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

[johnjohn123]

@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?

[johnjohn123]

@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.

[Pstork1]

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

[johnjohn123]

@Pstork1 so are there any workarounds?

[Pstork1]

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

[johnjohn123]

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

[Pstork1]

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

[johnjohn123]

@Pstork1 no you can apply multiple columns to the sort something as follow:-

sort(sort(sort(outputs('Get_items')?['body/value'],'ID'),'Created'),'Modified')

[Pstork1]

That's not applying multiple columns in a single sort. That is resorting the information multiple times. Its not the same thing.

[johnjohn123]

@Pstork1 so you mean this expression:-

sort(sort(sort(outputs('Get_items')?['body/value'],'ID'),'Created'),'Modified')

 will just sort the items by the Modified field, so it will be the same as saying :-

sort(outputs('Get_items')?['body/value'],'Modified')

?

Thanks

[Pstork1]

So it will sort first by ID, then it will sort that output by Created, and finally sort that output by Modified. The net result will probably be exactly the same as if you sorted by Modified alone.

[johnjohn123]

@Pstork1 So the link @ https://www.powertechtips.com/sort-array-power-automate/ is not 100% correct/valid?

[Pstork1]

No, its accurate.  But ID, Created, and Modified are all unique values. Created and Modified are Dates and Times down to the millisecond so no two values will be the same.  The article is talking about sorting things when you have duplicate values in some of the fields.  That isn't he case here and doesn't really apply anyway since the original question dealt with OData OrderBy not Sort(). They operate differently.

[johnjohn123]

@Pstork1 so what is the options i have to get this working without any threshold error:-

now the filter part will return only 470 items... but seems SharePoint can not Order By multiple fields.. so if i use raw sharepoint api call to get the items sorted will it work?

[Pstork1]

You said if you removed the extra orderBy fields that it worked.  That is the solution.  Remove the extra field and sort it as needed after doing the Get Items.

[johnjohn123]

@Pstork1 what do you mean exactly please? i need to do orderby using all those fields.. and is not the sort and orderby the same?