Using Filter with EndsWith on large dataset

[kylefriedman]

I am trying to search a list with more than 12K records in it. Basically a user types in an order number and I need to validate it's in a list in Sharepoint that we sync over from SAP nightly. I tried using Filter with EndsWith and it gives me delegation warning. Using StartsWith works fine. The docs are not 100% clear, but I doesn't list EndsWith as delegable (SharePoint - Connectors | Microsoft Learn). Any idea how to get around this so I can search a large dataset. I need an EndsWith test to make this work right. I even added Index to List for AUFNR field. I thought that would fix it after seeing this in SP. I also have less than 12 columns. 

Here is what I see in PowerApps

[Drrickryp]

error

[iAm_ManCat · Accepted solution]

Hi @kylefriedman 

The delegation documentation for SharePoint does state that only StartsWith is delegable:

https://learn.microsoft.com/en-us/connectors/sharepointonline/#power-apps-delegable-functions-and-op...

I would recommend creating a new column, and then creating a flow to pull the last X characters from the field and place it in the new column and do that for all rows, and then when you are storing data in this column going forward make sure to store the last X chars in the new field - this way you'll be able to filter using StartsWith against the new column which would effectively be the EndsWith..

It's not a pretty solution but its the one that I think will be delegable.

[Drrickryp]

error