Delegation Warning

[Syndicate_Admin]

Hi all,

I'm new to all this SharePoint, Power Apps and Power Automate, but slowly I'm learning and improving my knowledge thanks to these type of community sites. 

Can't seem to figure out how to get rid of the delegation warnings for the formulas set below to display on my Power Apps dashboard.

It works okay, however, when the data reaches 500 items it doesn’t load up anymore date post 500. Which I end up deleting data provided from (SharePoint).

Sort(Filter(PlanningTool,Or ('Choice Status'.Value= "Late" && ('Start Time' -0.5)Now())),'Start Time',Ascending)

Planning Tool = SharePoint Source 

Choice Status = Drop Down Choice selection of 5 items to choose from.

Start Time = Date and time picker

Finish Time = Date and time picker

Appreciate any help, thanks in advance.

[Syndicate_Admin · Accepted solution]

Hi @cwebb365 and @EddieE 

Thank you both for responding back, much appreciated and well done to you both for sorting my delegation out 😁

The formula does work perfectly with data row limit set to 1 which is exactly what I wanted and delegation has now disappeared.

However, is the formula limited to how many times it can be used or written? I have 13 item to select from in the Column1NameStatus.Value ie I have 13 item to select from in (1) “Published”, (2) “Late”, (3) “Not Arrived” (4) “Arrived Late” (5) “Departed” and so on, when the formula get written 7 times, its okay but after this point it goes in to an error where the warning states that I don’t have permission to use the Gallery!!

Any idea why or advice please?

[Syndicate_Admin · Accepted solution]

Hi @cwebb365 and @EddieE 

Thank you both for responding back, much appreciated and well done to you both for sorting my delegation out 😁

The formula does work perfectly with data row limit set to 1 which is exactly what I wanted and delegation has now disappeared.

However, is the formula limited to how many times it can be used or written? I have 13 item to select from in the Column1NameStatus.Value ie I have 13 item to select from in (1) “Published”, (2) “Late”, (3) “Not Arrived” (4) “Arrived Late” (5) “Departed” and so on, when the formula get written 7 times, its okay but after this point it goes in to an error where the warning states that I don’t have permission to use the Gallery!!

Any idea why or advice please?

[Syndicate_Admin · Accepted solution]

@EddieE ,

When I add another selection from my choice value to the Item formula, I get the following message , I don't have permission to view this data. Server Response: The Name of My List failed, the response is not in a JSON format.

Not a problem any longer,  as you advised, I’ve added a dropdown to my gallery and it works perfectly, I will continue to monitor the new formula and hopefully I can cross this one of the list of things to do.

Well done and thank you so much for your time, very much appreciated your help and advice. Brilliant keep up the good work you provide.

[Syndicate_Admin · Accepted solution]

Hi @cwebb365 and @EddieE 

Thank you both for responding back, much appreciated and well done to you both for sorting my delegation out 😁

The formula does work perfectly with data row limit set to 1 which is exactly what I wanted and delegation has now disappeared.

However, is the formula limited to how many times it can be used or written? I have 13 item to select from in the Column1NameStatus.Value ie I have 13 item to select from in (1) “Published”, (2) “Late”, (3) “Not Arrived” (4) “Arrived Late” (5) “Departed” and so on, when the formula get written 7 times, its okay but after this point it goes in to an error where the warning states that I don’t have permission to use the Gallery!!

Any idea why or advice please?

[Syndicate_Admin · Accepted solution]

Hi @cwebb365 and @EddieE 

Thank you both for responding back, much appreciated and well done to you both for sorting my delegation out 😁

The formula does work perfectly with data row limit set to 1 which is exactly what I wanted and delegation has now disappeared.

However, is the formula limited to how many times it can be used or written? I have 13 item to select from in the Column1NameStatus.Value ie I have 13 item to select from in (1) “Published”, (2) “Late”, (3) “Not Arrived” (4) “Arrived Late” (5) “Departed” and so on, when the formula get written 7 times, its okay but after this point it goes in to an error where the warning states that I don’t have permission to use the Gallery!!

Any idea why or advice please?

[Syndicate_Admin]

@Moe69 

Firstly, go to your settings and update your Data row limit to 2000, setting it to 1 was just for testing purposes.

To manage your 13 items you don't need to change your Filter all that much and you are probably best adding a dropdown outside of your Gallery, like this

- add a Dropdown, rename it to suit your needs but I'll just use Dropdown1 in my code below. Set the Items property to 

With(
    {
        localChoices: Choices(yourListName.Column1NameStatus)
    },
    ForAll(
        Sequence(CountRows(localChoices)+1), 
        { Value: If( Value = 1, "All", Last(FirstN( localChoices, Value-1)).Value)}
    )
)

This technique gives you all available 13 items in a Dropdown with the added option of "All". This technique isn't mine by the way. It was brilliantly developed and offered by @RandyHayes in his YouTube video here. Randy explains why you do this in his video and it's basically to make the Dropdown options dynamic over time.

Your Gallery Items then becomes:

Sort(
   Filter(
      yourListName,
         (Dropdown1.Selected.Value = "All" || Column1NameStatus.Value = Dropdown1.Selected.Value)  &&
         'Part 1 Planned Start Time' < DateAdd( Now(), 30, Minutes) &&
         'Part 1 Planned Finish Time' > DateAdd( Now(), -15, Minutes)
   ),
   'Part 1 Planned Start Time',
   Ascending
)

Hopefully this suits your needs, let me know if the above needs any extra guidance.

[Syndicate_Admin]

So you can't do datediff / add etc. to SharePoint dates with delegation but instead of doing the calculation that way, just do it on the other end? So instead of 

'Part 1 Planned Start Time' - 0.5 < Now() do:  'Part 1 Planned Start Time' < Now() + 0.5 

This essentially is the same thing, and you can do the calculation on that side of the condition. 

Also instead of doing +0.5 it's easier to understand using DateAdd(), so if you want 30 minutes do
 'Part 1 Planned Start Time' < DateAdd(Now(),30,Minutes)  

'Part 1 Planned Finish Time' > DateAdd(Now(),15,Minutes)

Easier to read and understand etc. I think this should get what you're looking for. 

[Syndicate_Admin]

@Moe69 

Well done to @cwebb365 , he has solved your delegation issue for you. I must admit, I haven't read over the MS Delegation docs for awhile and was also typing my answers late at night on my phone without checking said Docs - hence I was a little out of practice! It's nice be schooled and re-learn what has been lost 🙂

The reason this works is because you are sending a constant (ie DateAdd( Now(), 30, Minutes) ) to SharePoint instead of trying to do some maths on a column value (ie a Date value) which isn't delegable. The part in the Delegation docs where this is mentioned is here, which states:

You can also use portions of your formula that evaluate to a constant value for all records. For example, Left( Language(), 2 ), Date( 2019, 3, 31 ), and Today() don't depend on any columns of the record and, therefore, return the same value for all records. These values can be sent to the data source as a constant and won't block delegation.

You can also check if this formula will return all of your required records by changing the Data Row Limit in Settings to 1. Then if more than 1 record should be returned then you will see all of the records in your gallery, if the formula works - just another good tip.

So, your full working formula looks like:

Sort(
   Filter(
      yourListName,
         Column1NameStatus.Value = "Published" &&
         'Part 1 Planned Start Time' < DateAdd( Now(), 30, Minutes) &&
         'Part 1 Planned Finish Time' > DateAdd( Now(), -15, Minutes)
   ),
   'Part 1 Planned Start Time',
   Ascending
)

Thanks to @cwebb365 for getting me to do some homework 🙂 

[Syndicate_Admin]

Hi EddieE,

Thank you for your feedback, I will increase to 2000, however, it just means that I will need to delete data to allow for current week to be uploaded.

But, if you do work the formula out, please, please do get in touch.

Appreciate your support and advise

Thank you  

[Syndicate_Admin]

@Moe69 

The Or() function isn't delegable, hence the warning. You can re-write your function like this tho because it wasn't getting used anyway.

Sort(
   Filter(
      PlanningTool,
      'Choice Status'.Value= "Late" && 
      ('Start Time' -0.5)Now()
   ),
   'Start Time',
   Ascending
)

It's a good idea to reference the MS Docs when looking to understand/fix Delegation errors. If you want to get more understanding of delegation @RezaDorrani has a great 5 part YouTube series here.

[Syndicate_Admin]

Hi EddieE,

Thank you for responding back, much appreciated.

I have followed and amended as you mentioned, but still getting the delegation warning.

I have since update the names on the data cards in SharePoint, please see screen shot, still the same requirements of events. this is my real details been used in my SharePoint data list. 

[Syndicate_Admin]

@Moe69 

Sorry, @cwebb365  is correct in that the Or() function is delegable but you didn’t appear to be using it correctly. Removing it hasn’t fixed your issue though.

It seems like you are doing a time calculation on 2 columns, is that what you are trying to do?

[Syndicate_Admin]

Hi EddieE,

Thank you for replying back,

Yes, that’s correct. I am trying to do a time calculation on 2 columns, one for pre-planned start date and time and the other one is post planned finish date and time.

I’m not that experienced enough, however I’ve taken your advice to watched the delegations created by @RezaDorrani but I have not been lucky enough to resolve the delegation on my App. still trying my best to understand if there is a way round this through the different type of scenarios he has covered. 

At the moment, I’m deleting post data in my SharePoint list to allow for current day to be upload in to Power Apps.

Any help or advise is very much appreciated.

Thank you   

[Syndicate_Admin]

@Moe69 

Ok, so normally you’d use the DateDiff() function to do time calcs but I don’t think it’s delegable. Also, I don’t think simple time calcs, like the ones you are using are delegable either? Not sure, I’d have to test that.

If you need to do these calcs and the MAX number of records you need to do them is <2000 you can increase the records limit to 2000 in the Settings and just leave the formula as is. If you need more than 2000 records I think you’ll have to think differently about how you data is structured to achieve what you need

[Syndicate_Admin]

That’s actually not accurate. Or() is totally delegable. I think the delegation might be coming from the Now() function. To get past that you can just do an UpdateContext({varNow:Now()}) on the button click or what not leading to this filtered event (maybe on start?). Anyway not at computer so can’t test but it could be fixed. Just know getting dates used to cause delegation issues. Or should be fine.  

[Syndicate_Admin]

Hi  cwebb365,

Thank you for responding,

My data is coming direct from SharePoint where when the data card choice value is updated (from a drop down selection of 13 items). Power Apps is used as a monitoring screen and there is no button to click on, however i am using a timer to refresh the content to reflect the change has happened.   The below formula is repeated 12 times against the the other choice items.