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

Filtering a table between 2 date pickers

Hi there,

 

I have a data table that reads data from a sharepoint data source and the list is called "CustomerCareLog", The column that i'm wanting to filter by is called "Date_Complete".  I am wanting to be able to insert two dates in dropdown boxes one called "DatePickerFrom" and the other called "DatePickerTo". I would then like the data in the table to just show any items that have the completed dates bewteen the 2 insterted dates. (if that makes sense) it's logically quite simple but i cannot work out the formula.  This is what i have, but not having any joy with it.  Any help would be appreciated.

 

Filter(CustomerCareLog,Date_Complete,DatePickerFrom.SelectedDate>=DatePickerTo.SelectedDate)  

With this i am just getting the table saying that we didnt find any data at this time.

 

I'm not sure if this has anything to do with the format of the date? i have changed the date pickers to the UK format of "dd/mm/yyyy" however in the datatable the format is coming up as US format and i cant find anywhere to change this? any ideas?

 

thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Filtering a table between 2 date pickers

Have you tried to do just:

 

Filter(
     CustomerCareLog,
     (Date_Complete >= DatePickerFrom.SelectedDate) && (DateComplete <= DatePickerTo.SelectedDate)
 )

_____________________________________________________________________________________
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.

View solution in original post

6 REPLIES 6
Highlighted
Community Support
Community Support

Re: Filtering a table between 2 date pickers

Hi @d3ell,

Do you add two DatePicker controls within your app? One is DatePickerFrom, another is DatePickerTo?

Do you want to filter your table data source based on the Date_Complete column which is between the 2 inserted dates?

I suppose that you have added two DatePicker controls within your app, is it true?

Based on the formula that you mentioned, I think there is something wrong with it. I have made a test on my side, please take a try with the following workaround:

Set the Items property of the Gallery or Data Table to following:

Filter(
CustomerCareLog,
Text(Date_Complete, "mm/dd/yyyy") >= Text(DatePickerFrom.SelectedDate, "mm/dd/yyyy"),
Text(Date_Complete, "mm/dd/yyyy") <= Text(DatePickerTo.SelectedDate, "mm/dd/yyyy")
)

Or

Filter(
CustomerCareLog,
Text(Date_Complete, "mm/dd/yyyy") >= Text(DatePickerFrom.SelectedDate, "mm/dd/yyyy") && Text(Date_Complete, "mm/dd/yyyy") <= Text(DatePickerTo.SelectedDate, "mm/dd/yyyy")
)

More details about the Text function, please check the following article:

Text function

 

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

Re: Filtering a table between 2 date pickers

Hi,

 

Not had any joy with either, see pictures. i have tried to capture the error in the snip.

 

Any thoughts?

 

Thanks 

Highlighted
Helper V
Helper V

Re: Filtering a table between 2 date pickers

Hi

Have you or anyone any further thoughts on how this can be achieved? I’ve been reading some other forums and someone mentioned a function called datediff but I’m also not having any joy with that!?
Highlighted
Super User
Super User

Re: Filtering a table between 2 date pickers

Have you tried to do just:

 

Filter(
     CustomerCareLog,
     (Date_Complete >= DatePickerFrom.SelectedDate) && (DateComplete <= DatePickerTo.SelectedDate)
 )

_____________________________________________________________________________________
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.

View solution in original post

Highlighted
Helper V
Helper V

Re: Filtering a table between 2 date pickers

Thanks Randy, The solution works and does exactly what i asked for so i have made your reply the solution, i do however have a slight issue with it though as >= & <= arent delegateable in powerapps for a sharepoint list so i'm getting the dreaded delegation warning.  This shouldnt cause me any bother in the inetrim period as the data source is going to be fresh and it may take up to a year to reach 1000 items, however it does concern me that one day we're just going to have issues at some point down the line.  Is it known if these operators are likely to be delegateable further down the line?

 

Alternatively would you have in mind any other ideas for searching dates that doesnt include these operators? ie. would it be possible to somehow just search for items in any given month like Jan or Feb or per year etc without having to use the >=<=.

 

Thanks in advance and much appreciated your assistance thus far!

Highlighted
Super User
Super User

Re: Filtering a table between 2 date pickers

Glad it helped!

 

Yes, there is a lot to be desired for SharePoint delegation. My rule of thumb (although probably not the best design practice) is that by the time my lists grow to numbers that are an issue, they will have worked out better delegation methods for SharePoint.

 

That said, for your situation, the operators that you're referring to are delegable for number fields only.  

For numeric columns, all operators can be delegated. For ID columns, only the '=' can be delegated. Date columns can't be delegated.

So, the short-term workaround is to create a calculated numeric field in the SharePoint list that contains the datevalue of the date column.  Then in PowerApps, do your operations on that calculated field.

 

Hope that helps.

_____________________________________________________________________________________
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.

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,540)