cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
forbudt4u
Resolver I
Resolver I

Dynamic Filter Challenges (Revisit)

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.  

2 ACCEPTED SOLUTIONS

Accepted Solutions
StalinPonnusamy
Super User
Super User

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

View solution in original post

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)

)

 

 

View solution in original post

5 REPLIES 5
StalinPonnusamy
Super User
Super User

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

@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
Super User
Super User

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

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

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)

)

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,476)