Powerapp Gallery Filter Based on dropdown, multiple Buttons and search

[Simran13]

I want to filter the IT Asset gallery by Asset Type then status,

Requirement -  List IT Assets by "Asset Type" from the dropdown ( by default the dropdown value will be set to "All" which should show all Asset Types)   

NOTE -'Asset Type' column choices in SP are only {Monitors, Laptops, Desktops, Televisions, Tablet, Accessory, Smartphone }

I collected these choices and added an "All" option.   (collection name = ITAsstypes) 

and then apply Status filter by clicking buttons to see which of these assets are  "Available", "Reserved" or "In use" assets ) 

And to Search the items in this filtered gallery

Added four buttons for each status type

Buttons                       On Select Property

"Available"                  Set(varISAvailable,true);Set(varISReserved,false);Set(varISInuse,false);Set(varISAll,false)

"Reserved"                  Set(varISAvailable,false);Set(varISReserved,true);Set(varISInuse,false);Set(varISAll,false)

"In use"                       Set(varISAvailable,false);Set(varISReserved,false);Set(varISInuse,true);Set(varISAll,false)

and "All"                     Set(varISAvailable,false);Set(varISReserved,false);Set(varISInuse,false);Set(varISAll,true)

Each button is suppose to represent "Status" of the items in SP list. 

Gallery > Items Property formula   

( This formula is working on the buttons and filtering the Gallery by status but I am getting no results when I am trying to embed dropdown and search filter. 

Gallery Filtering by Buttons  This formula worked and it filters gallery on button 

If(varISAvailable=true,Filter('IT Assets',Status.Value="Available"),If(varISReserved=true,Filter('IT Assets',Status.Value = "Reserved"),If(varISInuse=true,Filter('IT Assets',Status.Value = "In Use"),If(varISAll=true,' IT Assets'))))

but when I tried adding dropdown and search filter using this formula- its not working 

SortByColumns
(Search(If(Dropdown1_4.Selected.Result="All",'IT Assets',

If(varISAvailable=true,Filter('IT Assets',Status.Value="Available"),

If(varISReserved=true,Filter('IT Assets',Status.Value = "Reserved"),

If(varISInuse=true,Filter('IT Assets',Status.Value = "In Use"),

If(varISAll=true,Filter('IT Assets',Status.Value<>""),

If(Dropdown1_4.Selected.Result<>"All",Filter('IT Assets','Asset type'.Value=Dropdown1_4.Selected.Result)))))))

,TextSearchBox1_4.Text,"Asset Name"),
"Asset Name",
If(SortDescending1,Descending,Ascending))

[Ramole]

@Simran13 

Please see if this helps you https://www.youtube.com/watch?v=XMopL0r8k3k 

[v-qiaqi-msft · Accepted solution]

Hi @Simran13,

Do you want to combine the Search() with Filter()?

Could you please share a bit more about the scenario?

I think you should modify your formula as below:

SortByColumns(
    If(
        Dropdown1_4.Selected.Value = "All",
        Search(
            If(
                varISAvailable = true,
                Filter(
                    'IT Assets',
                    Status.Value = "Available"
                ),
                If(
                    varISReserved = true,
                    Filter(
                        'IT Assets',
                        Status.Value = "Reserved"
                    ),
                    If(
                        varISInuse = true,
                        Filter(
                            'IT Assets',
                            Status.Value = "In Use"
                        ),
                        If(
                            varISAll = true,
                            Filter(
                                'IT Assets',
                                Status.Value <> ""
                            )
                        )
                    )
                )
            ),
            TextSearchBox1_4.Text,
            "Asset Name"
        ),
        If(
            Dropdown1_4.Selected.Result<> "All",
            Search(
                Filter(
                    'IT Assets',
                    'Asset type'.Value = Dropdown1_4.Selected.Result
                ),
                TextSearchBox1_4.Text,
                "Asset Name"
            )
        )
    ),
    "Asset Name",
    If(SortDescending1,Descending,Ascending)
)

[Simran13]

@v-qiaqi-msft Thank you for the updated formula it works partly same as before if I change dropdown value to something else other than "ALL" the status button filters don't work. 

Basically, what I wanted to was to filter list; when user comes to this screen - show full list regardless of asset type or status.   

and if user changes dropdown value it filters the list by that the asset type and if user then clicks on any of the status buttons it filters the list further by status.  (Cascading filter )  and search is there so user can search the list 

[Simran13 · Accepted solution]

@v-qiaqi-msft    This worked 

I did this on buttons   =set(varButtonValue,"Available") and so on for each button type and then the following formula worked.  

If(
Dropdown1_2.Selected.Result = "All" && varButonValue = "",
COLL_IT_ASSET,
If(
Dropdown1_2.Selected.Result <> "All" && varButonValue = "",
Filter(
COLL_IT_ASSET,
Dropdown1_2.Selected.Result exactin 'Asset type'.Value
),
If(
Dropdown1_2.Selected.Result = "All" && varButonValue <> "",
Filter(
COLL_IT_ASSET,
varButonValue exactin Status.Value
),
If(
Dropdown1_2.Selected.Result <> "All" && varButonValue <> "",
Filter(
COLL_IT_ASSET,
varButonValue exactin Status.Value && Dropdown1_2.Selected.Result exactin 'Asset type'.Value
)
)
)
)
)