How to query your database on every scan

Requires a Business subscription plan

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 querystring parameter.

How does a Lookup URL work?

A good example of a Lookup URL is Google's Search engine. Each time a user searches Google, the search terms are sent to https://www.google.com/search?q=red+socks. Google then searches its internal databases for a match and returns the results.

How can I use a Lookup URL with Orca Scan?

Orca Scan can use a Lookup URL to pull data from your systems every time a user scans a barcode. 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 (JSON keys must match column names)
    1. If the user taps save, the data is saved to your sheet
    2. If the user taps cancel, the data is not saved to your sheet

How do I create a Lookup URL for my system?

The code below are examples of how you can setup a Lookup URL for your system. A fully working version for all major programming languages is available on GitHub.

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; }

The following HTTP Headers are sent with each lookup request:

Parameter Description
orca-sheet-name The name of the sheet making the request
orca-user-email Email of user who triggered the request (requires HTTPS)
orca-data-url Live Data Url of the current sheet if enabled (requires HTTPS)
orca-timestamp UNIX epoch time of the request
orca-secret Secret value if set (check this to verify request is from Orca Scan)

How do I expose my Lookup URL to the internet?

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 (e.g. 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

Create a new Orca Scan cloud sheet

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

Opening the sheet integration menu

Now open Integration Settings for the newly sheet.

3. Add your Lookup URL

Sheet lookup URL input

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 a HTTP header orca-secret with every request; allowing you to determine if the incoming request is from Orca Scan.

4. Test your Lookup URL

Sheet lookup test result

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

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

5. Save the changes

Save sheet integration settings

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:

Questions about Lookup URLs?

We're happy to help you troubleshoot any issues, chat with us live or drop us an email.