The power of Airtable
Airtable's power comes from the automations you can build on top of a relatively dumb collaborative database. In this post, we're going to go into explaining how we use airtable (and other no-code tools) to automate our contractor payment platform as an example of how no-code solutions can build powerful workflow tools.
What airtable is and what it is not
At first, airtable may come across as a worse google sheet. And, in a way, it is. You don't have access to as many formulas or flexibility. In a way, airtable better resembles MS Access: you can store data in structured databases/tables and you can create queries ("Views" in airtable lingo) that filter out the data. One of the unspoken and misunderstood challenges of Airtable is the trap of thinking of it as a spreadsheet. It is not a spreadsheet, so a lot of the effort that goes into using Airtable properly is to “unlearn” spreadsheets. You can't have a result line, you can't link to/call a cell.
Entering data can be easily done and shared via forms. For every table, you can automatically create the corresponding form, and share it with those that you want to give permission to add records to the table.
In our payment platform, we have a table with the contractor data (name, email, banking details, customer information) and a table with all the invoices from contractors that we pay. There's a few things we need to do every month:
Remind contractors to invoice us
Allow them to submit a invoice
Check that the invoice is correct/accurate
Doing all of these every month, with an increasing number of contractors, can be a daunting task, and the probability of making a mistake quickly grows to 100%.
Prior to building the automations with Airtable, I had a list of contractors which I chased to ensure they submitted the invoice, manually reviewed each invoice (consulting the contractor agreement signed, and any additional details) and logged the invoices and their details on Airtable. When we had 5 contractors, it was a relatively simple task. At 35 contractors, I started making mistakes.
Automate everything!
The goal of automation will be to create a system that alleviates the manual work and error-proofs the process of getting people paid. We will mostly use airtable automations and zapier to connect to a pdf reader.
Step 1: to remind people to send an invoice.
Every time an invoice is submitted we will link it to the employee record. So, inversely, we can create a view (aka. a query) in our employee table that shows the employee records that have not submitted an invoice yet.
We will also create a formula field to set a reminder trigger. This trigger will turn 1 when the day is one of a set of days in the month we pick to send a reminder.
or(day(now())=20,day(now())=24,day(now())=28,day(now())=30) With this formula, the field will turn 1 on the 20/24/28/30 each month. This will happen for all employees.
Now we can create our first Airtable automation: we will email a reminder to anyone who remains on the list on those 4 days.
Step 2: allow people to submit their invoice
I used to receive invoices via emails or slack. Some people would send me links i had to click on and then download the file. I would then go on to upload it to Airtable, extract the relevant information and fill the corresponding record on my table for invoices that needed to be paid. Painful.
Now, we follow these steps:
we use the form view of Airtable, that automatically enables a form to submit the invoice with their email address.
we connect the email address to the employee table and retrieve automatically all the necessary fields.
If the email submitted does not match the employee i get notified via email so i can go and quickly assess the problem and solve it.
Step 3: verify the information is correct
One of the biggest blind spots I had, was that for every invoice I would get I had to validate that the contractor had not made a mistake and was over/under-charging us. That meant dedicating a time consuming task: to open the invoice, check the total, go to the relevant employee record, retrieve the rate, and validate the math was correct.
Fortunately, we were also able to automate this process thanks to Docparser:
every time a new invoice is received and matched to an employee, we upload it to docparser.
docparser "reads" the invoice and sends back the relevant information.
the information is stored in the invoice record and automatically compared with our expected rate.
If there's a mismatch, I get an automatic email that summons me to go check the discrepancy.
Step 4: logging activity and documenting the automations
Having all these automations can be hard to track what happens, when. I believe it can be good hygiene to log when an automation completes and documenting how the automations are supposed to work. Right now we have 25 automations running on airtable and 37 zapiers.
We decided to document each one of the automations on notion: describing the trigger and the actions. So anyone can learn what each automation does and when.
We also decided to give visibility to all the tasks being executed by logging the activity in a database, and publishing it on a slack channel. It helps give visibility to what is running behind the scenes.
The cost
Running these services costs a bit of money:
we currently pay $360/month to have 15 collaborators on airtable. We use airtble for many other usecases, so it's safe to say the cost for this tool is significantly lower (at a minimum, could be $24/month).
Doc parser charges us $36/month for 100 documents processed per month.
Zapier's month to month plan (for up to 50,000 tasks per month) is about $373 per month. And again, we do a great deal of automations (about 20,000 tasks per month) so, it is only fair to account for a portion of that expense to our little invoicing workflow app.
I estime the cost (sans the hours to design, improve, maintain the system) runs at $150/month. But more importantly, it doesn't require much more money to scale to process 500 contractors instead of 50. Prior to airtable my two only solutions would have been:
to use a contractor management tool (costs about $50/month per contractor, or 10x our current setup cost)
to hire an admin team to do the task for me - it's probably 20x the cost or our current setup + the people management system.
build a software. It would have taken me a lot of time to build all the workflows and functionalities, and maintain a development team on staff dedicated to that system.
The potential
As the newly named Chief Airtable Officer i've started automating all sorts of tasks: preparing the invoices to our customers, automatically setting meetings with employees 30 days from when they start, requesting and managing PTO. We've build all of these with no development and without using any vendors (other than the specific point solutions required to solve a step: ie. like docparser).
Airtable, as we said, is a collaborative database. Most websites are made of databases. They have views (pages) and data structures that save/retrieve the information. Therefore, the true power of airtable is to become the backend of a modern application. Today, one can start building simple rudimentary web applications thanks to Stacker or Softr, but Airtable has long ways to go to become a defacto backend for a web-application. There's a long list of requests that today are unsolved, starting with data validation on input forms, that prevent us from adopting Airtable as a long-term solution for our web app.
Our next step will involve building a portal accessible by contractors and customers that "sits" on top of our Airtable+Zapier setup (ie. our back-end). To our customers we will be just another web application. But instead of having had to build v1 from scratch, we will have gotten 95% of the way without no development support. We can't wait to show it when we launch it!