AirTable and Zapier
Creating a request tracker with AirTable & Zapier
There are lots of examples of how to use AirTable for team project tracking. Recently, I needed to create such a tracker that could also generate emails when new requests were made. Here's how:
AirTable form
First, a project request would be entered by an assistant on behalf of their manager. These requests would be managed by AirTable's really nice Form view.
To keep things organized this AirTable Form had two mandatory fields: 1) All requests had to be marked as "New" (This is a single select field, with the other options disabled), and 2) They had to enter today's date in the "Date Submitted" field. As of yet, I haven't found a way to automate either of these. The result is that all New requests are grouped together in our Dashboard, and sorted by date submitted.
The rest of the form collected the details of their project request, including the name of the person making the request, and the person submitting the form (these are both Linked Records from an adjacent table in the same AirTable base).
Once submitted, a new record would be created in the request table. This is when Zapier takes over.
Requests in the table
After a bit of a delay, Zapier would see the new record and send an outbound email with the details of the request. This email would be addressed to a set group as well as the individuals submitting the request. This part was really tricky, but I was pretty pleased with the solution.
Zapier configuration
You see, Zapier chokes a bit on linked records coming from other AirTable tables. Rather than giving you the values of the linked record's fields, it just reports back the ID of that linked record. To get around this, I created several rollup fields in the main table: One to collect all the email addresses of the linked records, one to collect all the names associated with the email address, and one to collect a few other pieces of relevant information. Since these rollup fields get delivered to Zapier as plain text, I could use them in the email "CC" field that I needed to populate. The rollup even properly separated the email addresses with commas.
Hidden Rollup fields in AirTable
Rollup Configuration
Finally, after the email came through, I had a rule on the mail server to forward the email to a Slack channel so the group had a collective record of the transaction. The group would often start a Slack thread connected to this imported email.
The specifics of this workflow are perhaps a bit peculiar...but isn't that where Zapier shines?
As for AirTable, its flexibility continues to amaze me. I’m at the point where nearly any project may start in AirTable. Their native toolset, to say nothing of their API, provide such amazing power in such an unassuming package.