Fill in an Airtable time tracking sheet with a QR Code

Fed up 😫 with manually filling your IT system from paper timesheets? Looking for a more efficient way to log times in your Airtable base and automate monthly statements?

In this tutorial we will learn to fill in hours in Airtable by scanning a QR code from the workplace. You will be able to create a QR code linked to a place and connect an Airtable base via Zapier for time tracking.

Overview of the Airtable base that will be updated via QR code scans

Optionally, you will be able to prevent cheating by checking if people were really at place or not, thanks to the Ubiqod geofencing feature.

Before you start, you need:

• A free Ubiqod account - a platform to track on-site activity, processes and assets with QR codes and IoT-devices
• A free Airtable account
• A Zapier account that allows “Multi-step Zaps”

Step 1: We will learn how to create a QR code for a workplace with Ubiqod

Step 2: We will set up an Airtable base for time tracking and workers’ repository

Step 3: We will create a Zap to log check-in and check-out hours in the Airtable base when a QR code is scanned.

Step 1 - Create a QR code for each workplace with Ubiqod

First sign in or create a free Ubiqod account.

Once you are logged in, select the Sitesmenu and click on +Newto create a workplace. Give it a name, and enter its address before clicking on Geocode it to get its GPS coordinates. Click on Createto save and visualize your location.

The site page store information about the workplace and its GPS location

Then, select the Dispatch groups menu and create a group called “Ubiqod to time tracking Zap”. This will be used later to send QR code scan events to Zapier.

Finally, select Custom Qods in the Trackers menu and click on +New to create a QR code related to the workplace:

• Give a name to your Qod (for example: “Time Tracking”) and select the previously created Site;

Choose a name and a site for your QR code

• Click on Next ;
• Select the option Compose a code and the previously created “Ubiqod to time tracking Zap” Dispatch group, click on Next ;

Select the action linked to the QR code

• In the last step, check Get user's GPS location if you want to use geofencing.

Enable the geofencing option

The QR code has been generated, click on the QR code icon to print it!

Display the QR code to print and scan it

Step 2 - Set up an Airtable base for time tracking

When tracking working times, you need to identify who clocks-in and out. In this example, we decided to set a PIN code to each worker. When the worker scans the QR code, he is requested to enter his code to then retrieve his identity.

Connect to your Airtable account and Add a base .

Create a first table to register workers and PIN codes. Set up a 2 columns table : PIN code number and Name single line text.

img

Create a second table as timesheet, with following columns:

• Key: a single line text used to create a log reference
• Date: a datefield
• Site: a single line text to store the site name
• Worker PIN code: a link to another record field to link the two tables depending on the worker. Customize field type to “allow linking to multiple records”.
• Worker name: a lookupfield to get worker name from Workers’ table. In the field customization panel, select “Worker PIN code” as the “Field on this table that links to the records you want to look up” and “Name” as “table field that you’d like to look up”.
• Check-in: a datefield to store arrival date
• Check-out: a datefield to store departure date
• Working time: a formulafield configure to compute work duration
  Formula = DATETIME_DIFF({Check-out},{Check-in},'seconds')
  Select the format Duration(1:23)in the formatting tab of the field customization panel.

Define the field formatting option as a duration

If you want to use geofencing, just add two number fields to store the GPS condition for check-in and check-out. If the user is in the delimited perimeter when scanning the QR code the GPS condition value is set to “1” or else it is set to “0”.

Your Airtable base is set up! Last straight line : create a Zap to connect the QR code to the timesheet.

Step 3 - Create a Zap to log events when a QR code is scanned

Connect to Zapier and watch the following videos to learn how to configure the Zap.

Note: when connecting Zapier to Ubiqod, you’ll be required the API key. You can find it in the top left Account menu of the Ubiqod platform.

Open the Account menu to get your Ubiqod API key

Overview: what type of builgding blocks are used in the Zap?

https://youtu.be/5hTGvILEAr0

Full walkthrough to setup the Zap

https://youtu.be/veTrLKqgVHs

You’re now ready to test!

• Scan the QR code a first time and enter one existing PIN codes: a new line is inserted in your timesheet and the worker name corresponding to the code is retrieved.
• Wait for a few minutes 🕑
• Scan the QR code a second time and enter the same PIN code: the previously created line is updated with the check-out time and the working time field is computed.
• If another scan occurs the same day with the same PIN code: a new check-in line will be created.
• If a scan occurs with another PIN code: a new check-in line will be created.

Congrats!

Now when workers arrives at work, they just scan the QR code and the timesheet is updated! We’ve built a simple and efficient app to collect proof of execution and compute working times.