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:

A user scans a barcode using the Orca mobile barcode app
Orca calls your Lookup URL with ?barcode=value appended
Your server code searches internally for a matching record
If a match is found, you return the data in JSON format
Your data is presented to the user within the mobile app
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.

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:

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:

Intercept a web request
Extract the barcode from the query string
Search your internal database for a match
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

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.

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.

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

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, get in touch.

Ready to start scanning?

Get Started Book a demo