cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DylanEvans
Helper I
Helper I

bugs: delegation to sharepoint online busted

Hi folks.  I just ran into a bunch of delegation issues against a sharepoint online list.  I noticed it  starting in version 3.20081.23.  Is there another place I should go to report platform bugs?

 

Background: I have an app that does a clearcollect on a filtered sharepoint list with item-level permissions.  The backend list has many thousands of records.   Prior to this version, the filter combined with the item-level permissions ensured that when any user accessed the app, the items that made it into the collection numbered under 60. 

Now, I see several issues:

  • I get delegation limits on queries with no delegation warnings.  For instance, the following query:

ClearCollect(MyRecords,Filter('SharepointRecords',Status.Value = "Open"))

has no delegation warnings, but differs in the records returned if the delegation limit in Settings is changed.  

  • The above query generates two duplicate records in the collection for every record in 'SharepointRecords'.  So, if the delegation limit is 1, the collection will have two identical records.  
  • I get delegation warnings on operators listed in the doc as delegable.  Notably, && and in are highlighted as non-delegable:

Filter('SharepointRecords',Status.Value = "Open" && Office365Users.MyProfileV2().mail in 'emails with access')

Screenshot proof:

delegable error.PNG

  • the filter returns 0 records when the only records that match the filter statement are over the delegation limit.  For instance, if Amit has item-level permissions to 3 records that match the filter query above and they are have record IDs 2564, 2204, and 2205, the query and resulting collection will have 0 records when he runs the powerapp.  

Can anyone tell me if these are confirmed bugs?  When will they be fixed? 

Can anyone recommend a workaround? 

8 REPLIES 8
WarrenBelz
Super User
Super User

Hi @DylanEvans ,

I cannot comment on the first one as it should be Delegable, however the in filter is not delegable in SharePoint and I am not sure on the Office Profile.

For this one you could use

With(
   {wUser:User().Email},
   Filter(
      'SharepointRecords',
      Status.Value = "Open" && 
      'emails with access'=wUser
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @DylanEvans ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi Warren.  Can you tell me more about how you know "in" is not delegable?  I linked the official documentation from microsoft, which says "in" is delegable.  Is that page wrong?  Do you have a more accurate source for the information it describes? 

 

Unfortunately, your workaround doesn't help.  My "emails with access" field has multiple emails in it; that's why I'm using the in operator.  Your workaround would only work if it contained a single email.     

Hi @DylanEvans ,

There is some conflicting documentation on this, this is probably the best guide, but I can guarantee you that the in operator is not Delegable in SharePoint. You will get a warning every time you use it and it does not operate over the Delegation limit. 

I did a blog on Delegation that may be of assistance to you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

Thank you.  That's a great resource, @WarrenBelz.  I've got a case open with MS; will update when resolved.  

 

I've worked around the issue for now by eliminating collections - in testing these bugs, they all disappeared when setting the gallery items directly = filter(SPlist,...) instead of assembling a collection first.  Hopefully MS will resolve.  

 

For completeness, I also ran into another related oddity that eliminating the collection doesn't resolve.  The following is delegable:

Filter('SPlist', field1 = 'constA' || field1 = 'constB' && field2 = 'constC')

But embedding an If like this is not: 

Filter('SPlist', if(global_var_bool, field1 = 'constA' || field1 = 'constB' && field2 = 'constC', field2 = 'constD'))
I assumed powerapps would evaluate global vars and pass them as const arguments to the sharepoint data API and refresh if the global var is changed.  looks like no.  This variation does not yield delegation warnings:

if(global_var_bool,
  Filter('SPlist',field1 = 'constA' || field1 = 'constB' && field2 = 'constC'),
  Filter('SPlist',field2 = 'constD')
)

@DylanEvans ,

One thing I see there is the mixing of And && and Or ||. I always bracket the ones that go together.

The delegation warnings are not to be trusted - there are many scenarios where Power Apps may not warn, but is not actually delegating (from memory, First, Last, Count, ShowColumns, nesting IF inside a filter).

One thing you can do is to use the Monitor (go to the tools icon on the left-hand navigator bar and then choose Monitor).

You can use this to check the network traffic and actually see the OData calls and the returned data to get a better picture of what is really happening.

A strength of Power Apps is the fact it is 'data-source agnostic' as you write the same filters/searches/lookups regardless of data source and Power Apps 'translates' those data requests into the relevant language for the data-source. Unfortunately the translator is pretty rubbish and any time it can't perform the translation it just delegates (pulls the first 500-2000 rows and performs the filter itself).

It would be much better if we had the option to choose to query using the native language (e.g. SQL queries for SQL Server) rather than being forced to rely on the 'translator'.

I'll look into that.  Thanks.

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 (2,943)