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

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (3,418)