cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Dual Super User
Dual Super User

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

View solution in original post

17 REPLIES 17
Highlighted
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.
Highlighted
Helper I
Helper I

Re: How to Filter Sharepoint List Datetime without Delegation Warning

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

Highlighted
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
Helper I
Helper I

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?

Highlighted
Dual Super User
Dual Super User

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

View solution in original post

Highlighted
Helper I
Helper I

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!!

Highlighted
Dual Super User
Dual Super User

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

Highlighted
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.
Highlighted
Helper I
Helper I

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
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,235)