Setting a variable or displaying a column value from a selected record

[ozzie14]

Hi Power App Community members,

I am stuck with an issue!!

I am trying to fetch the recordid of a selected record in my Canvas app.

My formula is: Lookup(mytablename, Right(serial_num,7) = searchValue).work_ord_num)

Where:

searchValue is a variable which contains user input: UpdateContext({searchValue:TextSearch.Text})

serial_num is the columnname I'm comparing the user input.

work_ord_num is the columnname which contains the recordid, I'm trying to fetch (datatype: int)

Right now I'm trying to display it, but would like to capture it in a variable, so I can use later for insert/update to data table (SQL Server).

FYI: I have used the formula: 

Filter(mytablename, Right(serial_num,7) = scannedValue || Right(serial_num,7) = searchValue && status = 1)

to display the record in a DataTable, and that is working fine!

The display textInput box is showing the error:

'Invalid argument type (Control). Expecting a Text value instead'

I have read several articles in this forum but none resolved my issue.

Would really appreciate any help.

Thanks

Ozzie

[FLMike · Accepted solution]

Hello,

Other than you typed Lookup versus LookUp, I do not see anything wrong with what you have unless one of the input values has an invalid type.

I have a table named Rests. Rests has a new_name string field. 

Text(LookUp(Rests, Right(new_name,7) = "").new_name) //is what I use and it works fine.

And I only used Text, because yours is an int so I explicitly casted as if mine was an int.

In your query, if you purposely change it to have real values (dont use the context values etc), does it now work? You only typed in stuff so I cannot see your real code, but its as if its not assigning a value, but a control to the context or one of your other values.

Can you share the code itself so I can get a better look.

Also something else you can do to validate its working properly, is if you know you will only get one record then do 

First(Filter(mytablename, Right(serial_num,7) = scannedValue || Right(serial_num,7) = searchValue && status = 1)).MyIdField

And verify that it gives you the right value, as it should.

[ozzie14]

Hi Mike,

Thank you for your reply.

As suggested, I changed Lookup to LookUp and that removed the error: 

'Invalid argument type (Control). Expecting a Text value instead'

However, that didn't resolved the issue.

After quite sometime I figured out that there is an extra bracket at the end of the formula..!!

LookUp(mytablename, Right(serial_num,7) = searchValue).work_ord_num)

I knew I was missing something, which is possibly a simple tweak in that line of code, and that was more frustrating!!

(I'm a more of a php guy, and try not to use JS or JSON, because of their cryptic nature!)

I've started using PowerApps just a few weeks ago and not that savvy yet. I hope this example will help the rest of the PowerApps users from making such a mistake.

Thanks.

Ozzie

[FLMike]

So my solution resolved the thing you said was the first problem 🙂 so please mark that as such.

The second problem was a different problem (since you didn't have the code to validate).

I am glad you got it working.
Cheers
If you like my answer, please Mark it as Resolved
and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | Ex-Microsoft

Site: https://gernaeysoftware.com

LinkedIn: https://www.linkedin.com/in/michaelgernaey