Filter and Search based on DropDown selection

[ElMeSaFeR]

Hello, 

as the file attached I need to set a filter and search on a Gallery based on a dropdown list 

 

I have SQL DB through a gateway access 

when the user writes a text in the search box then choose "FIRSTNAME" column the PowerApps start to filter and search on this column and the same way in the 2nd column "LASTNAME" .

Thanks

[mr-dang]

Hi @ElMeSaFeR,

It sounds like you want to filter your SQL table according to what's typed into TextSearchBox2, and you want it to check the column equal to what's selected in Dropdown1.

I can clarify a few things before I present a solution:

• When you type the name of a control between quotation marks, its name is treated as text and not its content. That's why the dropdown is not recognized.
• Search() is not able to dynamically change the column you want to search HOWEVER--it's even better than that
  It can search that text string against every column you specify, separated by commas.
• Search() understands that it will show everything if the search string is blank, so you do not need a condition. Isn't it great?

So here's one way that you can revise your formula:

Search('[dbo].[datasource]',
    TextSearchBox2.Text,
    "FIRSTNAME",
    "LASTNAME"
)

This means, "Search your datasource for the rows where whatever is typed into TextSearchBox2 is found in the FIRSTNAME or LASTNAME columns."

Let me know if this was helpful with a thumbs up.

Mr. Dang

_______

[ElMeSaFeR]

Hi @mr-dang

Thanks for your fast response ..

is that mean the only way to use Search formulas?

I'm also looking to include "Filter" in same formula to have an accurate search for what exactly user looking for 

[mr-dang · Accepted solution]

Hi @ElMeSaFeR,

You can combine Sort, Search, Filter and a whole lot more  

Here's the sequence you'll find in sample apps. It is a good common pattern to understand:

SortByColumns(
    Search(
        Filter('[dbo].[datasource]',
            condition1,
            condition2
        ),
    TextSearchBox2.Text,
    "FIRSTNAME",
    "LASTNAME"
    ),
"LASTNAME",Ascending,
"FIRSTNAME",Ascending
)

Read this formula starting from inside and going outward:

Filter the datasource if a row meets conditions 1 and 2.

Take that result and search if it has TextSearchBox2.Text in either the FIRSTNAME or LASTNAME columns.

From that new result, sort them by LASTNAME, then FIRSTNAME.

Let me know if this was also helpful with a thumbs up.

Mr. Dang

________

[ElMeSaFeR]

@mr-dang

I appreciate that 

please if you could give me some examples of these conditions 

I've still had some confusions with search and filter conditions 

[mr-dang]

@ElMeSaFeR,

A filter will check if a row has matching data in a column

Filter('[dbo].[datasource]',
    AGE>=25,
    GENDER="M"
)

The filter above will show all records where the AGE column has a value that is greater than or less than 25, AND where the gender is listed as male. If either one is not true, the record will not be returned. The general pattern is to compare a column against something else, whether it's a number like 25, a text string like "M", or the value in a control like a dropdown. Please search the forum as there are lots of examples of these.

You can add more conditions in the filter function with a comma. You also write Or statements--check out the "Operators" section at the top of the Formula Reference page for more information.

You can think of the Search function as a specific kind of filter. It checks the condition of if a text string is in a column or multiple columns. The equivalent Filter statement for the search would be something like:

Filter('[dbo].[datasource]',
    Or(
        TextSearchBox2.Text in FIRSTNAME,
        TextSearchBox2.Text in LASTNAME
    )
)

This means, "Filter the datasource where what you typed into TextSearchBox2 is in the FIRSTNAME or LASTNAME." But you'll find that the Search() function is designed for this purpose so it works better as you saw in the syntax I described in my first response.

So Filter and Search work together in many scenarios.

Let me know if this was helpful with a thumbs up.

Mr. Dang

_______

[ElMeSaFeR]

@mr-dang

ok it's working with me now 

I'm also thinking to use "IF" for the DropDown menu 

before starting to search looking for the DropDown value then looking for the column data to show the results and so on 

I'll try this method also then let you know what's the update 

Thanks so much, I always see your tutorials .. great work and keep it up always, please

Regards