cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
amarpal
Level: Powered On

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.
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.
amarpal
Level: Powered On

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.
amarpal
Level: Powered On

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.
amarpal
Level: Powered On

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.
amarpal
Level: Powered On

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.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 198 members 4,660 guests
Please welcome our newest community members: