Solved: Vlookup in Appsheet app

Thomas1 · 04-20-2020 09:12 AM

Hi guys,

I am very new to AppSheet but I see great potential. I want to design a barcode scanner, very simple stuff in theory:)

The Datacolumns in my googlesheet are: Barcode, Location, date of last check, date of future check

I got three questions:
a) After a barcode was scanned i would like to see the information of my GoogleSheet (Location, date of last check & date of future check) In Excel i would use the vlookup based on the scanned barcode.

b) based on the GPS coordinates i would like to filter my location list, e.g. GPS in the states, show only American Cities.

c) I would like to have the date of future check calculated based on the date of last check. Should be today plus 2 years as default but also editable.

Thanks for your help.

Best regards,

Thomas

Steve

Set Reset on edit? to ON.

View solution in original post

Steve

Thomas1

thanks for your input!
I have read the article about the lookup and also watched some videos about it. However, I really do not get it.
Based on the scanned barcode I would like to get the location data from the googlesheets which is linked to the barcode. This formula I use: LOOKUP([_THISROW].[Barcode],“Status”,“Barcode”,“Location”) and since it is just a suggestion and I want the user to edit it I put it into the “Suggested values” of the location row.

After scanning the barcode the field for Location stays empty.

Please help i am really clueless.

Best,

Thomas

Steve

Try putting the LOOKUP() expression in Initial value rather than Suggested values, just to test.

Thomas1

Hi Steve,

thanks for you support!
So i have tried this formula (LOOKUP([_THISROW].[Barcode],“Status”,“Barcode”,“Location”)) in the field: App Formula (the right value is shown, however, i can not edit it, which i need)
Initial Value (no value is shown at all)
Suggested Values (formula not working since a list is expected but i only have on location (There is always a 1 to 1 relationship).)
And editable is turned on for this field

Thanks for your help

Steve

Set Reset on edit? to ON.

WillowMobileSys

I don’t know if this will help with the Barcode question…but AppSheet has built-in the capability of searching by Barcode when viewing that specific table. To use it you would want to set the Barcode column as “Searchable” (see image below).

Then to activate the Barcode search on that table view, tap the Search icon at the top of the view, you should see a little Barcode icon (see second image below), tap that to activate the Barcode scanner. The view will then be filtered by any rows that match that Barcode (see third image below).

EDITED: You could then use Right Swipe or Left Swipe functions to perform navigation to a specific view if that is your end goal?

Make the Barcode column Searchable

Activate Barcode Search - tap Search icon and then Barcode icon shown in Image

Rows filtered by scanned Barcode

Thomas1

You are the man Steve! Thank you so much.
Sounds super easy now, but i really was lost.

CXIntegration

Having a similar issue where I want information associated with the barcode to auto-populate but I’m having no luck.

The goal is to have “On or Off Site” Enum column populate its last/most recent information when the QR is scanned.

This is what I tried… (LOOKUP([_THISROW].[Scanned], “Scan”, “Scanned”, “On or Off”))

Scanned (QR)
Scan (Table)
On or Off (Column)