Sharepoint lists over 5,000 items and OData filter restrictions

[efialttes]

Hi

I have a Sharepoint List with more than 7,000 items.

I know there is currently a limit on 'SPGet items' action block, no more than 5,000 items can be obtained in the reply.

So, I tried to use OData filter to grab a single item from my SP list, by asking for its UniqueID0 (a text column). UniqueID0 is as its name says unique, i mean two items from my List cannot share the same value.

The result of searching UniqueIDs that exist on my list is that, sometimes the target item is found sometimes not.

So I suspect 5,000 limit applies before ODATA Filter constrains applies, can anybody confirm this hyphotesis?

If so, the first workaround I can think of is to calculate first the nr of rows, then repeat 'Get items' the nr of times needed to cover the whole List, but not sure if it is the most optimal approach... do you recommend me any other approach (based on 'HTTP request to Sharepoint' or any other trick)?

Thanx!

[Pstork1 · Accepted solution]

Have you tried making sure the column you are using in the OData request is an Indexed column?  That might overcome the 5,000 item limit when requesting a specific Item using OData.

[RobElliott · Accepted solution]

@efialttes the blog post at https://www.ohthreesixtyfive.org/2018/03/microsoft-flow-overcoming-the-list-view-threshold.html

give a solution of indexing the Modified column.

Rob
Los Gallardos

[efialttes]

Mmmm... thanks @Pstork1 !

I said the values were Unique, but I didn't indexed it yet.

For sure I will try it, thanx for the tip!

[efialttes]

Thanx, @RobElliott !

@Pstork1 also pointed on this direction, so I need to find some time to test it... it sounds very promising!

[aechague]

Yep, in my case adding an index in al list of 19k records was the solution, thanks!!