Skip to main content
Community site session details

Community site session details

Session Id : iM42u3Lg8U+l3ORYTh7UyF
Power Apps - Building Power Apps
Answered

Delegation issue

Like (0) ShareShare
ReportReport
Posted on 7 Sep 2023 20:49:28 by

I have a gallery whose items come from a SP list. I'm trying to get the items to populate where the column is not blank but I am getting a delegation issue where this formula might not work correctly with "SP Column" on large data sets. I changed my formula and I can not get the delegation warning to go away.

Filter(PA_CAOS_DETAILS,'Master ID'=gblSelected.ID, !IsBlank('Supporting Air Unit'))

gblSelected comes from a previous gallery which has items from a different SP list.

 

Is there a way to simplify my code or fix it to not have a delegation issue?

Categories:
  • Chris-D Profile Picture
    1,246 on 08 Sep 2023 at 07:31:06
    Re: Delegation issue

    Yes, in reply to the guys above there is one caveat I should mention with the code example I gave you. @FLMike alluded to this but I have fallen into the trap myself so I will explain. 

     

    Mike - "returns more than the local collection can store"

    This isn't quite right, a local collection, one defined inside your app, can store as many rows as you like (although dumping your entire database into one probably isn't recommended!).

     

    You can add to an already existing collection:

     

    Collect( MyCollection, Filter( ...));
    Collect( MyCollection, Filter( ...));
    Collect( MyCollection, Filter( ...));

     

     

    The issue is with the Filter() function. Take the original Filter as an example:

     

    Filter(PA_CAOS_DETAILS,'Master ID'=gblSelected.ID)

     

     

    This filter won't give you a delegation warning, right? So it'll return as many rows as match, right? Unfortunately not. Even though it's a delegable filter, it will still only return the number of rows in your non-delegation rows limit. 

     

    The wording in Power Apps says:

    "Set how many number rows are retrieved from server-based connections where delegation is not supported."

     

    This isn't really correct. Whether delegation is supported or not, you will only get this many rows back. 

     

  • Verified answer
    EddieE Profile Picture
    4,641 Moderator on 07 Sep 2023 at 21:58:29
    Re: Delegation issue

    @FLMike 

    I agree, the warnings can be a good thing to remind you that there's a potential issue with the returned data.

     

    As both @kingy61422  and @Chris-D have said though, you cannot do what you are trying to do with the available function and SharePoint, ie

    - Not (!) isn't delegable

    - IsBlank() isn't delegable, and

    - '<>' isn't delegable for Text columns (it is for Number and Date types tho)

     

    So, you need to make some decisions around how your users will use the data in your app. For example, say this code should return >2000 records

    Filter(PA_CAOS_DETAILS,'Master ID'=gblSelected.ID)

    ... but users only need the latest, say 500-1000 records, you could do this instead

    Filter( Sort(PA_CAOS_DETAILS, ID, SortOrder.Descending) ,'Master ID'=gblSelected.ID)

    and use that in @Chris-D's code.

     

    If this doesn't work for your use case then you will need to consider other filter options like Dropdowns, Toggles, checkboxes, etc. For example, you could setup a Dropdown for 'Supporting Air Unit' outside the gallery then filter your gallery like so

     

    Filter( 
     PA_CAOS_DETAILS,
     'Master ID'=gblSelected.ID,
     'Supporting Air Unit' = ddAirUnit.Selected.Value
    )

     

    If you are not keen on these then you could try extending this @RezaDorrani video where he uses a Flexible Height gallery and a Show/Hide technique to 'filter' using non-delegable functions

     

    https://www.youtube.com/watch?v=44j2VRbdWjk

     

    It's all about workarounds with situations like this - unfortunately 🙂 

  • Michael E. Gernaey Profile Picture
    45,114 Super User 2025 Season 2 on 07 Sep 2023 at 21:35:51
    Re: Delegation issue

    Agree it will

     

    However if it returns more than the local collection can store he wont get all the records. So while the warning is gone, he / she / them :-), will not necessarily know why they aren't getting all the rows back.

     

    Sometimes I think the warnings are useful to remind us when we have issues, what to look at first, versus removing warnings that may or may not impact us.

     

    Just my 2 cents 🙂 and not worth the bits I typed them in 😉

  • Verified answer
    Chris-D Profile Picture
    1,246 on 07 Sep 2023 at 21:11:30
    Re: Delegation issue

    Hi Kingy, as FLMike said some functions are non-delegable. IsBlank is one of those. 

     

    However, there's a way around this. You can wrap your filter so you're not using the IsBlank function on the SP List:

     

    With({
     // First we filter on your SP List
     // This returns a collection and stores in SPFilter
     SPFilter: Filter(PA_CAOS_DETAILS,'Master ID'=gblSelected.ID)
    },
     // Then we filter SPFilter
     Filter( SPFilter, !IsBlank('Supporting Air Unit'))
    )

     

     

    This doesn't cause an issue because we store the result from the first filter in a collection within your app. You can do what you like with collections. 

     

    More information about delegation 

  • Michael E. Gernaey Profile Picture
    45,114 Super User 2025 Season 2 on 07 Sep 2023 at 21:05:36
    Re: Delegation issue

    Hello,

     

    There are just some instances where you will get the warning and you cannot make it go away. There are in some rare cases the ability to use collections to preload the data and run off of that, but in cases where you are talking to an SP, where you may or may not leverage delegation it won't go away.

     

    Think of it like this.

     

    Not all things are delegatable, depending on the source and all sources have some delegation limits.

     

    If you are querying and you have way more rows that "may come back" than the client can handle, then you may see warnings because there is nothing you can do.

     

    You can only retrieve back so many rows, so "if" it may give you back more than that based on the row type and the filter you are using, its just there... and as long as you know it will never be over that 500,2000 mark, don't worry.

     

    That being said, you should make sure you are using filters that will limit the rows as much as possible and always us delegatable filters.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete