cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gavinleung
Level 8

Delegation on Sharepoint List date column

Hi all, 

 

My application is to allow the users to time the tasks they are assigned to. In the landing page, there is a screen providing the tasks of today. That the user can select the time task by task. I'm using Sharepoint List with a date field, and I use Filter on powerapps to retieve the tasks of today. 

 

So the List is getting bigger (~3000 records now), and I found that some records are missing on my app. I believe this is due to the delegation issue.

 

I read some posts to suggest to change the field into a numeric field, how can it be done ? 

And when I need to write back a date into the List, does it mean that I need to change it back to a Date ? 

 

Any advice? 


Thanks,
Gavin

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Delegation on Sharepoint List date column

Hi @gavinleung ,

Currently, the 'in' operator is not delegable within SP list data source. I afraid that there is no direct way to get rid of the Delegation warning issue around the Title column within PowerApps currently.

 

As an alternative solution, please consider replace the 'in' operator with StartsWith function within your Filter formula. Please consider modify your formula as below:

If(
CountIf(Role, User.Email = CurrentUserEmail) > 0,
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), TextSearchBox1.Text in Title),
"Title",
If(SortDescending1, Descending, Ascending)
),
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), 'Assigned To'.Email = CurrentUserEmail, StartsWith(Title, TextSearchBox1.Text)), /* <-- Change the formula here */
"Title",
If(SortDescending1, Descending, Ascending)
)
)

please consider take a try with above soltion, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Delegation on Sharepoint List date column

Since you only want tasks of the day you can tweak your current Filter to get around the delegation issues.

 

Filter(SortByColumns('SPListName', "DateFieldName", SortOrder.Descending ), 'Your current filter condition')

 

You may still get the delegation warning but as long as all of todays tasks don't exceed the non-delegable query limit you won't have any issues.

 

To learn more about the workaround of using a number field with a date field you can watch this video from @RezaDorrani 

https://www.youtube.com/watch?v=eCMuXPI1Qok

 

If you want to learn more about delegation with SharePoint and PowerApps, you can checkout his other video listed below.

https://www.youtube.com/watch?v=gwiErbYtRdA



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
gavinleung
Level 8

Re: Delegation on Sharepoint List date column

Thanks @Jeff_Thorpe 

 

This is my filter:

If(CountIf(Role, User.Email = User().Email)>0, SortByColumns(Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyyMMdd"), TextSearchBox1.Text in Title),"Title", If(SortDescending1, Descending, Ascending)), SortByColumns(Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyyMMdd"), 'Assigned To'.Email = User().Email, TextSearchBox1.Text in Title),"Title", If(SortDescending1, Descending, Ascending)))

 

I am trying to fix the date delegation by adding a column in the List with a text. Not sure if it's working at the moment. 

 

But now, the "=" on name is another issue. 

 

 

Community Support Team
Community Support Team

Re: Delegation on Sharepoint List date column

Hi @gavinleung ,

Based on the issue that you mentioned, I think this issue is related to the Date type column in your Filter formula. Currently, the Date type column of SP list is not delegable in PowerApps.

 

As an alternative solution,I agree with you, you could consider add a Text type column in your SP list to store the Date value.

 

Then within your app, remove the SP list data source, and re-add it again, then modify your formula as below:

Set the OnStart property of the App control to following:

Set(CurrentUserEmail, User().Email)

Then modify your Filter formula as below:

If(
CountIf(Role, User.Email = CurrentUserEmail) > 0,
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), TextSearchBox1.Text in Title),
"Title",
If(SortDescending1, Descending, Ascending)
),
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), 'Assigned To'.Email = CurrentUserEmail, TextSearchBox1.Text in Title),
"Title",
If(SortDescending1, Descending, Ascending)
)
)

then re-load your app (fire the OnStart property of App control), check if the issue is solved.

 

In addition, as another solution to get rid of this Delegation warning issue, you could consider bulk-load your SP list records into a single collection in your app, then use the single one collection as data source in your app instead of your original SP list data source.

Please check and see if the following blog would help in your scenario:

https://blog.coeo.com/davidmorrison/powerapps-500-record-limit-delegation-and-how-to-work-around-it

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
gavinleung
Level 8

Re: Delegation on Sharepoint List date column

Thanks @v-xida-msft , I checked another thread suggesting that loading the whole SP list into a collection is not a good idea. That's why I haven't done so. Also the list will get larger and larger as the records increase everyday, loading it to a collection may casuse performance issue. 

 

I used a variable to get the user email. But what about the TextBox.text in 'Title', can delegation be avoided ? 

Community Support Team
Community Support Team

Re: Delegation on Sharepoint List date column

Hi @gavinleung ,

Currently, the 'in' operator is not delegable within SP list data source. I afraid that there is no direct way to get rid of the Delegation warning issue around the Title column within PowerApps currently.

 

As an alternative solution, please consider replace the 'in' operator with StartsWith function within your Filter formula. Please consider modify your formula as below:

If(
CountIf(Role, User.Email = CurrentUserEmail) > 0,
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), TextSearchBox1.Text in Title),
"Title",
If(SortDescending1, Descending, Ascending)
),
SortByColumns(
Filter('Standard Work Plan', DateInText = Text(Today(),"[$-en-US]yyyymmdd"), 'Assigned To'.Email = CurrentUserEmail, StartsWith(Title, TextSearchBox1.Text)), /* <-- Change the formula here */
"Title",
If(SortDescending1, Descending, Ascending)
)
)

please consider take a try with above soltion, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 407 members 5,795 guests
Please welcome our newest community members: