As many readers will know, the Search() function in Power Apps is very useful for filtering a table for rows where text within a column or a set of columns contains a search term.
This is especially useful for data sources where where the Search() function is delegable, such as Azure SQL.
If Search() is delegable, it can handle a bit of nesting and remain so. You can wrap Search() around another Search() and around another Search() and so on, until you reach the OData node count limit. The number of levels you can achieve depends on how many columns you’re searching within.
The outcome of this is you can take the contents of a text input box, split it on the space character and use the 1st, 2nd and 3rd (etc) item in the resulting array as search terms throughout many nested Search() functions, and (at least in the case of Azure SQL, as that’s all I’ve tested with), it’s delegable.
In this example we’re searching a SQL view that’s been filtered by a couple of columns already (Date and a Boolean column) using the first, second and third word in a Text Input:
This works by taking a string from the Text Input, splitting on a space character to create an array, then selecting individual items in the array with FirstN() and Last() to feed into the successive Search() functions.
If you only type one word into the search box, each of those 3 formulas output the same thing, so you’re basically searching for the same word within the search results of the same word. You therefore get all the rows where the columns contain that word (like a normal, single Search() function). If you only type two words, the outermost Search() is doing that for the results that come out of the second Search().
If the input box is blank, it returns the whole data source of the innermost Search().
Basically, the experience to the user is good – they get the results they expect for any number of search terms from 1 to 3.
So, to introduce a bit of user education and control into the piece, you can have a label on the screen with the Visible property like this:
And the Text property like this:
The result:
So there’s a little byte-sized tip for anyone who might be looking for a way to do this in Power Apps.
William's IT Blog 