cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobbybob
Helper II
Helper II

How to search multiple columns and filter

I have a sharepoint list connected to my Power App. This list has multiple columns for each "title" within the list. I'd like to be able to sort by the "title" and search in the search box based on multiple columns. This is the original formula I had to search and filter but it only searched through the "title" column.

SortByColumns(Filter([@'Filter Dates'], StartsWith(Title, TextSearchBox1.Text)), "Title", If(SortDescending1, Descending, Ascending))

 

I can't get it to search multiple columns and sort at the same time. Does anyone have any suggestions on how to do this?

1 ACCEPTED SOLUTION

Accepted Solutions

I still could not get the Search function to work. Instead I did a Filter formula and got it to work. I'll never have more than 2000 rows so this formula should be fine. 

SortByColumns(Filter([@'Filter Dates'],TextSearchBox1.Text in Title || TextSearchBox1.Text in Location || TextSearchBox1.Text in Modified), "Title", If(SortDescending1,Descending,Ascending))

This is the second app I've tried to get the Search function to work and cannot. I use the template: 

 

Search(Customers, SearchInput.Text, "Name")

 

within my formula and I can never get it to run. No idea why or what I'm doing wrong. If you have any advice that would be great. For now I'll use the Filter formula. Thank you

View solution in original post

9 REPLIES 9
AmDev
Super User
Super User

Hi @bobbybob 

 

To search on multiple columns you just need to add further logical tests inside your filter statement and separate each one with the Or operator (i.e. '||' ). See below where I have added 2 further logical tests based on two additional text fields in your data source - the green text would need to be swapped out with your additional field names:

 

SortByColumns(

Filter(

[@'Filter Dates'],

StartsWith(Title, TextSearchBox1.Text) ||

StartsWith(OtherTextField1, TextSearchBox1.Text) ||

StartsWith(OtherTextField2, TextSearchBox1.Text)

),

"Title",

If(SortDescending1, Descending, Ascending)

)

 

If you want to add choice fields to your query and you want to search them, you can add the following as another logical test your filter, again separating from adjacent logical tests with '||' :

 

TextSearchBox1.Text in YourChoiceField1.Value

 

Note, once you add the 'in' function, the expression becomes non-delegable and you will run into issues if your data source grows beyond 2000 rows - Where-as using the expression above as is with startswith on text fields is delegable and you will have no issues if you data source grows in size over time.

 

I hope this is helpful, but let me know if you have any queries.

Gerard

bobbybob
Helper II
Helper II

That did work. Now I realize I messed up and did a formula that has StartsWith. Is there any way I can use the formula above but make it into a fuzzy search instead? I want to be able to search any part of the column not just what it starts with. 

Drrickryp
Super User
Super User

@bobbybob 

You can use Search(splist,Textinput1.Text, "column1","column2",etc)  but it will be limited the same way @AmDev describes above for "in".

To answer your next question, see @WarrenBelz 's blog Practical Perhaps for how to deal with the delegation warning.

Thank you for the help

I tried to implement the new formula but can't get it to work. Is there something I did wrong?

 

SortByColumns(Search('Filter Dates',TextSearchBox1.Text,"Title","Location","Modified"), "Title", If(SortDescending1,Descending,Ascending))

Hi @bobbybob 

 

Search only works well with text fields, so I suspect you are having issues with the Modified date system field. To get around this you can use AddColumns to create a new column called say "Modified_TXT", which converts the "Modified" field to text and makes it searchable in your formula.

 

Try amending to the following:

 

SortByColumns(

Search(

AddColumns('Filter Dates', "Modified_TXT", Text(Modified)),

TextSearchBox1.Text,"Title","Location","Modified_TXT"),

"Title",

If(SortDescending1,Descending,Ascending)

)

 

As @Drrickryp recommends, it would be useful to get familiar with delegation as this expression is not delegable, but if your dataset stays low in row numbers this will work fine long term.

 

What data type is your location field? - if it's text you'll be fine, but if it's giving issues you may need to add that to the Addcolumns portion.

 

Good luck

Gerard

 

 

 

I still could not get the Search function to work. Instead I did a Filter formula and got it to work. I'll never have more than 2000 rows so this formula should be fine. 

SortByColumns(Filter([@'Filter Dates'],TextSearchBox1.Text in Title || TextSearchBox1.Text in Location || TextSearchBox1.Text in Modified), "Title", If(SortDescending1,Descending,Ascending))

This is the second app I've tried to get the Search function to work and cannot. I use the template: 

 

Search(Customers, SearchInput.Text, "Name")

 

within my formula and I can never get it to run. No idea why or what I'm doing wrong. If you have any advice that would be great. For now I'll use the Filter formula. Thank you

HI @bobbybob ,

Just my 20c worth- I have seen this before on posts where Search and Filter in the same piece of code do not seem to "play well together", but have never been able to replicate it. What you are doing however is fine and is what I do anyway - if I have a Filter already, I just add another using in. Another option - Search should work below and you will get rid of the Delegation warning (it is however still a "hidden limit" on the top filter output).

With(
   {
      wList:
      SortByColumns(
         Filter(
            [@'Filter Dates'], 
            StartsWith(
               Title, 
               TextSearchBox1.Text
            )
         )
      ), 
      "Title", 
      If(
         SortDescending1, 
         Descending, 
         Ascending
      )
   },
   Search(
      AddColumns(
         wList,
         "Modified_TXT", 
         Text(Modified)
      ),
      TextSearchBox1.Text,
      "Title",
      "Location",
      "Modified_TXT"
   )
)

 

 

 

crazor123
Helper I
Helper I

wrong topic: my bad

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,683)