cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Search items tool

Hello,

I wanted to search items in PowerApps from the SQL table. but the problem is that the the column contains only numbers and I am using this fucntion:

 

SortByColumns(Filter('[dbo].[BOFScrapYardReceipts]',StartsWith(OperatorId, TextSearchBox1.Text)

 

It shows me an error that it expects Text value and my columns contains only numbers. So, how to use or What should i change for the Work?

 

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions

The formula is looking at the BOFScrapYardReceipts table and if heather is listed in a column on that table then it isn't going to find her. You could try and add a lookup to the formula to get the name to search on. 

I hacked this together, so it may need some tweaking.

SortByColumns(Filter('[dbo].[BOFScrapYardReceipts]',StartsWith(PONum, TextSearchBox1.Text),
StartsWith(Lookup('Operator Table', Id = OperatorId, Name), TextSearchBox1_1.Text)),"
ReceivedDateTime",If(SortDescending1, Ascending,Descending))



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

9 REPLIES 9
Super User II
Super User II

You need to convert the number into text. I made a small change to the formula that work but you will get a delegation waring (blue squiggly line) because the Text() function isn't delegable. If you have a small data set (under 2k then this won't be an issue but you may need to increase the non-delegable query limit in the advance settings.

 

SortByColumns(Filter('[dbo].[BOFScrapYardReceipts]',StartsWith(Text(OperatorId), TextSearchBox1.Text)



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Anonymous
Not applicable

@Jeff_Thorpe 

 

Not working. I am typing but the results don't pop up

 

 

My full expression:

 

SortByColumns(Filter('[dbo].[BOFScrapYardReceipts]',StartsWith(PONum, TextSearchBox1.Text),StartsWith(Text(OperatorId), TextSearchBox1_1.Text)),"ReceivedDateTime",If(SortDescending1, Ascending,Descending))

If you have both the TextSearchBox1 and TextSearchBox1_1 blank (nothing in them) does the gallery show everything from [dbo].[BOFScrapYardReceipts]. 



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Anonymous
Not applicable

@Jeff_Thorpe 

 

Textboxsearch1 works but the other doesn't. yeah, everything is displayed on the browse screen

Based on how the formula is set-up both conditions must be true if there is something in the search box. So if you have a value in both search boxes then it will only return records that match both conditions.



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Anonymous
Not applicable

@Jeff_Thorpe 

 

Checkout the screenshots. When i serach in box 1 like search for 20 it shows. but if i type name in the 2nd textbox it doesn't shows anything.po.pngoperator.png

The Startswith is using OperatorId and not OperatorName. Isn't Heather in the OperatorName field?



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
Anonymous
Not applicable

@Jeff_Thorpe 

Yeah, but i used lookup and realtionships so, that Operator table points the Id to main table. So, i want to search  the name which is equivalent to id shows in the serach results. 

 

Like 

Main table

OperatorId

1

2

 

Operator Table

Id  Name   PID

1    Tom     1245

2     Harry   456

 

I wan to search the name as it shows name in the browse gallery. the dropdown is made of PID. so if we select the PID it shows the name on detailed screen and browse gallery.

 

The formula is looking at the BOFScrapYardReceipts table and if heather is listed in a column on that table then it isn't going to find her. You could try and add a lookup to the formula to get the name to search on. 

I hacked this together, so it may need some tweaking.

SortByColumns(Filter('[dbo].[BOFScrapYardReceipts]',StartsWith(PONum, TextSearchBox1.Text),
StartsWith(Lookup('Operator Table', Id = OperatorId, Name), TextSearchBox1_1.Text)),"
ReceivedDateTime",If(SortDescending1, Ascending,Descending))



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (11,974)