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

Filtering Data Table based on dropdowns, date pickers, and search bar

So I'm trying to filter a data table (my data is from Excel, tried to do Onedrive but that won't work for a datatable for some reason?) by two dropdowns, one search bar, and three date pickers. Ideally it would filter on multiple at a time if the user wants or just one or none and just display everything. 

So far I've connected the search bar to filter the data table using data table items=Search(Table1, TextInput1.Text, "Skills"). This works perfectly. 

I am struggling to figure out how to combine all of these. The three dropdowns are for "BusinessArea" and "Location" and for "GradDate" (formatted in text as "Spring 2020" etc). The Date pickers are for StartDate and EndDate. This could be in a dropdown because there are only a couple date options, but powerapps is forcing the date fields into date format rather than text so I haven't been able to fix that to work in a dropdown. 

Capture4.PNG

This is all fake data by the way, and I'm not sure why StartDate is doing that when I click on it it has the correct date. The StartDate1 and EndDate1 are just the text format of the first StartDate and EndDate. 

Capture5.PNG

How it looks so far. I would also like the dropdowns and datepickers to have a default option for "All" but I'm sure I can figure that out so thats not the focus of this post.

Let me know what might help, I'm guessing something along the lines of Filter() or SortColumns() or something similar.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
mkerfs
Level: Powered On

Re: Filtering Data Table based on dropdowns, date pickers, and search bar

This is what worked for me eventually:

Filter(Table1_10,
(If(BusA, If(IsBlank(Dropdown1.Selected.Result),true, Dropdown1.Selected.Result in BusinessArea,true)) &&
If(Loc, If(IsBlank(Dropdown2.Selected.Result), true, Dropdown2.Selected.Result in Location,true)) &&
If(GradD,If(IsBlank(Dropdown3.Selected.Result), true, Dropdown3.Selected.Result in GradDate, true)) &&
If(Skil, TextInput1.Text in 'Skill(s)', true)
&& If(Name2, TextInput4.Text in Name, true) && If(Univers, TextInput5.Text in University, true)) || If(!BusA && !Loc && !GradD && !Skil && !Name2 && !Univers, true))

Where BusA, Loc, GradD, Skil, Name2, and Univers are variables set to true when something is selected or typed in the dropdowns and search bars. 

4 REPLIES 4
mkerfs
Level: Powered On

Re: Filtering Data Table based on dropdowns, date pickers, and search bar

Sorry I made a few typos! THREE DROPDOWNS (location, business area and graddate) and only TWO date pickers (start and end date). The photo does not reflect this because I hadn't changed graddate to a dropdown when I took the screenshot!

Community Support Team
Community Support Team

Re: Filtering Data Table based on dropdowns, date pickers, and search bar

Hi @mkerfs 

 

Could you please try this formula expression?

 

Sort(Filter(DataSource,Location=DropdownLocation.Selected.Location && BusinessArea=DropdownBusinessArea.Selected.BusinessArea&&StartDate=StartDateDatePicker.SelectedDate&&EndDateDatePicker=DatePicker2.SelectedDate),ColumnName,Ascending)

 

More information:

function-sort

function-filter-lookup

 

Best Regards.

Yumia

 

mkerfs
Level: Powered On

Re: Filtering Data Table based on dropdowns, date pickers, and search bar

I created variables that use UpdateContext when the dropdowns have a selection the variable becomes true.

Here's what has worked somewhat well for me:

Filter(Table1,
If(BusA,Dropdown1.Selected.Result in BusinessArea,true),
If(Loc, Dropdown2.Selected.Result in Location,true),
If(GradD, Dropdown3.Selected.Result in GraduationDat, true),
If(Skil, TextInput1.Text in 'Skill(s)', true) )

This works in the way that when I unselect from a dropdown it stops filtering on that dropdown but continues filtering on the other ones. The issue with this formula is that I have to select something in all of the dropdowns before any data to show. So the table is completely blank until a selection is made from each dropdown. I want it to show all of the data at first, then start filtering one by one as each dropdown gets a selection.

 

Alternatively:

Filter(Table1,
If(BusA,Dropdown1.Selected.Result in BusinessArea,true),
If(Loc, Dropdown2.Selected.Result in Location,true),
If(GradD, Dropdown3.Selected.Result in GradDate, true),
If(Skil, TextInput1.Text in 'Skill(s)', true) )

This formula starts filtering immediately (doesn't need all 3 dropdowns used) but when any of them are unselected the whole table or gallery goes blank. 

 

So neither work right now. I'd like to work off the first one and fix it so that it doesn't need all of the dropdowns to be selected to show data. I kind of ignored the dates for now, figured I'd work on that later.

mkerfs
Level: Powered On

Re: Filtering Data Table based on dropdowns, date pickers, and search bar

This is what worked for me eventually:

Filter(Table1_10,
(If(BusA, If(IsBlank(Dropdown1.Selected.Result),true, Dropdown1.Selected.Result in BusinessArea,true)) &&
If(Loc, If(IsBlank(Dropdown2.Selected.Result), true, Dropdown2.Selected.Result in Location,true)) &&
If(GradD,If(IsBlank(Dropdown3.Selected.Result), true, Dropdown3.Selected.Result in GradDate, true)) &&
If(Skil, TextInput1.Text in 'Skill(s)', true)
&& If(Name2, TextInput4.Text in Name, true) && If(Univers, TextInput5.Text in University, true)) || If(!BusA && !Loc && !GradD && !Skil && !Name2 && !Univers, true))

Where BusA, Loc, GradD, Skil, Name2, and Univers are variables set to true when something is selected or typed in the dropdowns and search bars. 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 200 members 4,458 guests
Please welcome our newest community members: