How Airtable rocked my world with a payroll database, magazine editorial calendar, and easy, secured forms!
I am a new convert to the gospel of Airtable. Amen. It’s true. It makes me happy. Also, I am reminded that I miss the challenge of working with a database. I’m serious. I used to work with databases every day as a Business Analyst and it was my favorite part of my job.
I went to dinner with some dear friends a bit ago. The four of us met over a decade ago when we all worked in IT at our previous company. What drew me to them were two qualities they each of these three women possessed: kindness and competence.
At our dinner, I was excited to tell them about Airtable. I’d previously told my husband and my mom (and business partner), and they both told me it sounded pretty great. These three friends, though, were the right audience, with their past experiences writing reports, testing applications, querying databases, and son on! As I explained to them one of my particular use cases (for the process of managing the payroll and taxes side of my small business, Opus 26), Nichole piped up, “You need a database.”
See? They got it.
I first learned about Airtable from Nineveh Madsen, who invited me to join the OpenVPN table for the Women Tech Council’s awards. She told me how they used Airtable for their content as well as for her side hustle, HER Magazine.
“Seriously,” she said. “Check it out.”
A week later, I sat down to check it out as I was putting together some possible process improvement options for Flagship Publishing, which publishes Nebraska Life, Colorado Life, and Utah Life. They used spreadsheets for managing their editorial content. I had the idea to put together some Trello boards as possible options to manage it better, based on my personal experience with the tool as well as my “past life” as a Scrum Master.
I also created an Airtable base for them and had far more fun with that.
Airtable is a spreadsheet-database hybrid. Basically, it displays the database as a spreadsheet, which most of us are familiar and comfortable with. You can link between tables, set up views of the data, integrate with other applications, and set up forms for collecting data.
Here are my top 5 ways it has changed my life
I got to work in databases again
Technically, I’m not actually writing SQL queries like I used to, but the ability to figure out the best way to set up tables, linking them to each other, and determining the correct column types was a good challenge. I watched some instructional videos in order to understand how to write formulas for some of the columns, giving me the ability to automatically define editorial dates, sum total amounts, and calculate the total number of pages based on articles assigned to an issue. Exciting things like that!
The example above shows the formula I used to automatically populate the date for when “photos requested for high resolution” should be submitted based on the date the content was uploaded. There were a number of other deadlines, based on a specific date, which meant once I selected that, all of these other columns populated automatically. Yeah, I cheered when that worked out perfectly!
I saved so much time with my Payroll process
With Opus 26, I work with my mother to hire and pay musicians for recording and performing jobs in Utah. She’s the walking rolodex (knows all the best musicians for the job) and I’m the payment side. Previously, I used a combo of a spreadsheet and a CRM to do this. Can I tell you how obnoxious it is to search for the musician’s name on my spreadsheet of hundreds of rows so I can enter their payroll amount for the specific job?
Now that I’ve set up everything in Airtable, I simply start typing the musician’s name in my “checks” table and it pulls the choices from the “musicians” table (see example above). Look how it’s keeping track of who’s getting paid how much and for which job and gives me the option to easily add a new record when we hire a musician we haven’t hired before. I really can’t put it into words how much this has saved me time.
Airtable views are a tax-time saver
Having data is a must when it comes to taxes. I learned last year, that I needed to do a better job of keeping track of who sent checks and compile amounts when we received more than one from the same account. My numbers weren’t matching the 1099-MISC forms they sent. Now, I simply switch to an Airtable view, which groups the checks I’ve received by accounts, telling me the total amount of them all. Just like that, I have a list of the 1099-MISC forms I am expecting to receive the first of next year.
I’ve also used views to give me:
- A list of all musicians we need to send 1099-MISC forms
- Which musicians we need W-9 forms on file
- Which musicians we already have W-9 forms on file
- How many we paid in 2018
- The total amount we’ve invoiced for contracting and payroll in 2018
Basically, views give me reports. Woo!
It’s the coolest editorial calendar tool
I grabbed an existing Airtable template for an editorial calendar and began customizing it for Flagship Publishing. If you click on the image above, you can see it in larger format. It’s example information so it’s not the actual, working calendar. It depends on whether or not they decide to use it for their process/business. But, if they do, I hope they find it as awesome as I do. Here are a few things I did to customize it that I thought were cool:
- I could set it up with a drop-down choice for which magazine: Colorado, Nebraska, or Utah.
- When I entered the Issue Date, the other fields (note the “fx” in the titles, denoting they’re formula-driven), auto-populated with their dates based on defined deadlines! (This was the coolest part to me after I learned how to do it correctly from reading through documentation.)
- It was so easy to put together the calendar and all of the deadlines for the next year. It would probably take about 30 minutes to set it up for the next ten years!
- A column not pictured showed you the articles included in the issue, linked from another table.
- It automatically calculated the total number of pages for each issue, based on those linked articles.
Last, the template included an image attachment column so once you have the magazine cover designed, you can include it. Then, when you flip over to a Kanban board view, how cool is the visual of the magazines in the queue?
Easy to set up secure web forms
One of the challenges of working with many freelance musicians is collecting W-9 forms. In the past, we’ve brought papers to a recording job or concert and asked them to fill them out right then, take them back to the home office, and scan them in. Also, we’ve had people email them to us, which, understandably, is not the most secure way. Now, we’ve provided them a form on our website, which is secured with HTTPS using SSL so all they have to do is enter their name, email, and attach the PDF.
It sounds really simple, right? As a freelance writer myself, I’ve encountered a number of businesses that don’t offer me a secure way to send my W-9 form to them.
Now I’m searching for more reasons to setup Airtable bases
So basically, I’m wishing that I had more chances to set up new databases. It was a lot of fun and challenging to stretch my mind to figure out the design of the tables, how to link them, views I could use, etc.
Did you know that learning new things, whether it’s a language, hobby, or a cool tool in the cloud, helps your brain? I’ve been having a good time treating my brain well lately.
Up next on my Airtable to-do: customizing integrations with Zapier. I’m working through that learning curve in my spare time!