Get Items Filter Query does not work suddenly without any change

[410736]

Thank you in advance.  

I didn't make any change on SharePoint and Flow but it does not work suddenly. After check, I found there is no result of my filter query setting. It cannot get items of "Nature is Backlog + Agent" OR "Nature is NotAssigned + Type". Please share me how to change for correct outcome.

Status eq 'NEW' and AssignedTo eq null and Created lt '@{variables('Today_1716')}' and ((Nature eq 'Backlog' and (Agent eq 'Amy' or Agent eq 'Paul' or Agent eq 'Tom')) or (Nature eq 'NotAssigned' and (Type eq 'Toy' or Type eq 'Food')))

Get items either option 1 >> 

Status eq 'NEW' and

AssignedTo eq null and

Created lt '@{variables('Today_1716')}' and

Nature eq 'Backlog' and

Agent eq 'Amy' or Agent eq 'Paul' or Agent eq 'Tom'

Or Option 2 >> 

Status eq 'NEW' and

AssignedTo eq null and

Created lt '@{variables('Today_1716')}' and

Nature eq 'NotAssigned' and

Type eq 'Toy' or Type eq 'Food'

[wyattdave]

Hi @410736 
If you go to the list and manually recreate the filters do you get records? If so it could be due to the Created data column. SharePoint has some unusual behavior around midnight and different time zones (it will show as today but actually be 11pm the day before). So if your flow runs at midnight try a few hours later. Additionally check the region the site/list is set to as this could also cause some timing issues.

[410736]

The time zone setting for site and flow is same. The result is wrong even during day time.

I test the flow if the result is correct if filter query is only.....

Status eq 'NEW' and AssignedTo eq null and Created lt '@{variables('Today_1716')}' and Nature eq 'Backlog' and (Agent eq 'Amy' or Agent eq 'Paul' or Agent eq 'Tom')

or if filter query is only..... 

Status eq 'NEW' and AssignedTo eq null and Created lt '@{variables('Today_1716')}' and Nature eq 'NotAssigned' and (Type eq 'Toy' or Type eq 'Food')

I put top count is 1 in the flow to select one ID for once flow run. 

However, if these two combinations I put together with "OR" in same filter query for one output, it will not come out any result but actually it gets item if filter in the list. 

The get items will follow round robin, cannot split into two filter queries.

Much appreciate if any suggestion can fix it.

[wyattdave]

Hi @410736 ,

I tried building the query and got exactly the same as you (so Im very confused). Could you try below to see if it works (it's the same just restructured so if it works I have no idea why lol)

(Status eq 'NEW' and AssignedTo eq null and Created lt '@{variables('Today_1716')}' and Nature eq 'Backlog' and (Agent eq 'Amy' or Agent eq 'Paul' or Agent eq 'Tom')) or
(Status eq 'NEW' and AssignedTo eq null and Created lt '@{variables('Today_1716')}' and Nature eq 'NotAssigned' and (Type eq 'Toy' or Type eq 'Food'))

If it still doesn't work let me know which the last date it worked and the first date it failed.

[410736]

Tried. It results [ ] which is not correct expectation. 

[wyattdave]

@410736 I wonder if the size of the list or length of your query might be impacting it.

Can you see any limits you might have hit on this list https://learn.microsoft.com/en-us/sharepoint/search-limits 

Another thing to consider is indexing the columns you query, this is normally only for over 5000 row errors (which shows as a throttle error not null return) but its always good practice to index them so won't do any harm to try.

>--------------------------------------------
If this helped solve your question please mark it as the solution.

I also do a weekly Power Platform blog at https://dev.to/wyattdave

[creativeopinion]

@410736 Have you toggled on pagination and set a threshold? The Get Items action returns 100 items per call by default, if you want more items returned per call you'll need to toggle on pagination and set a threshold. The threshold max is 5000. 

[410736]

I change the threshold but didn't work. Finally, my flow resumes after I removed items of list less than 5000. 

[wyattdave]

@410736 did you try indexing the columns? 

• Go to list settings
• Below columns/fields you should see index columns
• Add all the columns used in the query
• Give it 5 mins to index

>--------------------------------------------
If this helped solve your question please mark it as the solution.

I also do a weekly Power Platform blog at https://dev.to/wyattdave