cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Elle
Level: Powered On

Search with multiple words in multiple columns

With the text in a text balk I want to search for multiple words in multiple colums. 

 

If I put in the text balk 'apple, orange, strawberry', I want to find all the items where 'apple', 'orange' and 'strawberry' are in, but in all different sequences possible. All the three items need to be present in the result table.

 

Right now I try it with a filter function, which splits the string in two by finding the seperator in the text input and filtering on the left side and the right side of the seperator. In this way I can only search for two words, and not for three or even more. 

 

Does anybody have any idea how to search for mutliple words and that the results do not have the same sequences as searched for, but still al words in it?

 

 

18 REPLIES 18
Elle
Level: Powered On

search for multiple words in multiple columns

 

With the text in a text balk I want to search for multiple words in multiple columns.

 

If I put in the text balk 'apple, orange, strawberry', I want to find all the items where 'apple', 'orange' and 'strawberry' are in, but in all different sequences possible. All the three items need to be present in the result table.

 

Right now I try it with a filter function, which splits the string in two by finding the seperator in the text input and filtering on the left side and the right side of the seperator. In this way I can only search for two words, and not for three or even more. 

 

Does anybody have any idea how to search for mutliple words and that the results do not have the same sequences as searched for, but still al words in it?

Community Support Team
Community Support Team

Re: Search with multiple words in multiple columns

Would you please share some samples here?

Besides, if it is convenient, could you please share the filter formula that you used ?

 

The filter function for PowerApps is documented in the following article:

Filter, Search, and LookUp functions in PowerApps

For Multiple word search, we could take use of or to combine them together, for example:

Filter (Table, Or(StartsWith(Column, StartText),

                      or (StartsWith(Column1, StartText1),

                           StartsWith(Column2, StartText2))

                   )

           )

To search on Multiple column, you could switch to Search () function, which only accepts one search string at a time:

Search( Table, SearchString, Column1, Column2, ...)

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Elle
Level: Powered On

Re: Search with multiple words in multiple columns

Thank you for your reply.

 

I use the filter function, the problem is that I only have one textbox as my search field. In this textbox I want to fill in multiple words and want to filter the data on all the words. So if I put three words in this specific textbox, I only want results with all these three words in it, and more specific: also results where the words are not in the same sequence as in the textbox. Right now, I solved it for two words, with splitting the text in the textbox with Right and Left, as you can see in the code. But, I want it to work for more words. Maybe some splitting function?

 

Filter(Table, Left(Text.Text, If(IsBlank(Find(" ",Text.Text)),Len(Text.Text), Find(" ",Text.Text)-1)) in Column1 || Left(Text.Text, If(IsBlank(Find(" ",Text.Text)), Len(Text.Text), Find(" ",Text.Text)-1)) in Column2, Right(Text.Text, Len(Text.Text)-Find(" ",Text.Text)) in Column1 || Right(SearchText.Text, Len(SearchText.Text)-Find(" ",SearchText.Text)) in Column 2

 

So the question is, can I split my textbox in different words, which I can use in my filter function? (More than two words)

 

Thanks in advance.

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Search with multiple words in multiple columns

You can use a few functions to do that. Assuming that your table is called "tbl", the text column you want to search is called "Text", and the text input control where you want to type the words is called "SearchText", here is what you would need to do:

Filter(
    AddColumns(
        tbl,
        "present",
        Sum(
            ForAll(
                Split(SearchText.Text, " "),
                If(IsBlank(Find(Result, Text)), 0, 1)
            ),
            Value
        )
    ),
    present = CountRows(Split(SearchText.Text, " "))
)

Let's break this expression in its parts. Here we're adding a new column (via AddColumns) to the expression, that will find how many of the words in the search text are present in each of the items of the table:

    AddColumns(
        tbl,
        "present",
        ...
    ),

To calculate that, we're using the Sum function, that will add all values from the table passed to it:

        Sum(
            <table>,
            <expression>
        )

To create that table, we will use the ForAll function, which executes a function for every word of the search text (which was extracted using the Split function), trying to Find the word of the search text in the Text column of your table. To check whether the word is found, we use the IsBlank function, and an If condition to return 1 if the word is found. At the end, we take the Value (the name of the column returned by ForAll) and pass it as the second parameter of the Sum function, so that the column "present" will have the total number of words from the search text that is found in the table record. 

        Sum(
            ForAll(
                Split(SearchText.Text, " "),
                If(IsBlank(Find(Result, Text)), 0, 1)
            ),
            Value
        )

Finally, we can use the outer Filter expression to only return the items from your table whose "present" count is equal to the number of words that are present in the search text.

Filter(
    AddColumns(...),
    present = CountRows(Split(SearchText.Text, " "))
)

Notice that this expression returns only items that have all words from the search text; if you want it to return items that have any of the words, you just need to change the Filter condition to "present > 0".

 

In short, this should do what you want, but as you can see it's quite verbose. If we had some function such as All (or Any) that would return true only if all (or one or more) of the values is true it would make this easier to write. Please consider creating a new feature request in the PowerApps Ideas board for this.

Bosschgn
Level: Powered On

Re: Search with multiple words in multiple columns

Hi Carlos, Thanks. Very useful. Your solution only searches on one column, ie in your example Text. Is there also a possibility to search on multiple columns ?
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Search with multiple words in multiple columns

If you want to search in multiple columns, you can have something like the expression below:

Filter(
    AddColumns(
        tbl,
        "present",
        Sum(
            ForAll(
                Split(SearchText.Text, " "),
                If(IsBlank(Find(Result, Text)) And IsBlank(Find(Result, TextColumn2)), 0, 1)
            ),
            Value
        )
    ),
    present = CountRows(Split(SearchText.Text, " "))
)

Where it will search on both the column named 'Text' and in the column named 'TextColumn2'.

Bosschgn
Level: Powered On

Re: Search with multiple words in multiple columns

Carlos,

I have used your formula, which works perfect in a browsegallery.  Really great. It makes it a very powerful search.

But  : when i move to an editgallery, it doesn't work anymore.  Most likely because I added a column "present".  

optoluctor
Level: Powered On

Re: Search with multiple words in multiple columns

Carlos,

Can you deternine why selected items which have populated the Item Gallery fail to populate the Display Form?

 

The error displayed on the Display Form's 'Item' setting when using Gallery.Selected is:

"Invalid formula. Expected a value compatible with 'DataSource'."

 

*****************************************************

The Formula for my Gallery 'Items' setting (which works amazingly, thank you!) is as follows:

SortByColumns(
Filter(
    AddColumns(
        CollectionACItems,
        "Result",
        Sum(
            ForAll(
                Split(TextSearchBoxACItems1.Text, " "),
               If(Or(ToggleNavItemNo1.Value=false, IsBlank(Find(Lower(Result), Lower('NAV No.')))) And Or(ToggleVendorItemNo1.Value=false, IsBlank(Find(Lower(Result), Lower('Vendor Item No.'))) And IsBlank(Find(Lower(Result), Lower('NAV Vendor Item No.'))) And IsBlank(Find(Lower(Result), Lower('Alternate Vendor Item No.')))) And Or(ToggleDescription1.Value=false, IsBlank(Find(Lower(Result), Lower('NAV Description'))) And IsBlank(Find(Lower(Result), Lower('Vendor Sheet Description')))) And Or(ToggleVendorName1.Value=false, IsBlank(Find(Lower(Result), Lower('Vendor Name')))) And Or(ToggleVendorNo1.Value=true, IsBlank(Find(Lower(Result), Lower('Vendor No.')))), 0, 1)
            ),
            Value
        )
    ),
    Result = CountRows(Split(TextSearchBoxACItems1.Text, " "))
)
,
"No. Sort", Ascending, "NAV No.", If(SortDescending1, Descending, Ascending), "Vendor Item No.", If(SortDescending1, Descending, Ascending))

 

*****************************************************

So, when I Navigate(DetailScreen, ScreenTransition.None) from a selected Gallery Item all of the Display Form's fields are now blank.

 

*****************************************************

The formula used for populating the Gallery previously which successfully populated the Display Form with data from a selected Gallery Item was:

SortByColumns(
        Filter(CollectionACItems,
                    If(ToggleNavItemNo1.Value=true, TextSearchBoxACItems1.Text in 'NAV No.') ||
                    If(ToggleVendorItemNo1.Value=true, TextSearchBoxACItems1.Text in 'Vendor Item No.' || TextSearchBoxACItems1.Text in 'NAV Vendor Item No.' || TextSearchBoxACItems1.Text in 'Alternate Vendor Item No.') ||
                    If(ToggleDescription1.Value=true, TextSearchBoxACItems1.Text in 'NAV Description' || TextSearchBoxACItems1.Text in 'Vendor Sheet Description') ||
                    If(ToggleVendorName1.Value=true, TextSearchBoxACItems1.Text in 'Vendor Name') ||
                    If(ToggleVendorNo1.Value=true, TextSearchBoxACItems1.Text in 'Vendor No.'),
                    If(ToggleNavItemNo1.Value=true, TextSearchBoxACItems2.Text in 'NAV No.') ||
                    If(ToggleVendorItemNo1.Value=true, TextSearchBoxACItems2.Text in 'Vendor Item No.' || TextSearchBoxACItems2.Text in 'NAV Vendor Item No.' || TextSearchBoxACItems2.Text in 'Alternate Vendor Item No.') ||
                    If(ToggleDescription1.Value=true, TextSearchBoxACItems2.Text in 'NAV Description' || TextSearchBoxACItems2.Text in 'Vendor Sheet Description') ||
                    If(ToggleVendorName1.Value=true, TextSearchBoxACItems2.Text in 'Vendor Name') ||
                    If(ToggleVendorNo1.Value=true, TextSearchBoxACItems2.Text in 'Vendor No.')),                     
"No. Sort", Ascending, "NAV No.", If(SortDescending1, Descending, Ascending), "Vendor Item No.", If(SortDescending1, Descending, Ascending))

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Search with multiple words in multiple columns

The items in the gallery come from a virtual table, created by AddColumns, from the (I guess, based on the name) a local collection (CollectionACItems). The item on a form should be an item from the same data source that is in the 'DataSource' property of the form. If the CollectionACItems was created from the same data source, then you should be able to retrieve the corresponding item from that data source, using a LookUp call. For example, if the item has an "Id" field that is unique for each item in that data source, then you may be able to use it to retrieve the item:

Form.Item: LookUp(<DataSourceName>, Id = Gallery.Selected.Id)