How to connect Orca Scan to your database

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 googles servers, for example, https://www.google.com/search?q=red+socks. Google searches its internal databases and returns the matching results.


How can I use a Lookup URL with Orca Scan?

You 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 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 create a Lookup URL for my system?

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 to your server with every lookup request:

Parameter Description
orca-timestamp UNIX epoch time of the request
orca-sheet-name The name of the sheet making the request
orca-data-url Live Data Url of the current sheet if enabled (requires HTTPS)
orca-secret Secret value if set (check this to verify request is from Orca Scan)
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 (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 sheet
Create a 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

Click on 'integrations' button at the top of the sheet
Click on 'integrations' button at the top of the sheet

Now open Integration Settings for the newly sheet.

3. Add your Lookup URL

Add a Lookup URL
Add a Lookup URL

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

Test your Lookup URL by clicking the 'test' button and 'ok'
Test your Lookup URL by clicking the 'test' button and 'ok'

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

Click save to confirm the changes
Click save to confirm 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, chat with us live or drop us an email.

Ready to start scanning?