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

Filter gallery using datepicker

I can't seem to figure out how to add a datepicker and be able to filter my gallery by the selected date. 

I have a sharepoint list, where all my information is stored and have a column called "date".

I have the following formula in Items (from my gallery):

Filter ("my sharepoint list name", Date=DatePicker1.SelectedDate)

 

But for some reason it's not working. 

Please let me know what I'm doing wrong.

 

Thanks, 

Janette 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Dual Super User
Dual Super User

Re: Filter gallery using datepicker

You've got two potential problems.

  1. Using a Date to filter a SharePoint list is not delegable, so if its a large list, more than 500 items, you may not get accurate results.
  2. Dates in SharePoint are always stored as Dates and Times.  So if you try to filter on a datepicker nothing will match because the Date may match but the time won't.

Try this instead

Filter('SharePoint List Name',Text(Date,ShortDate) =  Text(DatePicker1.SelectedDate,ShortDate))


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

View solution in original post

Community Support Team
Community Support Team

Re: Filter gallery using datepicker

Hi @jrojas 

 

Could you please reference @Pstork1 's suggestion?

If your issue has been fixed,please accept it as solution.

 

More information:

function-text

 

Best Regards.

Yumia

View solution in original post

18 REPLIES 18
Dual Super User
Dual Super User

Re: Filter gallery using datepicker

You've got two potential problems.

  1. Using a Date to filter a SharePoint list is not delegable, so if its a large list, more than 500 items, you may not get accurate results.
  2. Dates in SharePoint are always stored as Dates and Times.  So if you try to filter on a datepicker nothing will match because the Date may match but the time won't.

Try this instead

Filter('SharePoint List Name',Text(Date,ShortDate) =  Text(DatePicker1.SelectedDate,ShortDate))


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

View solution in original post

Community Support Team
Community Support Team

Re: Filter gallery using datepicker

Hi @jrojas 

 

Could you please reference @Pstork1 's suggestion?

If your issue has been fixed,please accept it as solution.

 

More information:

function-text

 

Best Regards.

Yumia

View solution in original post

ClarkyPA365
Level: Powered On

Re: Filter gallery using datepicker

Hi @Pstork1 

 

I have a simular situation but I want to use the DatePicker as a From and To function.

 

So I bring back informaion in a gallery that meet the dates critieria selected for a certain area.

 

 

Dual Super User
Dual Super User

Re: Filter gallery using datepicker

Its essentially the same problem since Date pickers only return a date and you need a datetime value if you want to compare with a datetime value.  If you are using a range then the problem of matching the time isn't an issue, so in that case I would cast the date returned by the picker into a full timespan string and use that for the comparison.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ClarkyPA365
Level: Powered On

Re: Filter gallery using datepicker

Hi

 

This is the forumla I have.  The Date element is underlined, red with the message 'Invalid argumenttype. Expecting a number value'  I'm assuming the number value is relating to the 'Time'?

 

Filter('Perimeter Issues', Date>=DatePicker1.SelectedDate, Date<=DatePicker2.SelectedDate,Building=Dropdown1.Selected.Result)

 

Can you explain more regarding a full timespan string?

 

In my Sharepoint list I have the Date column set to single line of text.  Do i need to change this ti Date and Time?

Dual Super User
Dual Super User

Re: Filter gallery using datepicker

To do a Date comparison you need to use a DateTime column rather than a single line of text.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ClarkyPA365
Level: Powered On

Re: Filter gallery using datepicker

Hi

I've amended my Date column in SP to  Date and Time.  

Assume I leave the rest of the options as they are?  Include Time and Friendly format as No.

 

I've updated my formula, I think, the way it is supposed to be using your previosuly example.

(Filter('Perimeter Issues', Building = Dropdown1.Selected.Result), Text(Date,ShortDate) >= Text(DatePicker1.SelectedDate,ShortDate)) && Text(Date,ShortDate) <= Text(DatePicker2.SelectedDate,ShortDate))

 

The comma after Dropdown1.Selected.Reseult) is underlined in red error messages

Unexpected characters. The formula contain 'ParenClose' where 'comma' is expected

Unexpected Characters. Characters are used in the formula in an expected way

Dual Super User
Dual Super User

Re: Filter gallery using datepicker

I think the closing parentheses after Dropdown1.Selected.Result is in the wrong place.  That closes the filter function before you've added your additional conditions.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
ClarkyPA365
Level: Powered On

Re: Filter gallery using datepicker

Hi,

 

I removed the parentheses and still have red underline for the remaining portion of the formula 'Invalid argument type.  Expecting a number Value

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 106 members 5,310 guests
Please welcome our newest community members: