Searching your database when a barcode is scanned

What is a Lookup URL?

A Lookup URL is a publicly accessible web address that allows external applications to search your internal database by passing a search term as a query string parameter.


How does a Lookup URL work?

A good example of a Lookup URL is Google’s Search engine. Every time a user searches Google, the search terms are sent via a query string to Google servers, for example, https://www.google.com/search?q=red+socks. Google searches its internal databases and returns matching results.


How can I use a Lookup URL with Orca Scan?

You can use a Lookup URL to pull data from your database every time a user scans a barcode using the Orca Scan mobile app. The flow is as follows:

  1. A user scans a barcode using the Orca mobile barcode app
  2. Orca calls your Lookup URL with ?barcode=value appended
  3. Your server code searches internally for a matching record
  4. If a match is found, you return the data in JSON format
  5. Your data is presented to the user within the mobile app
  6. If the user taps save, the data is saved to your sheet

Please note: JSON keys must match the column names in your sheet.


How do I show an error dialog in the app?

Return a HTTP status code 200 along with an ___orca_message with display set to dialog and type set to error, for example:

{
	"___orca_message": {
		"display": "dialog",
		"type": "error",
		"title": "Validation Error",
		"message": "Please provide more information"
	}
}

A dialog will appear after the lookup, preventing the edit/save:

A dialog box will appear after the lookup, highlighting the ‘Lookup Error’, preventing the edit/save.
A dialog box will appear after the lookup, highlighting the ‘Lookup Error’, preventing the edit/save.

How do I show a notification in the app?

Return an ___orca_message with display set to notification, for example:

{
    "___orca_message": {
		    "display": "notification",
		    "type": "success",
		    "message": "Please scan next item"
    }
}

A notification will appear briefly at the top of the app:

A notification will appear briefly at the top of the app showing the notification message.
A notification will appear briefly at the top of the app showing the notification message.

There are three types of notifications to choose from:

Type Appearance
error A red notification appears at the top of the app.
warning A yellow notification appears at the top of the app.
success A green notification appears at the top of the app.

How do I create a Lookup URL?

You can create a Lookup URL in a few lines of code, your code simply needs to:

  1. Intercept a web request
  2. Extract the barcode from the query string
  3. Search your internal database for a match
  4. Return the result as a JSON object
Node.js C# Go Python PHP Java
var express = require('express'); var app = express(); // GET / handler app.get('/', function (req, res) { // get the incoming barcode scanned by a user var barcode = req.query.barcode; // TODO: query your database or API to retrieve some data // create an object to return (property names must match column names) var dataToReturn = { Vin: barcode, Make: 'SUBARU', Model: 'Legacy', 'Manufacturer Name': 'FUJI HEAVY INDUSTRIES U.S.A', 'Vehicle Type': 'PASSENGER CAR', Year: 1992 }; // return data to Orca as a JSON object res.json(dataToReturn); });
[HttpGet] public JsonResult Get() { // get the incoming barcode scanned by a user string barcode = HttpContext.Request.Query["barcode"].ToString(); // TODO: query your database or API to retrieve some data // hydrate model (property names must match column names when serialised) var result = new OrcaLookupModel(){ Vin = barcode, Make = "SUBARU", Model = "Legacy", ManufacturerName = "FUJI HEAVY INDUSTRIES U.S.A", VehicleType = "PASSENGER CAR", Year = 1992 }; // return data to Orca as a JSON object return new JsonResult(result); }
// create a GIN router to handle requests router := gin.Default() // GET / handler router.GET("/", func(c *gin.Context) { // get the incoming barcode scanned by a user barcode := c.Query("barcode") // TODO: query your database or API to retrieve some data // return JSON object (property names must match column names) c.JSON(200, gin.H{ "VIN": barcode, "Make": "SUBARU", "Model": "Legacy", "Manufacturer Name": "FUJI HEAVY INDUSTRIES U.S.A", "Vehicle Type": "PASSENGER CAR", "Year": 1992, }) })
# GET / handler @app.route("/") def index(): # get the incoming barcode scanned by a user barcode = request.args.get("barcode") # TODO: query your database or API to retrieve some data # create data object to return (property names must match column names) data = { "VIN": barcode, "Make": "SUBARU", "Model": "Legacy", "Manufacturer Name": "FUJI HEAVY INDUSTRIES U.S.A", "Vehicle Type": "PASSENGER CAR", "Year": 1992 } # return data in JSON format return jsonify(data)
<?php // set HTTP content type header("Content-Type: application/json"); // get the incoming barcode scanned by a user $barcode = $_GET['barcode']; // TODO: query your database or API to retrieve some data // create data object to return (property names must match column names) $data = [ 'VIN' => $barcode, 'Make' => "SUBARU", 'Model' => "Legacy", 'Manufacturer Name' => "FUJI HEAVY INDUSTRIES U.S.A", 'Vehicle Type' => "PASSENGER CAR", 'Year' => 1992 ]; // return data as JSON object echo json_encode($data); ?>
@RequestMapping( value = "/", method = RequestMethod.GET, produces = "application/json") @ResponseBody public HashMap<String, Object> Lookup(String barcode) { // TODO: query your database or API to retrieve some data // return data as JSON object (property names must match column names) HashMap<String, Object> data = new HashMap<>(); data.put("VIN", barcode); data.put("Make", "SUBARU"); data.put("Model", "Legacy"); data.put("Manufacturer Name", "FUJI HEAVY INDUSTRIES U.S.A"); data.put("Vehicle Type", "PASSENGER CAR"); data.put("Year", 1992); return data; }

A fully working version for all programming languages can be found on GitHub.

HTTP Headers

The following HTTP headers are sent with every request:

Parameter Description
orca-request-type lookup
orca-secret Sheet assigned secret (check this to verify request)
orca-sheet-id Unique ID of the Orca Scan sheet
orca-sheet-name The name of the sheet making the request
orca-timestamp UNIX epoch time of the request
orca-user-email Email of user who triggered the request (requires HTTPS)

How do I connect my development machine?

During development, you will need to use a tool like ngrok to expose your local development service to the internet; you can do that using the following commands:

# install ngrok
npm install ngrok -g

# expose port 3000 to the internet (change to your port)
ngrok http 3000

This will output something like:

Session Status   online
Session Expires  7 hours, 59 minutes
Version          2.3.35
Region           United States (us)
Web Interface    http://127.0.0.1:4040
Forwarding       http://7349a8cf186a.ngrok.io -> http://localhost:3000
Forwarding       https://7349a8cf186a.ngrok.io -> http://localhost:3000

Copy the HTTPS Forwarding URL, for example, https://7349a8cf186a.ngrok.io. This will be your Lookup URL whilst developing your lookup service.

Once you have completed development, you will need to deploy it to a public server such as a Digital Ocean Droplet or Heroku and obtain a new public URL to use as your Lookup URL.


How do I add a Lookup URL to my sheet?

You can add a Lookup URL to any sheet in Orca Scan using the following steps (includes a working example):

1. Create a new sheet

You can add a Lookup URL to any sheet in Orca Scan.
You can add a Lookup URL to any sheet in Orca Scan.

Let’s start by creating a sheet called Vehicle Checks using the Vehicle Inspections template. The goal is to allow users to scan Vehicle Identification Numbers (VIN) using the Orca Scan mobile app and view associated information held by the National Highway Traffic Safety Administration (NHTSA).

2. Edit Sheet Integrations

Once you’ve created a sheet, open the integrations settings.
Once you’ve created a sheet, open the integrations settings.

Now open Integration Settings for the newly sheet.

3. Add your Lookup URL

In the Integrations settings, you will find the Lookup URL box. Add a Lookup URL that points to your system.
In the Integrations settings, you will find the Lookup URL box. Add a Lookup URL that points to your system.

This is where you would enter a Lookup URL that points to your system. For this example lets use https://decode.orcascan.com/vin

Security

You can provide a secret that will be sent to your server as an HTTP header orca-secret with every request; allowing you to determine if the incoming request is from Orca Scan.

4. Test your Lookup URL

You can now test the Lookup URL by clicking the 
You can now test the Lookup URL by clicking the 

You can now test the Lookup URL by clicking the Test button. This will send an HTTP GET request to your server with the query string barcode=value and display the result.

Note: you must return an HTTP 200 response within 750ms, otherwise it is ignored.

5. Save the changes

Save your changes.
Save your changes.

Finally, save your changes and you’re done.

6. Scan a VIN number

You can now open the Orca Scan mobile app, select the Vehicle Checks sheet and scan any VIN number to view NHTSA information. Try the following:

This is an example of a VIN Barcode.
This is an example of a VIN Barcode.

Questions about Lookup URLs?

We’re happy to help you troubleshoot any issues, get in touch.


Ready to start scanning?