Answered

Delegation issue

Like (0) Share

Report

Posted on 7 Sep 2023 20:49:28 by kingy61422

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:

General Questions

I have the same question (0)

All responses (5)

Answers (2)

Michael E. Gernaey 51,803 Super User 2025 Season 2 on 07 Sep 2023 at 21:05:36

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Chris-D 1,246 on 07 Sep 2023 at 21:11:30

Like (2)

Report
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

Was this reply helpful? Yes No
Michael E. Gernaey 51,803 Super User 2025 Season 2 on 07 Sep 2023 at 21:35:51

Like (1)

Report

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 😉

Was this reply helpful? Yes No
Verified answer

EddieE 4,641 Moderator on 07 Sep 2023 at 21:58:29

Like (1)

Report
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 🙂

Was this reply helpful? Yes No
Chris-D 1,246 on 08 Sep 2023 at 07:31:06

Like (0)

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

Was this reply helpful? Yes No