Dynamic Filter Challenges (Revisit)

(0) Share

Report

Posted on by forbudt4u

264

Hello,

I'm revisiting a filtering issue I've had with my app that I haven't been able to figure out. I originally posted a question to the topic a few months back and thought I would receive help, but it didn't happen. Anyways, I'm still in the same boat and having the ability to dynamically filter my gallery WITHOUT the use of a collection would improve the user friendliness of it.

What I have is a Gallery named SearchGallery_2 with the below Items coding. This coding looks at my scan button, text input field and a checkbox to filter what I'm looking for, and does a great job at it as is.

If(PendingCheckbox_1.Value=false,

SortByColumns(
 FirstN(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) ||
 StartsWith(LocationBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 2000),
 "CategoryBK",Ascending,
 "Holder",Ascending,
 "ManufacturerBK",Ascending),

SortByColumns(
 FirstN(
 Filter('All Asset List', 
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) && 
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) ||
 StartsWith(LocationBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 2000),
 "TransferStatus", Ascending,
 "CategoryBK",Ascending,
 "Holder",Ascending,
 "ManufacturerBK",Ascending)
)

I also have a filter button that produces a pop-up with two dropdowns along with an Apply Filter Button and Clear Filter Button.

WASP Admin Portal Filter.png

FilterByDD Dropdown Items:

["Category","Holder","Location","Manufacturer","Model","Status"]

FilterOptionDD Dropdown Items:

If(FilterByDD.SelectedText.Value="Category",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 CategoryBK),Result),
If(FilterByDD.SelectedText.Value="Holder",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 Holder),Result),
If(FilterByDD.SelectedText.Value="Location",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 LocationBK),Result),
If(FilterByDD.SelectedText.Value="Manufacturer",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 ManufacturerBK),Result),
If(FilterByDD.SelectedText.Value="Model",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 ModelBK),Result),
If(FilterByDD.SelectedText.Value="Status",
 Sort(
 Distinct(
 Filter('All Asset List',
 !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && 
 (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) &&
 (IsBlank(SearchTextBox_1.Text) || 
 StartsWith('Transfer Status', SearchTextBox_1.Text) || 
 StartsWith('ScanTag#', SearchTextBox_1.Text) || 
 StartsWith(Holder, SearchTextBox_1.Text) || 
 StartsWith(CategoryBK, SearchTextBox_1.Text) || 
 StartsWith(ManufacturerBK, SearchTextBox_1.Text) || 
 StartsWith('Serial#', SearchTextBox_1.Text))),
 Status.Value),Result)
 )
 )
 )
 )
 )
)

What I'm trying to accomplish (again, without using a collection):

1. Utilize the SearchGallery_2 Items AS IS

2. When clicking the Apply Filter (FilterAPPLYBTN) Button, further filter the SearchGallery_2 items based on the Filter By (FilterByDD) and Option (FilterOptionDD) dropdown selections.

3. When clicking the Clear Filter (FilterCLEARBTN) Button, restore the original SearchGallery_2 items before applying the additional filter.

I hope I provided all the info needed to help give the complete picture of what I have and what I'm trying to do. I'm hoping someone can help solve this riddle.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Sort by

Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @forbudt4u
We can change your gallery filter like

SortByColumns( FirstN( Filter('All Asset List', !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || 'ScanTag#'=SearchPopupBarcodeScanLbl_1.Text) && //Adding Other 2 filter with && Condition like (IsBlank(FilterByDD.Selected) || IsEmpty(FilterByDD.Selected) || ColumnName.Value =FilterByDD.Selected.Value) && (IsBlank(FilterOptionDD.Selected) || IsEmpty(FilterOptionDD.Selected) || ColumnName.Value =FilterOptionDD.Selected.Value) && (IsBlank(SearchTextBox_1.Text) || StartsWith('Transfer Status', SearchTextBox_1.Text) || StartsWith('ScanTag#', SearchTextBox_1.Text) || StartsWith(Holder, SearchTextBox_1.Text) || StartsWith(CategoryBK, SearchTextBox_1.Text) || StartsWith(ManufacturerBK, SearchTextBox_1.Text) || StartsWith(LocationBK, SearchTextBox_1.Text) || StartsWith('Serial#', SearchTextBox_1.Text))), 2000), "CategoryBK",Ascending, "Holder",Ascending, "ManufacturerBK",Ascending)

This filter applies only it has value.

Clear Dropdown by assigning "" as default item when you apply a clear filter or other possible places

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!
@StalinPonnusamy
Thank you for the guidance. I have applied your recommendation and it is not working. I'm not getting an error with the code, but I think it may be a combination of my interpretation of it and my explanation/your understanding of what I'm trying to accomplish.

First, I'm a little confused with the last OR statements in your recommendation:
ColumnName.Value =FilterByDD.Selected.Value
ColumnName.Value =FilterOptionDD.Selected.Value
I'm not sure what the ColumnName.Value is being used for and it's not proper syntax, but I'll offer the following. The Column Name will always be the first/top Filter By Dropdown (FilterByDD) selection, and the second/bottom Option Dropdown (FilterOptionDD) selection will be what you want to filter the column by. For example, if I type Joe Smith in the search input that the Gallery is looking at and it gives me 3 results, but I want to find out what items in Joe's list are Manufactured by Milwaukee... I would open my filter, chose Manufacturer in the Filter By Dropdown, choose Milwaukee in the Option Dropdown, then click the Apply Filter Button to a list of Joe's items manufactured by Milwaukee. If I want to go back to the original gallery showing Joe's items, I would click Clear Filters and I would have a gallery with all of Joe's items again.

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @forbudt4u
OR condition means it returns the first satisfying condition. That means no specific filter is applied if the dropdown is empty or blank
ColumnName which you are trying to apply for 2 columns (.Value) is may not need based on data type

Was this reply helpful? Yes No
forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!

@StalinPonnusamy
I'm not confused about what an OR statement is, I'm confused because only the top drowdown (Filter By) is looking at a column... the 2nd dropdown is the filtered result of the respective selected column. In both cases, the dropdowns themselves are telling the code what's already chosen. That's where my confusion lies.

Was this reply helpful? Yes No
Verified answer

forbudt4u 264 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks to @StalinPonnusamy on the side, he was able to assist with this to get it working. He used the sData function to achieve this... below is the code:
If(PendingCheckbox_1.Value=false, SortByColumns( FirstN( With( { sData: Filter( 'All Asset List', !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || Title=SearchPopupBarcodeScanLbl_1.Text) && (IsBlank(SearchTextBox_1.Text) || StartsWith(TransferStatus, SearchTextBox_1.Text) || StartsWith(Title, SearchTextBox_1.Text) || StartsWith(Holder, SearchTextBox_1.Text) || StartsWith(CategoryBK, SearchTextBox_1.Text) || StartsWith(ManufacturerBK, SearchTextBox_1.Text) || StartsWith(LocationBK, SearchTextBox_1.Text) || StartsWith(Serial_x0023_, SearchTextBox_1.Text)) ) }, If( !IsBlank(FilterByDD.Selected.Value) && FilterByDD.Selected.Value = "Category", Filter( sData, CategoryBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Location", Filter( sData, LocationBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Manufacturer", Filter( sData, ManufacturerBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Model", Filter( sData, ModelBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Holder", Filter( sData, Holder = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Status", Filter( sData, Status.Value = FilterOptionDD.Selected.Result ), sData ) ) ) ) ))), 2000), "CategoryBK",Ascending, "Holder",Ascending, "ManufacturerBK",Ascending), SortByColumns( FirstN( With( { sData: Filter( 'All Asset List', !(IsBlank(SearchTextBox_1.Text) && IsBlank(SearchPopupBarcodeScanLbl_1.Text)) && (IsBlank(SearchPopupBarcodeScanLbl_1.Text) || Title=SearchPopupBarcodeScanLbl_1.Text) && (IsBlank(SearchTextBox_1.Text) || StartsWith(TransferStatus, SearchTextBox_1.Text) || StartsWith(Title, SearchTextBox_1.Text) || StartsWith(Holder, SearchTextBox_1.Text) || StartsWith(CategoryBK, SearchTextBox_1.Text) || StartsWith(ManufacturerBK, SearchTextBox_1.Text) || StartsWith(LocationBK, SearchTextBox_1.Text) || StartsWith(Serial_x0023_, SearchTextBox_1.Text)) ) }, If( !IsBlank(FilterByDD.Selected.Value) && FilterByDD.Selected.Value = "Category", Filter( sData, CategoryBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Location", Filter( sData, LocationBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Manufacturer", Filter( sData, ManufacturerBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Model", Filter( sData, ModelBK = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Holder", Filter( sData, Holder = FilterOptionDD.Selected.Result ), If( FilterByDD.Selected.Value = "Status", Filter( sData, Status.Value = FilterOptionDD.Selected.Result ), sData ) ) ) ) ))), 2000), "TransferStatus", Ascending, "CategoryBK",Ascending, "Holder",Ascending, "ManufacturerBK",Ascending) )

Was this reply helpful? Yes No