How to Scan Barcodes to Save Books to Airtable

In this article, we are going to talk about how to build a web app to scan ISBN barcodes to get the info of books and manage bills of books on Airtable.

Airtable is a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet. It can be used as an online database for our applications.

Dynamsoft Barcode Reader is used to scan barcodes.

You can check out the video to see what it can do.

The demo can create or open a bill and scan books by ISBN barcodes to add them to the bill.

Online demo

Getting started with Dynamsoft Barcode Reader

Dynamsoft Barcode Reader  

Design Tables

Create two tables on Airtable.

Books table:

Books table

Bills table:

Bills table

The Bills column in the Books table links to the Bills table.

The Total Price column in the Bills table is a rollup column which sums the prices of the books.

Create a Page to Manage Bills

  1. Create a new HTML file with the following content. It contains a table to list the existing bills and a button to create a new bill.

    <!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Scan Books to Airtable</title>
    <style>
    </style>
  </head>
  <body>
    <div class="app">
      <h2 class="title">Bills</h2>
      <table class="bills">
        <tr>
          <th>Bill Name</th>
          <th>Action</th>
        </tr>
      </table>
      <button class="newBillButton">New Bill</button>
    </div>
    <script>
    </script>
  </body>
</html>

  2. Download Airtable’s JavaScript library and include it.

    <script src="airtable.browser.js"></script>

  3. Set up things like the token and the base’s ID for Airtable and create a base instance for related actions. Here, we are exposing the token on the client side. We can use a server to redirect the request later for security concerns.

    let Airtable = require('airtable');
let airTableToken = "<Your Token>";
let airTableBaseID = "<The Base ID>";
let base = new Airtable({apiKey: airTableToken}).base(airTableBaseID);

  4. List bills in the table. If a bill is clicked, go to the scanner page where we can see books scanned and upload new books.

    function loadBills(){
  base('Bills').select({
    view: "Grid view"
  }).eachPage(function page(records, fetchNextPage) {
    records.forEach(function(record) {
      appendBill(record);
    });

  }, function done(err) {
      if (err) { console.error(err); return; }
  });
}

function appendBill(record){
  let name = record.fields["Bill Name"];
  let id = record.id;
  let a = document.createElement("a");
  a.href = "javascript:void();"
  a.addEventListener("click",function(){
    window.open("./scanner.html?id="+encodeURIComponent(id)+"&name="+encodeURIComponent(name));
  });
  a.innerText = name;
  let table = document.getElementsByClassName("bills")[0];
  let tr = document.createElement("tr");
  let td = document.createElement("td");
  td.appendChild(a);
  tr.appendChild(td);
  let actionTd = document.createElement("td");
  let deleteButton = document.createElement("button");
  deleteButton.innerText = "Delete";
  deleteButton.addEventListener("click",function(){
    deleteBill(id,tr);
  });
  actionTd.appendChild(deleteButton);
  tr.appendChild(actionTd);
  table.appendChild(tr);
}

  5. Define the function to delete the bill by its ID.

    function deleteBill(recordID,tr){
  base('Bills').destroy([recordID], function(err, deletedRecords) {
    if (err) {
      console.error(err);
      return;
    }
    alert("Deleted");
    tr.remove();
  });
}

  6. Display an input modal for the user to enter a name for a new bill if the user clicks the New Bill button.

    HTML:

    <div class="inputModal">
  <label>
    Please input a name for the bill:
  </label>
  <input type="text" class="billInput"/>
  <div>
    <button class="okayButton">Okay</button>
    <button class="cancelButton">Cancel</button>
  </div>
</div>

    CSS:

    .inputModal {
  display: flex;
  flex-direction: column;
  align-items: center;
  justify-content: center;
  position: fixed;
  top: 50%;
  left: 50%;
  transform: translate(-50%, -50%);
  width: 300px;
  height: 150px;
  border: 1px solid gray;
  border-radius: 5px;
  background: white;
  z-index: 9999;
  visibility: hidden;
}

    JavaScript:

    document.getElementsByClassName("newBillButton")[0].addEventListener("click",function(){
  document.getElementsByClassName("inputModal")[0].style.visibility = "visible";
})

document.getElementsByClassName("okayButton")[0].addEventListener("click",function(){
  createBill();
});

document.getElementsByClassName("cancelButton")[0].addEventListener("click",function(){
  document.getElementsByClassName("inputModal")[0].style.visibility = "hidden";
});

  7. Define the function to create a new bill.

    function createBill(){
  let name = document.getElementsByClassName("billInput")[0].value;
  if (isValidBillName(name)) {
    base('Bills').create([
      {
        "fields": {
          "Bill Name": name,
          "Books": []
        }
      }
    ], function(err, records) {
      if (err) {
        console.error(err);
        return;
      }
      document.getElementsByClassName("inputModal")[0].style.visibility = "hidden";
      records.forEach(function (record) {
        console.log(record.getId());
        appendBill(record);
      });
    });
  }else{
    alert("Invalid name.");
  }
}

Create a Page to Scan Books to a Bill

In a previous article, we’ve written an ISBN barcode scanner. We can build the scanning page based on it.

We can make it list books stored on Airtable and sync newly scanned books to Airtable.

  1. List the scanned books of a bill.

    function loadBooksInBill(){
  let id = getUrlParam("id");
  let name = getUrlParam("name");
  if (id && name) {
    document.getElementsByClassName("name")[0].innerText = name;
    billID = id;

    base('Bills').find(billID, function(err, record) {
      if (err) { console.error(err); return; }
      let bookRecordIDs = record.fields["Books"];
      if (bookRecordIDs) {
        for (let index = 0; index < bookRecordIDs.length; index++) {
          const recordID = bookRecordIDs[index];
          base('Books').find(recordID, function(err, record) {
            if (err) { console.error(err); return; }
            insertRow([record.fields["Barcode"],record.fields["Title"],record.fields["Price"]],recordID);
            calculateTotalPrice();
          });
        }
      }
    });
  }
}

  2. Sync newly scanned books to Airtable.

    function syncToAirtable(){
  updateStatus("Syncing");
  let books = buildArrayForAirtable();
  base('Books').create(books, function(err, records) {
    updateStatus("");
    if (err) {
      console.error(err);
      alert(err);
      return;
    }
    alert("Uploaded "+records.length+" record(s).");
  });
}

function buildArrayForAirtable(){
  let books = [];
  let rows = document.getElementsByTagName('tr');
  for (let i = 1; i < rows.length; i++) { //skip the head
    let row = rows[i];
    if (!row.getAttribute("data-recordID")) { //newly scanned
      let cols = row.querySelectorAll('td, th');
      let book = {fields:{}};
      for (let j = 0; j < cols.length - 1; j++) { //do not include the last column
        book.fields.Barcode = cols[0].innerText;
        book.fields.Title = cols[1].innerText;
        book.fields.Price = parseFloat(cols[2].innerText);
        book.fields.Bills = [billID];
      }
      books.push(book);
    }
  }
  return books;
}

  3. Function to delete a record on Airtable.

    function deleteRemoteRecord(recordID){
  return new Promise((resolve, reject) => {
    base('Books').destroy([recordID], function(err, deletedRecords) {
      if (err) {
        reject(err);
        return;
      }
      resolve(deletedRecords);
    });
  })
}

All right, we’ve covered the important parts of the demo.

Source Code

Get the source code of the demo to have a try:

https://github.com/tony-xlh/Scan-Barcode-to-Airtable