Applying further filters to my results

(0) Share

Report

Posted on by 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 🙂

Categories:

Building Power Apps

All responses (29)

Answers (2)

Sort by

GarethPrisk 2,828 on at

Like
a
(0)

Report
Copy link

Link copied!
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 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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 223 on at

Like
a
(0)

Report
Copy link

Link copied!

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 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Anyone? Anyone? Bueller? Anyone?
WarrenBelz 156,774 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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 156,774 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

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 57 on at

Like
a
(0)

Report
Copy link

Link copied!

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?
WarrenBelz 156,774 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
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.