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

Filtering drop down based on a Date Picker with SharePoint Online List

I am trying to populate a drop down list in PowerApps based on a DatePicker field, however, the drop down is not showing all the values existing in the list in SharePoint.

 

List Name: Fitter List
DateTime field in Fitter List: AppointmentDate
DatePicker component in PowerApps set as ShortDate: DatePicker1
Field to display in drop down: Title

 

First query tried:

 

    Distinct(Filter('Fitter List', (AppointmentDate = DatePicker1.SelectedDate)),Title)

 

Error returned:

errorpowerapps.png

Then tried to convert both the field being queried and the Date picker field to text and compare. This returns some selections in the Date Picker but not all.

 

    Distinct(Filter('Fitter List', (Text(AppointmentDate,DateTimeFormat.ShortDate) = Text(DatePicker1.SelectedDate))),Title)

 

I then created another Calculated field AppointmentDateText based on AppointmentDate, as shown below:

 

    =TEXT(AppointmentDate,"m/d/yyyy")

 

and changed the query to:

 

    Distinct(Filter('Fitter List', (AppointmentDateText = Text(DatePicker1.SelectedDate))),Title)

 

Similarly, it worked for some of the selections but not all. The idea behind "m/d/yyyy" as Date Format is due the same format being displayed in the DatePicker component.

 

These are only a few of the queries tried, it is not working and nothing is making sense. any help would be greatly appreciated

2 REPLIES 2
Power Apps Staff CarlosFigueira
Power Apps Staff

Re: Filtering drop down based on a Date Picker with SharePoint Online List

As you noticed, currently filtering SharePoint lists via dates is not working (due to an incompatibility between the SharePoint connector and PowerApps). You're on the right track about using the text function, but you should use the same text representation explicitly for the calculated column and for powerapps (and I'd suggest using "yyyy-mm-dd", which is a representation that you can both filter and sort on). So your calculated column would have this expression:

=TEXT([AppointmentDate],"yyyy-mm-dd")

And your filter expression would be written as

Distinct(
    Filter(
        'Fitter List',
        AppointmentDateText = Text(DatePicker1.SelectedDate, "yyyy-mm-dd")),
    Title)

Or you can also bypass the calculated column and do the text conversion on the PowerApps side as well:

Distinct(
    Filter(
        'Fitter List',
        Text(AppointmentDateText, "yyyy-mm-dd") = Text(DatePicker1.SelectedDate, "yyyy-mm-dd")),
    Title)
Highlighted
jaymeszerman
Level: Powered On

Re: Filtering drop down based on a Date Picker with SharePoint Online List

Hello

I need help with the formula, I'm having difficulty adapting a formula.

First, the data source is in Sharepoint (List), there are 5 columns, it's called CHP.

Imagem 1.png

And PowerApps, has a calendar, gallery and form.

Imagem 2.png

I am trying formula with the gallery, I managed to filter the list by date, the formula is:

SortByColumns(Filter(CHP;Text('Data do Inicio';DateTimeFormat.ShortDate) = Text(_dateSelected;DateTimeFormat.ShortDate)); "Data_x0020_do_x0020_Inicio")

I am also trying 1 more filter for dropdown, which is name list, wanted to filter by name to see the list.

SortByColumns(Filter(CHP;Text('Data do Inicio';DateTimeFormat.ShortDate) = Text(_dateSelected;DateTimeFormat.ShortDate)); "Data_x0020_do_x0020_Inicio") ;; Filter(CHP; ‘Usuários’ = dropdown2.Selected.Value)

Appeared as an error, could you help me how to filter the dated hold?

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,912)