How to use Airtable for accounting and bookkeeping firms

There are many out-of-the-box project management options out there for accounting and bookkeeping firms, but if you want something truly customized and built to your specifications, you’re going to want to use Airtable.

Airtable is not project management software. Rather, it’s a database management application that allows you to build a database without the need to know any code.

This is what it looks like when you start a new Airtable Base from scratch; click on any image for a larger, high-res version, and here is a link to the actual Airtable Base.

Setting up Airtable for your accounting or bookkeeping firm

This is more than some assembly required. That’s the downside. The upside is that if you can conceive it, you can create it.

Think of QuickBooks® Online, or, better yet, QuickBooks Online Advanced.

You’re dealing with a very robust database, containing lots of lists. Here are some examples of the type of information you can set up in Airtable:

• Products and services
• Clients
• Projects/jobs
• Employees, or as I like to call them, “nerds”

When tracking who’s doing what, when, where, and how, it’s nice to be able to do it on your terms. Airtable can be customized to give you exactly what you need.

I’ve looked at a lot of project management tools over the years. To this day, none of them give you a really clear view across every project on who’s doing what, when, where, and how. Airtable actually makes it very easy to do this, and there are some “blocks” we can use to make it even easier to analyze.

But, first, we must build the database. Then, we have to fill it with data. After that, we can analyze it. And, analyze we will!

With Airtable, each column can be set up from a robust list of formats. They can be text, URL, Phone, email, currency, %, drop-downs, linked to another record (which we will use a lot), formulas, and many more.

What’s in an Airtable Base

When you click to add a new column, the default is single line text, but if you click that drop-down, you can see all of the choices:

Now that you see the list, you can begin to conceive all of the different things you can keep track of in Airtable for your accounting or bookkeeping practice.

The other important thing to understand about Airtable is that the ability to link fields between tables makes this a powerful database building tool. What this means is that when we are building a database, any time we run into something that will likely be repeated a lot probably needs its own table.

Think of all of the things you would want to track in a task: customer, project, product or service, start date, due date, and employee (assignee), to name a few.

Each of these things is something we probably want to track in a separate table, so that we can track a lot of detailed information about them, and then link to them when we create a task.

So, the first thing we want to do is set up each of these tables. We will likely add more as we go, but this will be a good starting point.

Let’s set these tables up as follows:

• Products/services
• Clients
• Jobs/projects
• Employees (I call them “nerds” in my organization)
• Tasks

Create your tables

Don’t worry about the details yet – just create the tables. If you’ve created your Airtable Base, then the first table is there. Just rename it from table 1 (double click) to products/services:

Products and services

Now, let’s build the tables out. The first one is easy. It’s just the list of the products and services you offer. You can add whatever other details you need, of course. There’s a long text column in there by default, where you can describe the service and any details you like.

I’ll keep it simple for illustrative purposes, just to give you an idea. This will be used to populate drop-downs elsewhere in the database. You can add a currency column for your “Standard Rate,” and why not add a “Rate Type” column to give those rates context?

Clients

Next, let’s see what the client database looks like. Note, in this one, that we can build a form for new clients to fill out so their info drops right into our database. This means we don’t need to go hunting and gathering to get all of their information in. And, a quick Zap in Zapier can get them set up in QuickBooks Online Accountant, once the information is submitted from the form into Airtable.

Jobs/projects

Next we’re going to start creating a list of jobs or projects that we’re working on. This is the fun part because we now get to start linking things.

As we create each project, we need to assign a customer. The first column in each table is what is refer to as an “Index” column. This is the column that will have info we can “link” to from any other table. In a perfect world, each record (or row) in this column is unique.

For this jobs/projects table, we want to rename that first column and call it “Job or Project.” When we finish the rest of the columns, we are going to come back to this one and change its “type” to a formula, and we will use a “Concatenate” formula (just like the one in Excel) to combine info from the other columns and give each record its unique identifier. When this information is referenced from any other table, you can look up any other piece from any other column.

Next, let’s build the other columns. It helps to build it around a specific project and client, so let’s think of a standard monthly bookkeeping engagement, and what we need to track regarding that.

Link to products/services

For the first new field, let’s link to the products/services table, so that we can incorporate that into the project.

“Allow linking to multiple records” will be on by default, but you wouldn’t want any one project linked to more than one service. In the next screen, you’ll be prompted to add in lookup fields. You don’t need any at this point, so you can skip that for now.

Now, go ahead and select a product or service from that field. If you filled in the details like I had, it should look like this:

Once selected, you will notice that you can click on your choice and a popup will appear that gives you a window into that source record.

Link to clients

Next, we’ll want to assign a client. It’s the same process that’s linked to a different table. On this one, you might as well add the look up to the “Contact,” since that will surely come in handy here. It’s probably worth going back and adding a client to that table, so that you can fill this in and see how it starts to take shape.

And, while you’re in the jobs/projects tab, you can click into the linked clients field and add a new record to that clients table, right from within the jobs/projects table.

Fees (currency field)

Next, we need a currency field to establish the fee. You can use a lookup to bring over the standard fee for the product or service you chose, but keep in mind that each project may be quoted differently, based on the conversations you’ve had and other extra things you agree to do. So, you will want the flexibility of assigning the fee on a project by project basis. Then, you’ll want to repeat the fee type field here. In the video, I will show you a quick way of setting this up, since you already have it in the products/services section.

Jobs/projects – the first column

Now, let’s go back and build the first column, using a concatenate formula.

Double click on the column, and change the type to formula. Start typing Concatenate, and it will come up. Then, click into the parentheses so you can start building the formula. You will see how you can choose the various fields from the table, and you can also put literal components inside quotation marks, so that you can separate things, as follows:

CONCATENATE(Client,” – “,{Product / Service})

The rest of the setup

The employees tab should be filled in, so that you have your list of people you can assign to tasks. You can add as much detail as you want about each employee here, including their contact info, so you have it at close reach.

Once all of the above is set up, we can now start adding tasks. The beauty of this is that you’re going to see how you can add all tasks for all clients and projects in one big table. This allows you to easily see all tasks across all projects, and Airtable’s “Views” will allow you to carve out “reports” that show you only a particular client, project, or status, and so on.

In my video, I am going to populate my Umbrella Accounting database with sample data, so that I can show you what this might look like.

As you can see (especially once you watch the video!), if you use Airtable for your accounting or bookkeeping practice, there will be some setup required, but now you have something completely customizable.

If you can conceive it, you can create it.