Overview: I built a custom time tracking payroll software using Airtable+Zapier for the GEMS factory in Ohio, helping them avoid over $30k worth in development fees they were considering in order to build a bespoke app. The app is currently used by the admin team and saves over 2 hours/day + countless errors in manual transcription that add up.
GEMS is an Engineering and Manufacturing firm with specialty automation and design located in Ohio; the firm operates on a contract basis where multiple client projects are occurring simultaneously at any point in time, and there are is a roster of contract workers that are hired on a per-project basis.
The problem: Using a paper-based time tracking system is becoming unscalable as the team grows, and the hours of work required to manually tally up hours is unsustainable.
The administration team currently uses a paper-based time tracking system that contract workers fill out at the end of the shift. The goal of this paper-based system is to not only calculate the total pay for each worker but also to be able to categorize all the hours that have been worked on a specific project so the client can be billed transparently and accurately.
Exploring current solutions: time tracking software currently costs a lot (especially because they’re usually billed on a per-worker system), and they’re not designed around the unique project-based approach that GEMS uses.
The team was exploring hiring a custom dev team to build out a bespoke system for their needs, the cost of which would have been $30k+.
Instead, I proposed using airtable + zapier to build them a bespoke solution at a fraction of the cost
Why Airtable + Zapier?
- Airtable essentially serves as a “database” that you can directly interact with, while being able to incorporate lightweight logic and visualize data
- Zapier uses simple “if, then” logic to automate certain processes. In this case, Zapier could automatically run calculation funcitons every time a worker inputted a new self-reported timesheet, and automatically generate payroll reports at certain intervals.
Designing an effective workflow
I wanted to help them create a tool that wouldn’t be too much of a learning curve as the workers shifted over. We worked with the employees and the administration team to determine a simple “ideal-case-scenario” for how to collect information
Airtable for timesheet:
In terms of conceptualizing this database, I had to think in an object-oriented fashion. This meant creating three types of “objects”
- Each client project (associated with a specific number)
- Each worker (identified by their name)
- Each work item. This is the central source of truth (the primary key in this case), that everything else would be linked to.
At the end of each day, a worker currently fills out his timesheet and plugs in how much time he worked for the project.
From this form, you can see how the “work item” object includes these corresponding fields:
- The “job number” that is associated with each client project
- The worker’s name, which is associated with each contract worker
- The time worked for the particular project
Now with every new form entry, multiple of these “work items” can be created, where each entry is tied to a job. So this set up the main components of the database, but how might this app generate payroll reports?
That’s where Zapier comes in
At the end of the 2 week period, it
- Combines hours worked for ONE WORKER
- Holiday + vacation + regular hours = x. If x > 80, then it will do overtime
- Combine the hours worked under each JOB NUMBER
Zapier also runs everytime a new record is created. Here, I wanted to use that functionality to automatically email each worker with the payroll report, so the process is further automated.
- Find all records with same WEEK NUMBER && same NAME
- Email all those records to the worker.email
The no-code advvantage
For the sake of conciseness, I chose to avoid diving into some of the complexities of how this app was developed, but the major point I hope to make here is how no-code technologies can truly provide up to 90% of the custom functionality of an app at a fraction of the cost and development time.
As we rolled out this timesheet, we were also able to quickly incorporate feedback, when incorporating the same simple changes in a traditional app development environment might’ve taken weeks. The beauty of this was also that the admin team could be trained to understand how to use and maintain Airtable on their own, which made this product scalable in the long term.
Anants Blog 