cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter sharepoint list by Date

This is my first post so i apologize if it's structured poorly. I have some familiarity with PowerApps but i'm still learning. 

I'm trying to create a collection on the onvisible property. until i can get it working correctly, i ave the functionality on a button so it can be easily tested. This is what i currently have for the ClearCollect: 

ClearCollect(
UsersTapeCollection,
Filter(
'Lots of Data',
CPMName = currentUser, 'WE Date' = currentWEDate//(but equals doesnt work??????????????
)
)

 

currentWEDate is determined by this: 

Set(todaysDay,Weekday(Now()));
Set(currentWEDate,
Switch(todaysDay,
1,Now()-0,
2,Now()-1,
3,Now()-2,
4,Now()-3,
5,Now()-4,
6,Now()-5,
7,Now()-6
))

 

whats happening above is the currentWEDate is always being set to the date of the previous sunday. like right now, the current WE date is 12/29 because the most recent sunday was yesterday.

 

My gallery items are: 
UsersTapeCollection

 

and the error i get is "the requested operation is invalid. Server Response: The expression "(CPMName eq 'Kyle Johnston')and(WE_x0020_Date eq 2019-12-29)" is not valid. clientRequestID:addfbf8c.............."
I believe it has something to do with - and / being used but if i try a substitute it changes the property type and still gives errors. please help. 

9 REPLIES 9
Highlighted
Frequent Visitor

Re: Filter sharepoint list by Date

replacing "=" with "in" makes it work but with delegation error. it was to my understanding that only "=" can be delegated when filtering dates???

Highlighted
Dual Super User II
Dual Super User II

Re: Filter sharepoint list by Date

Two points to make.

1) Only filtering for a NULL date is delegable in SharePoint.  Otherwise filtering on dates is always non-delegable, even with '='.  Here's the footnote from the documenation.  Null assignment is delegable for a DateTime and Date field. Actual DateTime and Date values are not delegable. Filtering a DateTime or Date field for Blank is supported. Filter on not null is not supported. Only the = (not the <>) operator is delegated to SharePoint (the Not operator is not delegated.)

2) The other problem is that Date values are always Date and Time values.  So even if the dates are equal the time portion probably isn't, so they aren't equal.

 

Here's the way to get around those issues.

Use a Nested filter that Filters the records in two steps. First, but Current User, which is delegable, and then filter the results, which are hopefully now below the limit, by the non-delegable date.  To filter the date put both sides of the filter equation to get just the date as a string.  Like this

Text('WE Date',ShortDate) = Text(currentWEDate,ShortDate)


-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Frequent Visitor

Re: Filter sharepoint list by Date

The filter for current user alone is not enough to push under the delegation limit (500) it's possible that within just the first week the returned values for the currentUser alone could reach above 500 (approx 650) so even raising the limit to 2000 would only work for 4 weeks or less. Thats why i wanted it to only display results for a specified date AND for a specified user. 

Highlighted
Frequent Visitor

Re: Filter sharepoint list by Date

there is a third value that i'll be filtering for as well, that'll be branch Number. But If i leave the filter for only CurrentUser and Branch number, it would still push about 2600 results over 52 weeks. I'm pretty sure i need that Date to be part of the filter.

Highlighted
Dual Super User II
Dual Super User II

Re: Filter sharepoint list by Date

If you have control of the underlying SharePoint list you can create a column that is automatically populated when a record is created or changed with the string representation of the date entered.  Then you can filter for it using a delegable filter.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Frequent Visitor

Re: Filter sharepoint list by Date

I tried... but im horrible at the syntax for creating a calculated column on sharepoint. 

 

i go to List settings > create Column

Give it a Name

Type of Info - Calculated

Formula - ????? (guessing something like "=Text(['WE Date'])"

Data Returned  - Single line of text

Highlighted
Dual Super User II
Dual Super User II

Re: Filter sharepoint list by Date

Unfortunately, calculated columns can't be used in a delegable filter either.  You would need to have something like a Power App that creates or edits records and have it generate the value to be stored in a regular text column.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Highlighted
Frequent Visitor

Re: Filter sharepoint list by Date

I solved it by making my date column a single line of text column. whenever the date is patched, it is patched as a text value. This made the "date" delegable as a filter because I'm filtering a text value now.

 

What happens, is onvisible the collection is populated for the specific user with the specific date range. those two filters should absolutely never yield more than 250 results. if for any reason i need the date value of the text, i can more easily convert it over then. 

Highlighted
Community Support
Community Support

Re: Filter sharepoint list by Date

Hi @KyleJ ,

 

Thanks for sharing your resolution here. 

 

Regards,

Mona

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

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

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
Top Kudoed Authors
Users online (4,213)