Substitute for in operator for SharePoint list

[McMonty]

Hi,

I'm trying to find either a substitute or workaround for a delegation warning for the following code:

If(Sum(Filter(LineItems,ReportID.Id in OpenReports.ID),Cost)>0,"Yes","No")

It's the 'in' that's the problem. 

Any ideas or advice welcome 🙂

[mdevaney]

@McMonty 
There is not any workaround for the IN operator with SharePoint. You must change to another datasource liek SQL or Dataverse to use IN without delegation warnings.

If the text is found at the start of your column you can use STARTSWITH.  This is what apps built on SharePoint typically do.

Filter(LineItems, StartsWith(ReportID.Id, OpenReports.ID))

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Drrickryp · Accepted solution]

Hi @McMonty 

Please see https://warrenbelz.blogspot.com/2020/08/power-apps-delegation-is-word-not.html for a few work arounds.  They mostly involve converting the SP data source to collections and combining them. @mdevaney is correct that you will be happier in the long run to use Dataverse and SQL for large data sets as they have more delegatable functions.  One last option that many are not aware can be used in the setting where there is no need to edit or append to the list,(ie. a pure lookup table); that  is to save your list as an Excel spreadsheet table and import it into the App as a Static Excel connector.  An example I use is a table of around 43,000 Zip codes I use to prefill cities and states using just the zip code. 

 https://powerusers.microsoft.com/t5/News-Announcements/Automatically-Prefill-City-and-State-using-Zi... 

[McMonty]

Hi,

I'll have a look at the blog for a short term solution.

You are absolutely right, Dataverse is the way to go in the long term.  

[mdevaney · Accepted solution]

@McMonty 
FYI, the blog post linked in the solution you selected will not solve your specific delegation issue as it is described above.  In fact, it will still have the same delegation problems but you won't receive any warning because you used the WITH operator.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."