SharePoint Get Items Filter Query never matches value from other list

[Leslie01]

I am trying to update one list when another list is updated based on the value of a number in a text column 'NewID' in both lists. If a new item is added into list 1 then a new item is added into list 2 with certain values copied from the list 1 item. If the list 1 item is updated then I need to check if an item with this NewID value already exists in list 2 then update it. 

The problem is Get Items is not returning any rows. The input looks like this:

and the output includes

filter=NewID+eq+%2714727%27

Any suggestions as to how to get this to work?

Thanks

[Expiscornovus]

Hi @Leslie01,

Your setup looks ok.

Might be worth checking if the internal name of the NewID field is actually NewID in the target list (list 2) as well. Can you double check that name in the list settings?

[Leslie01]

Yes the field in both lists is a single line of text and name is NewID in the list settings.

[Expiscornovus]

Hi @Leslie01,

Thanks for checking. Sometimes the internal name can be different from the display name because of renaming in the past (NewID vs New_x0020_ID). And the Filter Query field expects the internal name, so it was worth checking 😁

And looking at your example I assume in both lists a list item exists with NewID value of 14727? Again double checking for troubleshooting purposes.

Out of interest, have you also tried a Filter Query for List 2 with one of the out of the box fields like ID or Title? Would be interesting to see if those ones do work.

[Leslie01]

Yes the NewID values are the same in both lists. 

I made copies of the 2 lists as a dev environment (without data as they have over 5k rows) and I built the flow on there, where it works as it should. I then made a copy of the flow and connected it to the live lists where it runs successfully, but it won't update the target list because of this filter. 

[Leslie01]

I've also tried adding a filter query for list 1. It's immediately after the update item which creates the NewID value. The output looks much the same as the other one where no item is returned (in my working dev example the output includes values of all the columns )

I'm unsure what's going on here....

[Expiscornovus]

Hi @Leslie01,

Can you add a Send an HTTP request to SharePoint action for troubleshooting purposes? Would be interesting to see if that action can find your list item with the correct NewID which is stored in the target list.

_api/web/lists/getbytitle('OtherList')/items?$select=NewID&$filter=NewID eq '@{triggerOutputs()?['body/NewID']}'

[Leslie01]

I can't get the http request to work 

https://mytenant.sharepoint.com/sites/mysite/_api/web/lists/getbytitle('My%20List%20Name')/items?$select=NewID&$filter=NewID%20eq%20%2714757%27

List 'My List Name' does not exist at site with URL 'https://mytenant.sharepoint.com/sites/mysite'

I tried this with both lists using the Send an HTTP Request to SharePoint action and directly in the browser.

I used the list name as it appears in the URL of the list with and without the %20 but always the same error.

[Leslie01]

I tried this again using the list name as it appears in the site (not the URL) and  the flow ran for over 2 minutes so I cancelled it. When trying in the browser I get the error:

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

(both lists have over 5000 items)

In my DEV list where the flow works I get a good response

[Leslie01]

Is it possible the filter only evaluates the first 5000 items in the list and any item matching the criteria after the first 5000 items would not be matched?

[Expiscornovus]

Hi @Leslie01,

Apologies for the delayed reply. I missed that part of that your target list has over 5000 items, my bad.

Have you tried turning on pagination in the settings of the Get Items action?

https://manueltgomes.com/microsoft/powerautomate/what-is-pagination/

[Leslie01]

Hi Expiscornovus, 

I haven't tried turning on pagination, but I have just tried making NewID on the target list an indexed column, and this seems to be working now. Not sure how reliable this is going to be so I will keep an eye on it for a while. 

If it's working now is there any benefit to turning on pagination? There's about 10500 items in the list. 

Thanks for all your help.