Answered

Dropdown filter data then search

(0) Share

Report

Posted on by JC-18030151-0

I have a powerapp created from a sharepoint list 'MCN_Plants5'. The following formula is attached to the Gallery: SortByColumns(Filter([@MCN_Plants5], StartsWith(Species, TextSearch.Text) || StartsWith('Common Name', TextSearch.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)) as per attached 1. This works OK but I have a delegation warning on searching by 'Common Name', which doesn't bother me.

I'd like to add a dropdown filter: Distinct(MCN_Plants5,'In the Nursery'), to search if the plants are in the nursery or not. How do I amend the gallery formula to accomodate the dropdown list filtering whether the plants are in the nursery ("Yes"} or not("No) please.

I am a relative newby, but I try hard. Thanks for your help.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Verified answer

BhargavPatel 660 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Please try the code below. Replace "Dropdown1" with the name of your dropdown control.
SortByColumns( Filter( [@MCN_Plants5], StartsWith( Species, TextSearch.Text ) || StartsWith( 'Common Name', TextSearch.Text ) || TextSearch.Text = Blank(), 'In the Nursery' = Dropdown1.Selected.Value || IsBlank(Dropdown1.Selected.Value) ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )
I added conditions to show all items in the gallery if the search box is empty or nothing is selected from the dropdown.

Please accept this as a solution if it helped resolve your issue.

Was this reply helpful? Yes No
ANB 7,252 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Johno1 Does 'In the Nursery' is choice column? If yes, then try this:
SortByColumns(Filter([@MCN_Plants5], 'In the Nursery'.Value = dropdownName.Selected.Value || IsBlank(dropdownName.Selected.Value), StartsWith(Species, TextSearch.Text) || StartsWith('Common Name', TextSearch.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
If , 'In the Nursery' is not choice column then below code should work:
SortByColumns(Filter([@MCN_Plants5], 'In the Nursery' = dropdownName.Selected.Value || IsBlank(dropdownName.Selected.Value), StartsWith(Species, TextSearch.Text) || StartsWith('Common Name', TextSearch.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))
-----------------------------------------------------------------------------------------------------------------------------

I hope this helps.

Please click Accept as solution ✅ if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs up.👍

Thanks,
ANB

Was this reply helpful? Yes No
JC-18030151-0 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Bhargav. You have saved me a lot of trouble.

Was this reply helpful? Yes No
JC-18030151-0 22 on at

Like
a
(0)

Report
Copy link

Link copied!

Bhargav, thank you for solving my dropdown and search issue. Is it possible to include the "All" possibilty in the dropdown please? I am not good with formulae in a gallery. Thanks again for your help to date. John.

Was this reply helpful? Yes No
BhargavPatel 660 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Johno1. Here are the two ways to achieve that

Option 1. If the only options you will need in the dropdown are "All", "Yes", or "No" then:
Change the dropdown Items property to

["All", "Yes", "No"]

Option 2.
Set the OnVisible property of the screen to

ClearCollect(inNursery,["All"],Distinct(Plants,'In the Nursery'))

Change the dropdown Items property to

inNursery

After Option 1 or 2.
Change the Gallery Items property to

SortByColumns( Filter( [@MCN_Plants5], StartsWith( Species, TextSearch.Text ) || StartsWith( 'Common Name', TextSearch.Text ) || TextSearch.Text = Blank(), 'In the Nursery' = Dropdown1.Selected.Value || IsBlank(Dropdown1.Selected.Value) ||Dropdown1.Selected.Value = "All" ), "Title", If( SortDescending1, SortOrder.Descending, SortOrder.Ascending ) )

Please like the reply and accept it as a solution if it helps resolve your issue.

Was this reply helpful? Yes No