Dropdown filter data then search

[Johno1]

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.

[BhargavPatel · Accepted solution]

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.

[ANB]

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

[Johno1]

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

[Johno1]

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.

[BhargavPatel]

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.