cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gavinleung
Resolver I
Resolver I

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

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
Jeff_Thorpe
Super User
Super User

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.

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. 

 

 

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.

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 ? 

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,113)