QR Code scanner lookup

Mike_T · 08-14-2020 10:43 AM

In a form, the second column is the QR code scanner. (I’m using a uniqueID() for the key). I’m trying to get two other columns to auto populate once the qr code is scanned. When the qr code column is blank it doesn’t show up in the form (which is fine). I’m using the following formula:

ANY(SELECT(QR Codes[Campaign], ([QR_Code] = [_THISROW].[QR_Code])))

and in another column:

LOOKUP([_THISROW].[QR_Code], “QR Codes”, “QR_Code”, “Element”)

in the QR Codes table i have a list of the QR codes and the reference information in the columns “Campaign” and “Element”

Here’s the funny part. When I click the scanner button in the emulator and get the resulting 1111222333 code, the other columns show up with the correct information. However when I use my phone and an actual QR code, it scans the qr code fine giving me the same data as in the table, but the other columns don’t appear with the relevant info.

What am i missing?

Steve

Perhaps the scanned code has extraneous spaces around it? Try wrapping [_THISROW].[QR Code] with TRIM():

ANY(SELECT(QR Codes[Campaign], ([QR_Code] = TRIM([_THISROW].[QR_Code]))))

See also:

Mike_T

Thanks for the reply. Unfortunately that didn’t solve it. In that same vein when I do submit the form all of the logic and vlookups I have running on the google sheet recognize the QRCode just fine. So I don’t think it’s a formatting issue either (i.e. text, number, etc). btw…my qrcodes are text and the Type is set as text also. Does anyone think this could have something to do with it? Text vs number?

Mike_T

I’m still looking for help here. I just can’t figure out why I’m not able to use a lookup or any(select in a column formula to input a value based off another columns value (qrcode scan). Anyone have any ideas? Meeting with a client tomorrow to try to explain. Thanks in advance!

Steve

As you need is time-sensitive, consider engaging support@appsheet.com for help with this.

Mike_T

OK. Works like a charm when I use Enum and “hard code” the list of Campaigns into the column. Once I select the corresponding Campaign the resulting enum list is triggered in the ShowIf of the other column.

The goal is to have the QRCode scan trigger the Campaign column which will trigger which of the 3 columns is shown.

Basically… if QRCode “A” is scanned, “1” will appear in the “Campaign” column. Then column “Blue” will Show with Enum List of “Pale” “Baby” “Navy”. If QRCode “B” is scanned, Campaign “2” is autofilled and column Red is shown with Enum List buttons of “Ruby” “Scarlet” and “Light”. Any thoughts why the QRScanner is not triggering the other columns?

Suvrutt_Gurjar

Hi @Mike_T,

Based on @Steve’s guidance, can you please try

ANY(SELECT(QR Codes[Campaign], (TRIM([QR_Code]) = [_THISROW].[QR_Code])))

Mike_T

Yep. Tried the TRIM formula and did not work. It’s the same values in the columns. When I do submit the form data all of the vlookups in my google sheets work just fine, so I know the text matches.

Thanks for the suggestion!