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
Solved! Go to 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
________
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:
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
_______
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
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
________
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
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
_______
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