Lookup invalid argument type Text Input and SQL

[iwonder]

Thank you for taking the time to read my question

I am working on an app that is connected to an on premise SQL db for the first time.

I want to be able to see if an item exists in one of my tables.

I have a Text Input and a Search icon. On the OnSelect action of the Search icon I want to have a LookUp that helps me determine if the code entered by the user exists in the SQL table.

The field I'm using for the LookUp is nvarchar(15). The value in the Text Input is text.

The equal sign has the error and says "Invalid argument type". 

.Text is the only option I have.

If(IsBlank(LookUp('[dbo].[tblEpyDG]',[@ProductCode] = SearchProductCode.Text,[@ProductCode])),Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

How do I resolve this?

Thanks

[eka24]

Do you get errors in the formula.

Do you have record in the database that matches what you entered in the search box.

Please show sample if that's the case 

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[iwonder]

When I pasted in your code I do not get any errors, the Notification comes up and says "Blank"

I also tried this as your code would return 3 records, the extra criteria returns just one.

If(IsBlank(LookUp('[dbo].[tblEpyDG]',ProductCode = SearchProductCode.Text && Classification = "GHS_CAN")),
Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

If I use the word And instead of && I get red underlines.

Here is my SELECT statement with the added criteria on Classification:

[eka24]

You have added another criteria that was not part of the original formula you gave which is also tested. But you are giving impression that it didn't work meanwhile that is not so.

Did you test based on the original the formula you gave?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[iwonder]

Yes, I did test on the original formula you provided and I received the word "Blank" when SearchProductCode = 16161623

Then I added the extra criteria thinking that the original criteria returns more than one record so maybe that's why I was still getting "Blank". Even with the extra criteria I get "Blank"

Note: There are more fields in this table but I'm only showing the ones in the criteria.

When I click the magnifying glass icon which has the code I see the dots go left to right across the top of the screen... so it's connecting to the DB I guess.

Thanks again for your help

[eka24]

Since your Productcode contains numbers, try;

If(IsBlank(LookUp('[dbo].[tblEpyDG]',ProductCode = Value(SearchProductCode.Text))),

Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

Or with other Criteria:

If(IsBlank(LookUp('[dbo].[tblEpyDG]',ProductCode = Value(SearchProductCode.Text) && Classification="GHS_CAN")),

Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

Or

If(IsBlank(LookUp('[dbo].[tblEpyDG]',ProductCode = SearchProductCode.Text && Classification="GHS_CAN")),

Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[iwonder]

Hi,

I pasted in both code examples (I added the "I" on the If for the 2 criteria example) and get this error:

I must say 5 Stars to you for your effort on this.

[eka24]

Please I have revised the formula is: If

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[iwonder]

I have been doing some more reading and it seems there may be a limit to the number of records PowerApps references when doing the LookUp. Is that correct?

My SQL table has 30370 rows.

I also just tried having the second criteria Classification = "GHS_CANADA" and my Notification was "Found"! I then added the ProductCode criteria after the Classification criteria and typed in && ProductCode = "16161622" and the Notification was "Blank". So I removed both criteria and typed in ProductCode = "16161622" and the Notification was "Blank"...

in SQL ProductCode is nvarchar(15) and Classification is nvarchar(7)

Update: I searched for ProductCodes that were less than 8 characters long and those were found! ProductCodes that were Text or Numbers

01234 = Text

KCTEst = Text

12345 = Number

but 16161622 is not found

Further UPDATE: 

I have this working: If(And(IsBlank(Search('[dbo].[tblEpyDG]',"16161622","ProductCode")),IsBlank(Search('[dbo].[tblEpyDG]',"GHS_CAN","Classification"))),Notify("Blank",NotificationType.Information),Notify("Found",NotificationType.Information))

For some reason Search is able to find my ProductCode but LookUp can't.

...still stumped...

Thanks

[eka24]

I don't know where you read from but this is documentation on lookup for SQL

https://docs.microsoft.com/en-us/connectors/sql/

The formula should work. I tested it with over 14000 rows. 

Take a screenshot of what formula you used lastly that didn't work.

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[iwonder]

I just updated my previous post with more info... made some progress