cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
McMonty
Resolver I
Resolver I

Substitute for in operator for SharePoint list

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 🙂

2 ACCEPTED SOLUTIONS

Accepted Solutions

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... 

View solution in original post

mdevaney
Super User
Super User

@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."

View solution in original post

4 REPLIES 4
mdevaney
Super User
Super User

@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."

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... 

View solution in original post

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
Super User
Super User

@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."

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,339)