SharePoint Delegations?

[leyburn19]

I found this issue by chance as there were no errors created to indicate a problem.  Basically I have a sharepoint list with 40,000 records, growing by 1500 per week.  I have a need to do a filter on the list so I can do other tasks.  As I do not know the actual ID I cannot do a "get item" filter and have to use "get items" based another column with a unique value.  Some like:

Column1 eq 'myvariable'

I found that I get a null result and found that this must be a delegation issue.  I increased the Get Items by configuring the setting to paginate up to 5000 rows.  This started to get the match I need.  I was able to improve this as I knew the get items would be in a batch of the last 100 records so I put a sort in to have it sort by ID descending. I am not sure if the sort is pre filter or post,  but it works

So while I have my workaround,  I a concerned that this will not  suit some other flows I have.  I did not think it would be a delegation issue as the filter is not not complex.  Does anyone have ideas on why this has occured and potential fixes.

[Pstork1]

I suspect the major issue is the column type you are trying to use in the filter.  SharePoint filter statements are only delegable for number, text, and boolean columns.  DateTime columns and complex columns like Person or Choice columns are non-delegable at this time, although MS is working to expand delegation support in SharePoint.  What kind of column are you filtering on?

[leyburn19]

t is a simple short text column.  I duplicated the issue on another short text column as well.  

[leyburn19]

This issue persists but it is still wierd.  A standard query on get items returns 100.  I changed this to 5000 and I do not get the problem. The query type is delegable so I belive I should not have to do this.  My query is based 'eq' and on a short text field in SharePoint.

I have a work around but a get items of 5000 does add time and if it were not deleagble then even 5000 would be an issue.   I 40000 records growing at 1500 per week so I am keen to understand why