Free search using a text input

[epiej]

Say I have a the following data table:
"Pet_Type"    "Pet_Name "    "Owner"
Dog                Buster             Adam

Cat                 Mushroom      Becky

Toucan           Rocko              Chris

I'm trying to use one text input line to search on all three columns. I want it so that if I enter to the text input control: "og hri", the data table should display the Dog and Chris rows. Currently I've got a gallery populated by splitting the input text by spaces, like

Split(SearchInput.Text, " ")

Then, I filter the pet table by

Filter('Pets',IsBlank(SearchInput)||Title.Text in Pet_Type||Title.Text in Pet_Name||Title.Text in Owner)

But when I put in "Dog Chris", it behaves very eratically, sometimes only showing the dog row, or chris row, or none at all. Once or twice I just had "Dog" and it showed nothing.

[RandyHayes · Accepted solution]

@epiej 

If you are just doing a free text search over all the data, then I believe the following formula might be what you are looking for:

Filter(Pets, 
       (Sum(Split(SearchInput.Text, " "), If(Result in Pet_Type,1))>0) 
     || Sum(Split(SearchInput.Text, " "), If(Result in Pet_Name,1))>0
     || Sum(Split(SearchInput.Text, " "), If(Result in Owner,1))>0
     )

If you want to tidy it up a bit, you could do something like this as well:

Filter(AddColumns(Pets, "allData", Pet_Type & " " & Pet_Name & " " & Owner), 
       (Sum(Split(TextInput4.Text, " "), If(Result in allData,1))>0) 
)

Or, for even more tidy:

Filter(AddColumns(Pets, "allData", Concat([Pet_Type, Pet_Name, Owner], Value & " ")), 
       (Sum(Split(TextInput4.Text, " "), If(Result in allData,1))>0) 
)

I hope this is helpful for you.

[v-yuxima-msft]

Hi @epiej ,

Do you test with @RandyHayes 's solution, I test on my side and it works ok. 

And I will accept it as solution.

Best Regards.

Yumia

[epiej]

This works great, thank you!