Tool tracking in Airtable with a Bluetooth sensor

Airtable is spreadsheets re-imagined for the web and is used by lots of people - from sports teams to marketing departments to factory operations teams. 

I thought it would be interesting to see how quick it would be for someone in one of those teams to log the usage of a particular piece of equipment in an Airtable using Blecon. Let's see how we get on!

Overview

I can connect Blecon directly to Airtable, because Airtable supports webhook triggers. Great! 

To replicate this setup, you'll need an Airtable Pro subscription in order to use the scripting feature we will require. Let's get started.

Create a Blecon Network and add a sensor

1. Open the Blecon console at https://console.blecon.net.
2. Click Create Blecon Network and choose any name.
3. Connect a sensor by scanning the Network QR code with your phone, then tapping your phone against a Blecon Device.

Place sensor

Let's use the Blecon B1 motion sensor to detect the usage of a particular piece of equipment. Here we have this very well-used impact driver. The B1 is of course waterproof and shock resistant so we don't need to worry too much about how it's handled.

Preparing Airtable

1. Log into Airtable and open a new table
2. Set up the table like this:

Note that the column type of the Timestamp column should be like this: 

Connecting Blecon to Airtable

We need to tell Blecon where to send sensor events. Here's how you do that.

1. Open the table you created earlier in Airtable.
2. Go to Automations at the top, and then choose Add Trigger, then Webhook.
   
   
3. Now Airtable will generate a webhook URL for you, here:
   
   
   
   
4. Copy that webhook URL and then in the Blecon Console, enter that as the Handler URL in your network configuration. That's all you need to do on the Blecon side.
   
   

Completing the setup on Airtable

To populate our Airtable, we need to add a small script so that our Blecon sensor data is represented correctly in our Airtable.

1. Staying in the Automations screen from the previous step, click Add Action, then choose Run Script.
2. In the code section, paste the following script:
   
   
   
   
   
3. Now you need to set up the inputs to the script. You need to create four inputs, named length, timestamps, types, and device. You do this by finding the input you want within the JSON sent by Blecon.
4. Create the length input by going to request_data, then records, and length
5. Create timestamps and types by going to request_data, records, then the respective item
6. Create Device ID by going to network_headers then choose device_id
7. When you're sure you've created those inputs with the right names, you can click Finished editing, and you're done!

Visualising the data

If you've completed those steps, you should already be seeing data flowing into your table. 

Let's say however we want to make this more useful, and have a graph of tool usage over time. What we want to do first is bucket our events by hour. 

1. Create a new column named Buckets.
2. Set this column to a Formula type and paste the following formula:
   DATETIME_FORMAT({Timestamp},'HH-DD-MM-YYYY')
   
   
3. You should now see buckets by date appear like this:
   
   
   
4. This represents the hour and date of the tool movement event. Now with this, we can make a graph.
5. Go to the Extensions section of Airtable, and add a bar chart.
6.  Set up the chart as below:
   
   
   

Conclusion and results

Here's how our tool tracker is looking. I've added another sensor to my Blecon network so this is tracking the usage of two tools.

I think the combination of Blecon cloud native sensors and modern productivity tools such as Airtable is an extremely powerful one. If we can put real-world data into the hands of everyone, there's no limit to its applications in areas such as energy efficiency, business processes, or healthcare, just to name a few.

What do you think? 

Blecon is in limited preview and is currently available to Early Access members. Contact us or apply for Early Access.