Search on text and int columns using 1 search box (textinput)

(0) Share

Report

Posted on by SearchingYT

107

Hi power app experts,

My datasource is sql server, and my table has text and int columns.
Lets look at 2 columns "Name" (text) and "ID" (int).
I have a gallery with these columns and I use a textinput field for searching capabilities.

I tried to write a search formula in gallery items as follows:

If( !IsBlank(TextInput),
Search(datasource, TextInput.Text, "Name", "ID"),
datasource)

It fails because the search function want to only search on text.
What can I do to combine searching for both datatypes.

Any help, workarounds will be appreciated
Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Sort by

WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@SearchingYT ,

Correct - Search() only functions on Text values - you cannot search on a numeric value. You could do this

If( !IsBlank(TextInput), Search( Filter( datasource, ID = Value(TextInput.Text) ), TextInput.Text, "Name" ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Ok. I tried using your for formula but it didnt work. It returns nothing...

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

Like
a
(0)

Report
Copy link

Link copied!
@SearchingYT

How about this

Filter( datasource, TextInput.Text in "Name" || Textinput.Text = ID )

Was this reply helpful? Yes No
WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@SearchingYT ,

Just updating what @Drrickryp has suggested

Filter( datasource, TextInput.Text in "Name" || ID = Value(Textinput.Text) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp and @WarrenBelz I just tried your formula. This gave me funny results:

- It works on searching ID. But my search should match exact. So, if ID = 123, I have to type "123" to get a match. If I only type "12", I get no match.
- It works on searching numbers in Name column but not text. So, if Name = "BBC Radio 3 Channel 201" and I type "BBC" or "Radio", this gives me no results. But, If I type "3" or I type "201", then it matches? This is quite strange.

Any ideas on what is going on here.
Thanks.

Was this reply helpful? Yes No
WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
@SearchingYT ,

Correct (on the partial match on ID), you simply cannot do it on a number - as you already have an in Filter which is not Delegable, try this (will limit you to 2,000 items in the search). Also Name is not a good title for a field as it is a Reserved Word in Power Apps.

With( { wList: AddColumns( DataSource, "txtID", Text(ID) ) }, Filter( wList, TextInput.Text in Name || TextInput.Text in txtID ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Awesome! The searching functionality works! But, I am running into delegation issues, as you mentioned as well. My datasource tabel has more than 5000 records. Could you please tell me how I could solve delegation issue here.

Yes, my column name is not Name, it was just for example purposes.

Was this reply helpful? Yes No
WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@SearchingYT ,

Quite simply Search and in are not Delegable.

I have a blog on Delegation that has some suggestion for large collections , particularly this one, but note if you get much over 10,000 you will have significant performance issues.

Is your original question now solved?

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
SearchingYT 107 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz Thanks a lot!
Firstly, Search function is delegable on sql datasource.
When I search on a text column, I do get matches on more than 2000 rows.
Yes, "in" is not delegable, but I don't need that as Search function on text column works great.

The issue here is that with your formula I am collecting my datasource in "wlist". At this point I have already missed rows > 2000.

I like your solution, but I will not be able to use it here, as my client will get incomplete results...

Was this reply helpful? Yes No
WarrenBelz 156,003 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@SearchingYT ,

I don't know which option you were using, but it does collect all records - wList is a temporary variable, not the collection name.

Was this reply helpful? Yes No