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

how to use datepicker to filter a list based on a date column

I have a SharePoint list that i need to filter with a date picker in my Power app.

 

My date column is called NextReview and my list is called Projects.

 

I have inserted a Date picket above my gallery and i tried adding the following on the Datepickers "On select"

Distinct(Filter(Projects, NextReview = Text(DatePicker1.SelectedDate, "[$-da-DK]mm-dd-yyyy")),Title)

It is throwing an error and doesnt work. What am i doing wrong?

 

25 REPLIES 25
Highlighted

So your list now contains the formula like:

Filter(
      Projects,
      Text(NextReview,ShortDate) = Text(DatePicker1.SelectedDate, ShortDate)
   )

?

If so, a blank list to me sounds like a formatting incompatibility.  Where I would go next is create two text boxes on the screen, set one to "Text(NextReview, ShortDate)" and the other to "Text(DatePicker1.SelectedDate, ShortDate)" and make sure the two text boxes appear identical in formatting.

Highlighted

My "Text(NextReview, ShortDate)" textbox is empty all the time, but the other one fills out with the expected date.

Now i at least know that it is something to do with the first box.

 

error1.PNG

Highlighted

@Pushthe ,

As you wish, I will not respond further on this

Highlighted

@Pushthe assuming your list is in some form of gallery, your "Text(NextReview, ShortDate)" textbox should also be in that gallery, otherwise it will be looking at an entire column rather than a single entry and would be blank.

Highlighted

when i put it inside the gallery and run the app nothing happens when i choose dates on my date picker.

 

error1.PNG

As shown above my 2 textboxes you sugested are visible. 
My "SelectedDate from picker debug" is outside of the Gallery.
My "NextReview debug text" is inside the gellery.

When i start the app this is the result.

err.PNG

There are no results, my "NextReview debug text" doesnt show as the results arent coming, but my "SelectedDate from picker debug" is showing the correct date.

So something with results of my selection is te problem. i just cant figure it out.

Highlighted

@Pushthe You might wanna back track a little then, return your gallery to it's original state of showing all items regardless of the datepicker so you at least get the results, then compare the two debug txt objects to make sure you have the data in the right format before you start trying to filter using them. 

Highlighted

Ok ty.

 

The only other thing I had in the Gallery "Items" property was this:

 

Sort(Projects,NextReview,Ascending)

And my gallery worked with this. I could see all the element and open individual ones up, edit them, save etc.

 

After i changed the "Items" property of the Gallery to this:

Filter( Projects, Text('NextReview',ShortDate) = Text(DatePicker1.SelectedDate, ShortDate),Title)

I started getting problems.

Guess ill have to tell the users that datepicker is not an option.

Highlighted

@Pushthe 

This is probably just a formatting problem.  I knocked up a quick demo of this and the supplied formula is working.  I'll detail what I did here and maybe you'll see where you're going wrong.

So first I created the collection on the "OnStart" of the app

ClearCollect(
    EntriesList,
    {Name: "Entry 1", Date: "06/08/2020"},
    {Name: "Entry 2", Date: "05/08/2020"},
    {Name: "Entry 3", Date: "06/08/2020"}
)

Then created a gallery and a date picker.

Next, in the gallery's "Items", I put the following

Filter(
      EntriesList,
      Text(Date, ShortDate) = Text(DatePicker1.SelectedDate, ShortDate)
   )

And it works

DateExample1.png

DateExample2.png

Highlighted
Helper I
Helper I

Try this!
Distinct(Filter(Projects, NextReview >= DatePicker1.SelectedDate),Title)

Highlighted


So first I created the collection on the "OnStart" of the app

 

ClearCollect(
    EntriesList,
    {Name: "Entry 1", Date: "06/08/2020"},
    {Name: "Entry 2", Date: "05/08/2020"},
    {Name: "Entry 3", Date: "06/08/2020"}
)

 

Then created a gallery and a date picker.

Next, in the gallery's "Items", I put the following

 

 

 


Ok So what is the point of the ClearCollect? and when you said you created it where did you put it in the app? Or did you "just" insert it into the "OnStart" of the app?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,334)