cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Simran13
Regular Visitor

Powerapp Gallery Filter Based on dropdown, multiple Buttons and search

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'))))

 

 

PowerApp screen 1.png

 

 

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))

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-qiaqi-msft
Community Support
Community Support

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)
)

 

Best Regards,
Qi

View solution in original post

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

View solution in original post

4 REPLIES 4
Ramole
Super User
Super User

@Simran13 

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

Thank you
If this post helps, then Click on the Thumbs Up below. and Accept it as the solution . to help others find helpful.
v-qiaqi-msft
Community Support
Community Support

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)
)

 

Best Regards,
Qi

@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 

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

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,315)