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.
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.
Solved! Go to Solution.
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
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)
)
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
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.
Hi @forbudt4u
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.
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)
)