cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Super User
Super User

Re: Search items tool

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
Super User

Re: Search items tool

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

Re: Search items tool

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

Super User
Super User

Re: Search items tool

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

Re: Search items tool

@Jeff_Thorpe 

 

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

Super User
Super User

Re: Search items tool

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

Re: Search items tool

@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

Super User
Super User

Re: Search items tool

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

Re: Search items tool

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

 

Super User
Super User

Re: Search items tool

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (6,003)