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

@Gareth_C This will not work as it breaks my Gallery results formulas. It cant recognize This.value anymore.

Highlighted

@Pushthe

Hi,
So I used the ClearCollect command to create a data set to work with so I could test the formula I provided.  I'd assume your data set would be coming from a Sharepoint list/excel sheet/SQL table ect ect?  But as I don't have any of those, I just created a simple data set in the OnStart of my app so I had a collection to work with. 

Highlighted

oh ok. i have tested on a complete different list and i stil get no results.

 

so the new list is called "Functionality"

it has a Title field  (Text)

Start Date field (Date)

End date field (Date)

Status field (Dropdown)

Project lead field (Person)

test.PNG

When i use The filter on the Gallery "Items" property that looks like so:

Filter(
      Functionality,
      Text(End Date,ShortDate) = Text(DatePicker1.SelectedDate, ShortDate)
   )

When i run this I dont get any hits at all. Same as on my original list. I am 100% certain i am choosing the dates that i can see in my new list.

 

Do i maybe have to do <= or >= instead? or is is because the app wants to format dates in en-US and im using a PC with da-DK??

Highlighted

@Pushthe 

Hi,

I think I'll need to do some further testing on my end, but just quickly..

In your Gallery Items property, should 'End Date' not have single quotes around it?

 

And also, which operator you use depends on what result you want.  "=" means equal to, "<=" means equal to OR less than and ">=" means equal to OR greater than

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators

Finally, I'm in UK here and date working fine so don't think it would be an American date format issue.

 

EDIT:

Alright.. further testing done and I'm still not seeing where your issue lies.

So I created a list in my Sharepoint called Functionality (It looks like you're using sharepoint too)

Slaverty_0-1597225069441.png

And after connecting this to my Gallery, it all worked.  Code inside gallery is

If(
    IsBlankOrError(DatePicker1.SelectedDate),
    Functionality,
    Filter(
        Functionality,
        Text('End Date',ShortDate) = Text(DatePicker1.SelectedDate, ShortDate)
    )
)

I just added an If statement so it was possible to view all records.

Slaverty_1-1597225182481.pngSlaverty_2-1597225206825.png

 

Slaverty_3-1597225225252.png

The yelllow exclamation mark is just a warning about potentially not retrieving all records because I'm filtering directly on the data source rather than a collection or something similar.

The date object there has no special configuration on it.

Only thing I did differently was I made the "Status" field of "Text" type on Sharepoint because I've had issues in the past trying to use the "Choice" type on sharepoint.

Highlighted

hmmm... I was browsing similar other Questions posted on the forums and on a whim tried this:

 

 

Filter(FirstN(Functionality, 10000), EndDate = DatePicker1.SelectedDate)

 

 I found that suggestion here

Suggestion was posted by CarlosFigueira (Staff)

This seems to have solved my results as i am now getting results when i choose an EndDate date from my DatePicker1.

Thank you all for suggestions @Slaverty @WarrenBelz @Gareth_C 

I have also tried with your suggestion @Slaverty and your solution also worked for me. Tho is is still giving me a "Warning" about delegation.

If( IsBlankOrError(DatePicker1.SelectedDate), Functionality, Filter( Functionality, Text('End Date',ShortDate) = Text(DatePicker1.SelectedDate, ShortDate) ))

 

View solution in original post

Highlighted

@Pushthe 

Ace!  Glad to hear you got it working!

 

The delegation warning can also be overcome by putting your datasource into a collection during the app's OnStart

 

ClearCollect(FunctionalityColl, Functionality)

 

Then referencing FunctionalityColl in-place of Functionality 

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,768)