How to search multiple columns and filter

[bobbybob]

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?

[AmDev]

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]

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]

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

[bobbybob]

Thank you for the help

[bobbybob]

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

[AmDev]

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

[bobbybob · Accepted solution]

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

[WarrenBelz]

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]

wrong topic: my bad