Searching on multiple columns in gallery

[kriggo15]

Preface: I'm pretty new to PowerApps. I have a gallery that is linked to a SharePoint List. I want to be able to search across all fields with one search box. I can get it working when searching for 1 field but when I try to modify it to search across multiple fields, it breaks.

Details:

SP Online List Name: Data Stewards

Fields: Area, Steward Name, Type of Steward

Working function when searching across 1 field: SortByColumns(Filter([@'Data Stewards'], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

What modifications need to be made to the above function to be able to use 1 search box and search across all 3 fields?

[Edwin-Abdalian]

Depends on your search terms, you would need to separate your search conditions by either And (&&) or Or||. for example:

SortByColumns(Filter([@'Data Stewards'], StartsWith(Title, TextSearchBox1.Text)) || StartsWith(SecondColumn, TextSearchBox2.Text)), "Title", If(SortDescending1, Descending, Ascending))

--------------------------------------------------------------------

Please Accept as Solution if this post answered your question so other members can find it. If you found this post helpful consider giving my post a Thumbs Up!

[kriggo15]

Not sure that I completely follow the example. There should only be one TextSearchBox and whatever text is entered in that box it should be compared to each of the three fields.

I thought maybe I could use Search instead of Filter and took a stab using the below function but it doesn't work either.

SortByColumns(Search(@'Data Stewards', TextSearchBox1.Text, Steward Name, [Type of Steward, Business Area], If(SortDescending1, Descending, Ascending))

[Edwin-Abdalian]

you can combine your search criteria and apply them all together. based on your example, I think you need to separate them with Or||

SortByColumns(Filter([@'Data Stewards'], StartsWith(Title, TextSearchBox1.Text)) || StartsWith(Title, TextSearchBox2.Text) II StartsWith(Title, TextSearchBox3.Text)), "Title", If(SortDescending1, Descending, Ascending))

[SeanTambling]

I think Kriggo15 was wanting to only use 1 search text box. Below is an example that is very similar to Edwin-Abdalian's but using only 1 textsearch box.

SortByColumns(Filter(PrintRequest, TextSearchBox1.Text in Title || TextSearchBox1.Text in BillTo || TextSearchBox1.Text in Status || TextSearchBox1.Text in DueDate), SortPriority, If(SortDescending1, Descending,Ascending))

[Drrickryp]

Hi @kriggo15 

There is another approach that you could use if your dataset is less than 2k items.  Create a Collection from it and then you can use Search().  The syntax would be simpler.  

In the OnVisible property of the screen:
ClearCollect(ds,YourDataSet)
In the Gallery Items propery:
Search(ds,Textinput1.Text,"Field1","Field2","Field3","Field4")

[notj]

@Drrickryp example is how we handle searching. It works great for us.

[kriggo15]

So maybe I need to take a step back and admit that through my trial and error and testing, I've messed up my gallery pretty well. I'm trying to take the suggested approach of creating a collection and searching on that. I've done some googling and have found out how to create a collection from a button click, but how do I do it without having to press a button? Ideally, I would have a gallery that is based on the 3 previously mentioned columns from my SharePoint list.  Note that the SharePoint list does contain more than those 3 columns.

After I create the collection, I assume I could use the below formula for searching. Would this look for the input string in any location within the 3 fields (not just starts with)?

Search(StewardList,TextSearchBox.Text,"Steward Name","Business Area", "Type of Steward")

Apologies for asking some probably easy questions, but I'm just beginning to learn powerapps. I appreciate your feedback and patience.

[notj]

You could utilize things like the OnVisible property of the screen the gallery is on, or before that could be the OnStart property of the App itself.

Your other question is regarding operators in the Search function. Here's a great article on PowerApps operators. https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators

You should be able to find what you need there using the "in" operator: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators#in-and-exactin-oper...

[v-yutliu-msft · Accepted solution]

Hi @kriggo15 ,

1)You could set this formula about creating collection to an action that will automatically perform.

For example, the app's OnStart, the screen's OnVisible, a combo box's OnChange,etc.

2)I suggest you use Filter function and in operator.

Try this:

Filter(StewardList,TextSearchBox.Text in Steward Name||TextSearchBox.Text in Business Area|| TextSearchBox.Text in Type of Steward)

Best regards,