Handling Time Series Data in Quickbase Apps
Introduction
Before my home state of Georgia went on lockdown, I dabbled in sourdough as a hobby. After lockdown, it was an obsession. To the point that I decided I needed to make my own proofing box. I looked at several designs, eventually settling on using a cooler, a Raspberry. Pi, a piezoelectric mister, a light bulk, and 2 smart switches to control it. I wanted to have careful control over the setup, so I integrated a humidity and temperature sensor as the brains, but I wanted a place to store and monitor the data generated by the proofing box—Quickbase was the perfect tool.
I had recently worked with a large chemical-processing company to setup a monitoring system encompassing thousands of sensors and while I didn’t need the same Enterprise scale, the concepts were similar. Specifically, pushing sensor data to Quickbase and presenting it back in an easily consumable fashion.
Loading Data Into Quickbase
Pushing data to Quickbase is easy, especially using our new RESTful API ( LINK: https://developer.quickbase.com/). I use a simple Python script on the Raspberry Pi to monitor the temperature and humidity readings, so to record the readings I wrote a small snippet of code which sends the current humidity and temperature readings to Quickbase once every minute:
import time
import requests
import Adafruit_DHT
#define the parameters for the sensor
DHT_SENSOR = Adafruit_DHT.DHT22
DHT_PIN = 4
#setup the API parameters (use your own values)
TABLE_ID = 'TABLE_ID_HERE'
headers = {
'QB-Realm-Hostname': 'REALM_HERE',
'Authorization': 'QB-USER-TOKEN TOKEN_HERE'
}
while True:
#read the temperature and humidity
humidity, temperature = Adafruit_DHT.read_retry(DHT_SENSOR, DHT_PIN)
if humidity is not None and temperature is not None:
#build the API body for the upsert call
body = {
"to": f"{TABLE_ID}",
"data": [
{
"6": {
"value": temperature
},
"7": {
"value": humidity
}
}
],
"fieldsToReturn": []
}
#push to Quick Base
r = requests.post(
'https://api.quickbase.com/v1/records',
headers = headers,
json = body
)
#pause for 1 minute
time.sleep(60)
The above code calls the new insert record API passing in the humidity and temperature readings. An HTTP POST call is made to https://developer.quickbase.com/operation/upsert. First, headers are set for the realm and user token:
headers = {
'QB-Realm-Hostname': 'REALM_HERE',
'Authorization': 'QB-USER-TOKEN TOKEN_HERE'
}
And, next, the body is passed in which informs the new Quickbase API which table I want to store the data in and the rest of the JSON sets the values based on the field ids:
#build the API body for the upsert call
body = {
"to": f"{TABLE_ID}",
"data": [
{
"6": {
"value": temperature
},
"7": {
"value": humidity
}
}
],
"fieldsToReturn": []
}
That’s it for storing the data in Quickbase—nice and easy. But, now that I have my data, I want to view it.
Enabling the Data For Use
The easiest route is to setup a simple line chart as you normally would. It’s quick, no-code, and perfectly functional. However, having recently worked on that large sensor app recently, I thought using a time-series chart would be more interesting for my use case. Thankfully, Quickbase’s Code Page functionality let me do just that.
When working with time-series data, I like to use the HighStock chart from Highcharts. It's a great charting library and all you have to do is write a few lines of binding code and you’re all set. Below is the code I used (don’t worry, we’ll go through it line by line):
<html>
<head>
<!-- import the highstock library-->
<script src="https://code.highcharts.com/stock/highstock.js"></script>
<script>
//setup
const REALM_ID = "REALM_HERE";
const USER_TOKEN = "USER_TOKEN";
const REPORT_ID = 1;
const TABLE_ID = 'TABLE_ID';
const RECORD_CREATED_FID = "1";
const READING_FID = "6";
const READING_NAME = "TEMPERATURE";
//function to build the chart
function buildChart(title, data, reading_name) {
Highcharts.stockChart('container', {
exporting: {
chartOptions: {
chart: {
width: 1024,
height: 768
}
},
fallbackToExportServer: false
},
chart: {
zoomType: 'x'
},
rangeSelector : {
selected : 0
},
xAxis: {
type: 'datetime'
},
title: {
text: title
},
plotOptions: {
series: {
color: '#000000'
}
},
series: [{
name: `${reading_name} - Readings`,
data: data,
type: 'spline',
tooltip: {
valueDecimals: 2
}
}]
});
}
//holds excution until all libraries have been loaded
function pageReady(fn) {
// see if DOM is already available
if (document.readyState === "complete" || document.readyState === "interactive") {
// call on next available tick
setTimeout(fn, 1);
} else {
document.addEventListener("DOMContentLoaded", fn);
}
}
//build our chart
pageReady(function() {
var xhttp = new XMLHttpRequest();
var url = `https://api.quickbase.com/v1/reports/${REPORT_ID}/run?tableId=${TABLE_ID}`;
xhttp.open('POST', url, true);
xhttp.setRequestHeader('Content-type', 'application/json');
xhttp.setRequestHeader('QB-Realm-Hostname', REALM_ID);
xhttp.setRequestHeader('Authorization', `QB-USER-TOKEN ${USER_TOKEN}`);
var params = '';
xhttp.onreadystatechange = function() { //the results are processed here
if(xhttp.readyState == 4 && xhttp.status == 200) {
const data = [];
var response = JSON.parse(this.responseText);
response.data.forEach(el => {
data.push([new Date(el[RECORD_CREATED_FID].value).getTime(),el[READING_FID].value]);
});
const title = 'DHT 22 - READINGS';
//we have our data -- build the chart
buildChart(title, data, READING_NAME);
}
}
xhttp.send();
});
</script>
</head>
<body style="margin:0;">
<div id="container" style="height: 400px"></div>
</body>
</html>
A pattern used in a lot of code pages for custom displays is the LCD method (load, convert, display). That’s exactly what the code above does, let’s take a look.
pageReady(function() {
var xhttp = new XMLHttpRequest();
var url = `https://api.quickbase.com/v1/reports/${REPORT_ID}/run?tableId=${TABLE_ID}`;
xhttp.open('POST', url, true);
xhttp.setRequestHeader('Content-type', 'application/json');
xhttp.setRequestHeader('QB-Realm-Hostname', REALM_ID);
xhttp.setRequestHeader('Authorization', `QB-USER-TOKEN ${USER_TOKEN}`);
var params = '';
xhttp.onreadystatechange = function() { //the results are processed here
if(xhttp.readyState == 4 && xhttp.status == 200) {
const data = [];
var response = JSON.parse(this.responseText);
response.data.forEach(el => {
data.push([new Date(el[RECORD_CREATED_FID].value).getTime(),el[READING_FID].value]);
});
const title = 'DHT 22 - READINGS';
//we have our data -- build the chart
buildChart(title, data, READING_NAME);
}
}
xhttp.send();
});
Here, we’re loading the data from our sensor table using the new report API. Similar to inserting the record, we make an HTTP POST to the API URL ( https://api.Quick Base.com/v1/reports/{reportId}/run ), making sure to supply the required realm and authorization headers just as before. The result of that call is a JSON object containing an array of result records:
{
"data": [
{
"1": {
"value": "2020-06-24T18:18:49Z"
},
"6": {
"value": 100.42
}
}
],
"fields": [
{
"id": 1,
"label": "Date Created",
"type": "timestamp"
},
{
"id": 6,
"label": "TEMP",
"type": "float"
}
],
"metadata": {
"numFields": 2,
"numRecords": 1,
"skip": 0,
"totalRecords": 1
}
}
Highchart’s doesn’t understand that data as is, but with a little bit of code we can convert it to the format that Highchart expects:
data.push([new Date(el[RECORD_CREATED_FID].value).getTime(),el[READING_FID].value]);});
The code above takes the Date Created field (FID 1), which is a date in string format, and converts it to a timestamp format which HIghchart’s understands, it also extracts the value of the reading field (FID 6), and then places them both into an array.
After that, we just need to configure the chart and display it for the user, which is what the following does:
buildChart(title, data, READING_NAME);
The final result looks something like this:
