You’re offline. This is a read only version of the page.
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() && ('Finish Time'+0.25) >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.
@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.
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
Share
Report
All responses (
Answers (
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.
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?
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?
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 🙂
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.
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
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
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
#1
WarrenBelz
146,587
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,928
Most Valuable Professional
