Applying further filters to my results

[Petecmsmith]

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 🙂

[GarethPrisk]

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

You can simplify parts of your function as follows

• TextSearchBox1
  • Given the name, you must be using this as a Text Search
  • Instead of doing multiple OR filters against columns with In, use the Search function
  • https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup
  • Search ( dataSource , controlTextToSearch, "columnToSearch1", "columnToSearch2" )
  • Search ( AccessLMS, TextSearchBox1.Text, "Question", "Response", "Tags", "Feature" )
• 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

[Petecmsmith]

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.

[Max44]

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.

[Petecmsmith]

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?

[Petecmsmith]

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

Anyone? Anyone? Bueller? Anyone?

[WarrenBelz]

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.

[Petecmsmith]

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?

[WarrenBelz]

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.

[Petecmsmith]

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?