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 🙂

[WarrenBelz]

Hi @Petecmsmith ,

There are two bits to this

Date > DatePicker1.SelectedDate

is simply looking for a date (you will have to use your actual field name) greater than the date selected in the Date Picker.

The && simply means And - so both conditions have to apply.

The next bit is the second condition you specified originally

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.

As you have not specified exactly the Items of your drop-down or the field name you are matching, I put in the generic structure required.

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,

I'm still not quite there yet though.

I've pasted below the exact code with all the details from the dropdowns which relate to the Tables in my data source. Apologies for the bulk but might give you an idea of what I am trying to achieve. Each table has the exact same fields (columns). One of which is named Date which is what I am wanting to further filter my results on.

I have many errors in the formula field so I know what I have written is quite wrong but perhaps you'll be able to understand where I have and can advise?

Filter(
Switch(
ProductDropdown.Selected.Value,
"AccessLMS", AccessLMS,
"GameBrain", GameBrain,
"EarlyPay", EarlyPay,
"CoreHR", CoreHR,
"SelectHR", SelectHR,
"PeopleHR", PeopleHR,
"AccessFinancials", AccessFinancials,
"Expense", Expense,
"SupplyChain", SupplyChain,
"Document", Document,
"Workspace", Workspace,
"thankQ", thankQ,
"Epos", Epos,
"HospitalityPeople", HospitalityPeople,
"Gamma", Gamma,
"ProcureWizard", ProcureWizard,
"Collins", Collins,
"ComplianceManagement", ComplianceManagement,
"Dimensions", Dimensions,
"MedicationManagement", MedicationManagement,
"Mobizio", Mobizio,
"ProNett", ProNett,
"Capture", Capture,
"EducationHR", EducationHR,
"EducationFinance", EducationFinance,
"EducationPeople", EducationPeople,
"TeamSeer", TeamSeer,
"Rapidata", Rapidata
),
(
TextSearchBox1.Text in Question ||
TextSearchBox1.Text in Response ||
TextSearchBox1.Text in Tags ||
TextSearchBox1.Text in Feature
) &&
"Date" > DatePicker1.SelectedDate &&
"AccessLMS" = ProductDropdown.Selected.AccessLMS,
"GameBrain" = ProductDropdown.Selected.GameBrain,
"EarlyPay" = ProductDropdown.Selected.EarlyPay,
"CoreHR" = ProductDropdown.Selected.CoreHR,
"SelectHR" = ProductDropdown.Selected.SelectHR,
"PeopleHR" = ProductDropdown.Selected.PeopleHR,
"AccessFinancials" = ProductDropdown.Selected.AccessFinancials,
"Expense" = ProductDropdown.Selected.Expense,
"SupplyChain" = ProductDropdown.Selected.SupplyChain,
"Document" = ProductDropdown.Selected.Document,
"Workspace" = ProductDropdown.Selected.Workspace,
"thankQ" = ProductDropdown.Selected.thankQ,
"Epos" = ProductDropdown.Selected.Epos,
"HospitalityPeople" = ProductDropdown.Selected.HospitalityPeople,
"Gamma" = ProductDropdown.Selected.Gamma,
"ProcureWizard" = ProductDropdown.Selected.ProcureWizard,
"Collins" = ProductDropdown.Selected.Collins,
"ComplianceManagement = ProductDropDown.Selected.ComplianceManagement,
"Dimensions" = ProductDropDown.Selected.Dimensions,
"MedicationManagement" = ProductDropDown.Selected.MedicationManagement,
"Mobizio" = ProductDropDown.Selected.Mobizio,
"ProNett" = ProductDropDown.Selected.ProNett,
"Capture" = ProductDropDown.Selected.Capture,
"EducationHR" = ProductDropDown.Selected.EducationHR,
"EducationFinance" = ProductDropDown.Selected.EducationFinance,
"EducationPeople" = ProductDropDown.Selected.EducationPeople,
"Teamseer" = ProductDropDown.Selected.TeamSeer,
"Rapidata" = ProductDropDown.Selected.Rapidata
)

[WarrenBelz]

Hi @Petecmsmith ,

Firstly good luck with Power Apps rendering the first bit, although it should work.

Second point - change the field name Date - it is a reserved word and cause ambiguity.

Lastly, your dropdown/combobox will only ever have one valid output - ProductDropdown.Selected.Value and this simply needs to match the field name you said was the same in all lists.

Filter(
   Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", AccessLMS,
      "GameBrain", GameBrain,
      "EarlyPay", EarlyPay,
      "CoreHR", CoreHR,
      "SelectHR", SelectHR,
      "PeopleHR", PeopleHR,
      "AccessFinancials", AccessFinancials,
      "Expense", Expense,
      "SupplyChain", SupplyChain,
      "Document", Document,
      "Workspace", Workspace,
      "thankQ", thankQ,
      "Epos", Epos,
      "HospitalityPeople", HospitalityPeople,
      "Gamma", Gamma,
      "ProcureWizard", ProcureWizard,
      "Collins", Collins,
      "ComplianceManagement", ComplianceManagement,
      "Dimensions", Dimensions,
      "MedicationManagement", MedicationManagement,
      "Mobizio", Mobizio,
      "ProNett", ProNett,
      "Capture", Capture,
      "EducationHR", EducationHR,
      "EducationFinance", EducationFinance,
      "EducationPeople", EducationPeople,
      "TeamSeer", TeamSeer,
      "Rapidata", Rapidata
      ),
   (
      TextSearchBox1.Text in Question ||
      TextSearchBox1.Text in Response ||
      TextSearchBox1.Text in Tags ||
      TextSearchBox1.Text in Feature
   ) &&
   "Date" > DatePicker1.SelectedDate &&
   YourMatchingFieldName = ProductDropdown.Selected.Value
)

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 again Warren - I wasn't aware that Date was a reserved word. I have changed to Entered throughout - that makes sense as elsewhere the Date field was causing problems with updating my data source.

Regarding the final part of the formula:

&&
   "Date" > DatePicker1.SelectedDate &&
   YourMatchingFieldName = ProductDropdown.Selected.Value
)

Obviously I amend "Date" to "Entered". Where you say matching field name, the dropdown simply is a list of the separate tables within my xls data source so this doesn't contain field names. In this case what should I have in here? The dropdown item property is simply: 

["AccessLMS","GameBrain","EarlyPay","CoreHR","PeopleHR","SelectHR","AccessFinancials","Expense","SupplyChain","Document","Workspace","Epos","HospitalityPeople","Gamma","thankQ","ProcureWizard","Collins","ComplianceManagement","Dimensions","MedicationManagement","Mobizio","ProNett","Capture","EducationHR","EducationFinance","EducationPeople","TeamSeer","Rapidata"]

[WarrenBelz]

Hi @Petecmsmith ,

What are you trying to match the drop-down value with in your list? I was simply providing the syntax.
 You said all the tables had the same types - maybe I should have said column heading.

[Petecmsmith]

Thanks, Warren, my apologies I'm not being too clear.

Essentially the user journey is:

Text entry box for search terms that returns results from my data source - an xls hosted in OneDrive that has around 20 tables all with the exact same structure and column names.

Below this, I have a dropdown where the user selects the table in order to filter results to a single table. The options in the dropdown are simply the names of the Tables.

Then I wanted to allow further filtering using my column that contains the date that the row was entered which exists in all tables. All results from a certain date.

This is an app to assist in answering questions on the various software products that my company produces. The data is all the questions and answers previously created over the years. Obviously, with software it evolves hence the date filter to remove results prior to a specific date when the functionality of the software might have changed due to an upgrade.

I hope that helps add some context and I really do appreciate your help and time on this.

[WarrenBelz]

Thanks @Petecmsmith ,

To summarise your requirements

Text entry box for search terms that returns results from my data source - an xls hosted in OneDrive that has around 20 tables all with the exact same structure and column names.

(
   TextSearchBox1.Text in Question ||
   TextSearchBox1.Text in Response ||
   TextSearchBox1.Text in Tags ||
   TextSearchBox1.Text in Feature
)

Below this, I have a dropdown where the user selects the table in order to filter results to a single table. The options in the dropdown are simply the names of the Tables.

Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", AccessLMS,
      "GameBrain", GameBrain,
      "EarlyPay", EarlyPay,
      "CoreHR", CoreHR,
      "SelectHR", SelectHR,
      "PeopleHR", PeopleHR,
      "AccessFinancials", AccessFinancials,
      "Expense", Expense,
      "SupplyChain", SupplyChain,
      "Document", Document,
      "Workspace", Workspace,
      "thankQ", thankQ,
      "Epos", Epos,
      "HospitalityPeople", HospitalityPeople,
      "Gamma", Gamma,
      "ProcureWizard", ProcureWizard,
      "Collins", Collins,
      "ComplianceManagement", ComplianceManagement,
      "Dimensions", Dimensions,
      "MedicationManagement", MedicationManagement,
      "Mobizio", Mobizio,
      "ProNett", ProNett,
      "Capture", Capture,
      "EducationHR", EducationHR,
      "EducationFinance", EducationFinance,
      "EducationPeople", EducationPeople,
      "TeamSeer", TeamSeer,
      "Rapidata", Rapidata
      )

Then I wanted to allow further filtering using my column that contains the date that the row was entered which exists in all tables. All results from a certain date.

 "Date" > DatePicker1.SelectedDate

These are already in there - are you saying you do not need further filters?

Your original request was

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.

What in addition to the current code do you need?

[Petecmsmith]

The part that is not working is the date picker bit. It will not refine the results based on the selected date being greater than or equal to the date in the column Entered. Once I have the syntax correct I assume I need to also ensure that the formatting for date in my data source columns is correct?

[WarrenBelz]

OK @Petecmsmith ,

Try this - I think I have the logic the right way around (Entered has to be after the selected date) - just reverse these if incorrect.

Filter(
   Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", AccessLMS,
      "GameBrain", GameBrain,
      "EarlyPay", EarlyPay,
      "CoreHR", CoreHR,
      "SelectHR", SelectHR,
      "PeopleHR", PeopleHR,
      "AccessFinancials", AccessFinancials,
      "Expense", Expense,
      "SupplyChain", SupplyChain,
      "Document", Document,
      "Workspace", Workspace,
      "thankQ", thankQ,
      "Epos", Epos,
      "HospitalityPeople", HospitalityPeople,
      "Gamma", Gamma,
      "ProcureWizard", ProcureWizard,
      "Collins", Collins,
      "ComplianceManagement", ComplianceManagement,
      "Dimensions", Dimensions,
      "MedicationManagement", MedicationManagement,
      "Mobizio", Mobizio,
      "ProNett", ProNett,
      "Capture", Capture,
      "EducationHR", EducationHR,
      "EducationFinance", EducationFinance,
      "EducationPeople", EducationPeople,
      "TeamSeer", TeamSeer,
      "Rapidata", Rapidata
      ),
   (
      TextSearchBox1.Text in Question ||
      TextSearchBox1.Text in Response ||
      TextSearchBox1.Text in Tags ||
      TextSearchBox1.Text in Feature
   ) &&
   DateDiff(
      DatePicker1.SelectedDate,
      Entered,
      Days
   )>0
)

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]

Great thanks Warren - really getting there now. Good news is that PowerApps likes the syntax and returns no errors or redlines - this rarely happens for me.

I did need to go through and reformat the Entered column in my tables. I used Date, UK, dd/mm/yyyy.

However, it doesn't seem to be taking notice of the date in the data source (I created an entry with a specific recent date and it would not return that result when I chose a date prior to that).

Is our formula requesting results where the date entered in the date picker is before the date in the Entered column of the table? As the datepicker is below my search box and dropdown the results are being compiled before the user reaches the datepicker - is there an argument to change the order or does that not really matter?