Sharepoint Get Items - Filter Query - unexpected behaviour

[M4k5]

Hi,

I am currently working on a flow to get items from a sharepoint list. Since the list is larger than 5000 entries I use the "Filter Query"-Option from the "Get Items"-Step.

There are two neccessary Column which I use for filtering.

In the sharepoint list the expression "Column1 ne null" would return more than 5000 entries.

In the sharepoint list the expression "Column2 eq null" would return less than 5000 entries.

I notices unexpected behaviour when using the "and-expression":

Column1 ne null and Column2 eq null

If I use the "and-expression" like above only the first 5000 entries of the sharepoint list are evaluated with "Column2 eq null".

This is kind of unintuitive since I thought the whole expression is evalueted when using a Filter Query.

But after I switched the expressions like this:

Column2 ne null and Column1 eq null

It does work like intended since "Column2 eq null" returns less than 5000 entries which are then filtered with "Column1 eq null".

Are there any information on how the filter query is functioned internally? 

Or is this the intended behaviour?

Thanks already!

[fchopo]

Hi @M4k5 

The SharePoint REST API limits queries to 5000 number of items to be returned (so it has nothing to do with Power Automate).

You can overcome this limit making different requests and merging all the results, at is explained in this excellent post by @Paulie78 : Get more than 5000 items from SharePoint with Power Automate (tachytelic.net)

Regards,

Ferran

[M4k5]

Hi fchopo,

thanks for the reply. But it doesnt fully answer my question. The problem is not the 5000 item limit it is the expression I am using to bypass this limit. 

I am using the filter query to limit the returned data but there is the problem. 

It seems like the expression I am using does not work as intended. 

It seems like the filter query is just using the first part of the expression (Column1 ne null) to retrieve items from the list and then filters it with the second part of the expression (Column2 eq null).

The problem here is that the first part of the expression is retreiving more than 5000 items. 

Therefore the second part of the expression filters just a small part of the full list which has more than 40000 entries.

I thought the filter query uses the whole expression to retrieve items from the list but it seems like it is not.

If I switch the expression like this Column2 ne null and Column1 eq null it works just fine since the second part of the expression (Column2 eq null) only retrieves less than 5000 items which are then filtered with (Column1 eq null).

I hope this specifies my question a bit more. 

Thank you already!

Maks

[fchopo]

Hi @M4k5 

Really weird.. Could you try to use the "SharePoint Send HTTP Request" action and use the following expression?:

https://mycompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('YourBigList')/items?$filter=Column1 eq null and Column2 neq null

And use the outputs results, instead of using the get items action.

Hope it helps!

Ferran

[M4k5]

Hi Ferran,

I just tried to send a HTTP Request to solve the problem. But if I use the request as you mentioned it occurs the BadGateway-Error. 

Strangly enough if I switch the statements of the filter expression it works as intended.

https://mycompany.sharepoint.com/sites/test/_api/web/lists/GetByTitle('YourBigList')/items?$filter=Column2 neq null and Column1 eq null

So the problem is also present in HTTP-Requests. 

It is a pretty weird behaviour but I think it is sharepoint which handles the filtering not as I thought it would. 

Maybe there will be a fix in the future. 

Thank you anyway Ferran!

Maks