Unanswered

Searching on multiple columns in gallery

(2) Share

Report

Posted on by kriggo15

526

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?

Categories:

Building Power Apps

I have the same question (0)

All responses (16)

Answers (1)

Edwin-Abdalian 641 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
kriggo15 526 on at

Like (0)

Report

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))

Was this reply helpful? Yes No
Edwin-Abdalian 641 on at

Like (0)

Report

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))

Was this reply helpful? Yes No
SeanTambling 176 on at

Like (0)

Report

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))

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (4)

Report
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")

Was this reply helpful? Yes No
notj 782 on at

Like (0)

Report

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

Was this reply helpful? Yes No
kriggo15 526 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
notj 782 on at

Like (1)

Report

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-operators

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft on at

Like (5)

Report
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,

Was this reply helpful? Yes No
kriggo15 526 on at

Like (0)

Report

I appreciate everyone's feedback/suggestions. I got it working withe name field but when I try with Type or Business Area, it says that the type is unsupported for evaluation. All 3 of these fields are single lines of text in the SharePoint list.

Update - Just got this working. Apparently I was choosing the wrong field.

Was this reply helpful? Yes No