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

Applying further filters to my results

Hi all,

 

I'm learning so much about powerapps by trial and error but also from the answers you guys provide to questions here.

 

I already have a great powerful app that my colleagues are already benefitting from but there is a piece of functionality I want to build in.

Current functionality.

I have attached to an excel spreadsheet in OneDrive which has a number of tables which each contain the same fields of information regarding a specific software product that my company provides.

My app allows the user to select which table to search (which product) via a drop-down and then to use a free text box to search within that table for keywords.

This returns all results within that table and selecting an answer then allows the user to view the details as well as editing the details and updating them.

 

What I would like to add

I would like to add further filtering for once the product (table) has been selected and the keyword searched for. To filter based on a few of the columns I have. These would be a drop-down containing the options from one of the columns and also a date selector allowing the user to search only for results that have a date from a specified date onwards (to filter out product features that are too old and have been updated). In the tables, I have a column where I have specified the date.

 

Can you help with the two additional filters?

 

For further info the formula I have for the search already is:

Filter( If( ProductDropdown.Selected.Value = "AccessLMS", AccessLMS, ProductDropdown.Selected.Value = "GameBrain", GameBrain, ProductDropdown.Selected.Value = "EarlyPay", EarlyPay, ProductDropdown.Selected.Value = "CoreHR", CoreHR, ProductDropdown.Selected.Value = "SelectHR", SelectHR, ProductDropdown.Selected.Value = "PeopleHR", PeopleHR, ProductDropdown.Selected.Value = "AccessFinancials", AccessFinancials), TextSearchBox1.Text in Question || TextSearchBox1.Text in Response || TextSearchBox1.Text in Tags || TextSearchBox1.Text in Feature )

 

I know I'm asking a lot but this will offer great functionality that my colleagues will really rejoice in and will make me look so great 🙂

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @Petecmsmith ,

No as far as the order goes - this is not like Excel calculating by columns or rows - if the date is on the form, it will be included in the calculation and yes - the test is that the date entered is before the date in your list.

So what is not working as intended now?

Note due to timezone I will be offline shortly, but will pick up your response in the (my) morning.

View solution in original post

Hi @Petecmsmith ,

That actually makes it a bit easier.

If you have no Default on the DatePicker (leave it blank), and then Reset it, it will end up with null date (sort of), but for your purposes it is empty.

So the filter I gave you will work and ignore a blank date.

If(
   Value(DatePicker1.SelectedDate)=0,
   true,
   xxxx

So you simply need to have

Reset(DatePicker1)

to clear it.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

29 REPLIES 29
Super User II
Super User II

Welcome to Power Apps! Glad you, and your organization, are enjoying it.

 

You can simplify parts of your function as follows

  • TextSearchBox1
  • ProductDropdown
    • The primary Filtering criteria looks like a DropDown - what do you have for the Items for that control?
    • Presumably, it's the same values/Items as the column you are looking to filter on
    • Filter ( dataSource, DropDown.Selected.X = dataSourceColumn )
    • Filter ( AccessLMS, ProductDropdown.Selected.Value = {tbdAccessLMScolumnName} )
  • Date Filtering
    • How is your column in AccessLMS formatted? Power Apps can handle both text and actual date values, but there's some nuances involved
    • Add a Date Picker control, and maybe a label indicating that values after the date will be shown
    • Filter ( AccessLMS, DatePicker.SelectedDate < {tbdAccessLMScolumnName} )
    • If the types don't match, you may need to convert the DatePicker to Text, or the AccessLMS column to a Date Value, so they match - let us know what we're working with

Thank you, Gareth. I think I am happy with the filtering and searching that is currently defined. The results seem accurate.

 

Regarding the date picker, I need this to work from the table that has been selected in the drop-down and therefore the table used for the results. From what you have specified will it not just filter against that one table (AccessLMS)? Also, it is erroring when I try it with Invalid use of '.' in the DatePicker.SelectedDate section. Where have I gone wrong there?

 

Thanks again - I really appreciate all the help.

Helper V
Helper V

Right-click any last name, and click Text Filters > Begins With.
Enter “L” in the box that appears, and click OK. Access applies the filter, and now you can print the report with just that data.

Thank you Max - not sure that response was meant for my question?

 

Can anyone give me any more guidance on applying a datepicker to select a from date to my already filtered search results against each of my tables?

Any thoughts? Or do I need to provide more information?

 

Anyone? Anyone? Bueller? Anyone?

Hi @Petecmsmith ,

Some syntax below for you to consider - I will be offline shortly due to time zone - will pick up any response in the morning

Filter(
   Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", AccessLMS, 
      "GameBrain", GameBrain, 
      "EarlyPay", EarlyPay, 
      "CoreHR", CoreHR, 
      "SelectHR", SelectHR, 
      "PeopleHR", PeopleHR, 
      "AccessFinancials", AccessFinancials
   ),
   (
      TextSearchBox1.Text in Question || 
      TextSearchBox1.Text in Response || 
      TextSearchBox1.Text in Tags || 
      TextSearchBox1.Text in Feature
   ) &&
   YourDateField > YourDatePicker.SelectedDate &&
   YourOtherField = YourDropDownName.Selected.xxxx   //Insert Value/Result/Field Name
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thank you Warren. Apologies if a stupid question but what should this code be applied to?

 

Is it to make my search and drop-down portion neater? Or should I apply this to a datepicker element in addition to the search and drop down?

Hi @Petecmsmith ,

I modified the code you provided (I assume this is a Filter for a gallery) to include the date and the drop-down as requested and showed where to put them in the filter. I also tidied up all the If statements with a Switch statement. So the code goes wherever it is now. Is this what you are asking?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Thank you Warren,

 

Yes that's great. I'm just getting my head around this.

 

So the date picker section:

Date > DatePicker1.SelectedDate &&
YourOtherField = ProductDropDown.Selected.xxxx //Insert Value/Result/Field Name

I have used my datepicker name and referenced the field within the tables (date). The dropdown (productdropdown) references the names of the separate tables in my data source (one product per table). With that in mind how do I amend your code? 

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (44,009)