Dynamic Filter Challenges (Revisit)

[forbudt4u]

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.  

[StalinPonnusamy · Accepted 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

[forbudt4u]

@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. 

[StalinPonnusamy]

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

[forbudt4u]

@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.  

[forbudt4u · Accepted solution]

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)

)