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?
Solved! Go to Solution.
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.
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)
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??
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
Finally, I'm in UK here and date working fine so don't think it would be an American date format issue.
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)
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.
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.
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) ))
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
Then referencing FunctionalityColl in-place of Functionality
Check out the on demand sessions that are available now!
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Features releasing from October 2020 through March 2021
Check out the Power Platform Community Highlights