cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kriggo15
Post Patron
Post Patron

Searching on multiple columns in gallery

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

16 REPLIES 16
Edwin-Abdalian
Solution Supplier
Solution Supplier

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!

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

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

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
Super User
Super User

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

 

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

kriggo15
Post Patron
Post Patron

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.

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

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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,515)