cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FinePilsner
Level: Powered On

How to Filter Sharepoint List Datetime without Delegation Warning

How can I avoid Delegation Warning when trying to filter a SharePoint list based on the absence of a value in a Datetime column?

Here's the situation -- I have a SharePoint list that includes four datetime columns for sequential tasks. For simplicity:

ID | Task 1 | Task 2 | Task 3 | Task 4

There are well over 500 rows in this list.

I'm using a PowerApp to pull back records from this list, change/add datetimes, then Patch the records back to the list.

If all four tasks are complete, I don't need to pull the record into the PowerApp. Tasks 1-4 are completed sequentially, so for convenience, I truly only need to check if Task 4 has a value.

Any technique I try to filter the list is giving a delegation warning. From searching around this forum, it seems = is the only operator I can use to avoid a delegation warning in a date or datetime column. However I can't use = here, as I'm trying to find records that have no value in the datetime column. I also considered adding a calculated column in sharepoint, but it seems any use of a calculated column gives a delegation warning as well.

 

=ClearCollect(TaskColl,Filter(TaskTable,'Task 4'=0))        --> Does not return any records
=ClearCollect(TaskColl,Filter(TaskTable,'Task 4'<1)         --> Delegation warning
=ClearCollect(TaskColl,Filter(TaskTable,Len('Task 4')=0) --> Delegation warning
=ClearCollect(TaskColl,Filter(TaskTable,IsBlank('Task 4')) --> Delegation warning

Is there some workaround here? Open to any suggestions.

 

1 ACCEPTED SOLUTION

Accepted Solutions
RezaDorrani
Level 10

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

 

Delegation warning can be avoided on a date field by using the following

 

Filter(datasource, datefieldcolumn = Blank())

 

trick is in using Blank()

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

17 REPLIES 17
Super User
Super User

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

There are only two functions that can delegate for datetime columns - Sort and SortByColumns

If you have less than 2000 records, you can get away with delgation warnings and use standard syntax on the formula:

  Filter(TaskTable, IsBlank('Task 4')

 

However, if you are over that amount, then you'll need to consider a way to get your datetime values into a column that can be delegated with more functions.  

Typically the plan is to establish a Flow/WorkFlow to populate a column (and no, not a calculated column as those are non delegable) and then you can use the Filter(TaskTable, 'Task 4 text column' = Blank()) formula to get your results.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
FinePilsner
Level: Powered On

Re: How to Filter Sharepoint List Datetime without Delegation Warning

Thanks @RandyHayes -- any creative solutions to this problem that do not require Flow?

Super User
Super User

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

Not really...you need something to put a text version of your date into another column automatically.

As mentioned, calculated columns will not work.  So, beside entering it by hand for every record, then Flow/Workflow is your only option.

Now note, I say Flow/Workflow.   I am referring to using Microsoft Flow to do this, or SharePoint workflow. 

If your concern is over Flow pricing or throughput, then this is easily done with SharePoint workflow without any cost or throttle. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
FinePilsner
Level: Powered On

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@RandyB Ehh more just not want to go through the trouble of creating a Flow for such a small element of this -- easy enough to set up but just adds another layer of complexity to the app. Good to know that's an option though. I'm wondering from a design POV if it's better to add a "Complete" column and just fill with true/false via the PowerApp when Task 4 is completed... hmm

 

Eventually I plan to assocated each Task column with a Person column (Task 1, Person 1, Task 2, Person 2). I assume the same delegation issue would apply to a People field as well?

RezaDorrani
Level 10

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

 

Delegation warning can be avoided on a date field by using the following

 

Filter(datasource, datefieldcolumn = Blank())

 

trick is in using Blank()

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

FinePilsner
Level: Powered On

Re: How to Filter Sharepoint List Datetime without Delegation Warning

PERFECT! So simple. Really appreciate it. I kept thinking "Why can't I just do =null" and totally overlooked this!!

RezaDorrani
Level 10

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

 

Glad it worked out 

Always use Blank() when checking for Null

Delegation is very complex and behaves differently for different data sources

 

Regards,

Reza

Super User
Super User

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@FinePilsner 

Yes, having an actual column that has something you can delegate against is the best way to go.  So, having a complete column would be a good start.

As for @RezaDorrani 's solution - be cautious as the official connector reference for SharePoint indicates that the equal operation is NOT delegable to DateTime columns.  Although it "appears" to work, this is one of those areas where it might not be completely supported yet.  I know that it actually works, but I've never seen an official statement that it is supported, and I try to avoid providing suggestions that can't track back to an official word of some sort.  Perhaps @RezaDorrani you have seen something to the contrary?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
FinePilsner
Level: Powered On

Re: How to Filter Sharepoint List Datetime without Delegation Warning

@RandyHayesThanks for the input, I'll definitely be on the lookout for funny behavior. I've found several sources saying the = operator is delegable for Dates (not sure on Datetime), but to your point, they only seem to believe this because it makes the delegation warning go away, not because of any Microsoft documentation.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 197 members 4,649 guests
Please welcome our newest community members: