Search items tool

[Anonymous]

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

[Jeff_Thorpe]

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)

[Anonymous]

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

[Jeff_Thorpe]

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

[Anonymous]

@Jeff_Thorpe 

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

[Jeff_Thorpe]

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.

[Anonymous]

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

[Jeff_Thorpe]

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

[Anonymous]

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

[Jeff_Thorpe · Accepted solution]

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