How to keep track of inventory for a small business with Airtable

Managing inventory can be awful.

If you’re managing inventory without a system, well, you’re probably feeling really stressed out all of the time, especially during this holiday season.

Whether you’re managing inventory for a retail business or even just inventory of free promotional items to clients, this post is here to help you manage inventory in a systematic way using Airtable.

If you want to follow along, you can grab a free copy of this Airtable template using the link below.

So, without further ado, let’s jump into this Airtable base and see how you can track inventory of items in Airtable.

Transactions

First up in building this Inventory Tracker in Airtable is the transactions table. This table shows when any item enters or leaves your inventory. If one item enters or a million items leave at once, everything is shown in this table in this base. This is the most active table in this base.

In this base, there is name of the transaction, person responsible for the transaction, # of items, whether or not they are adding items, which campaign it may be a part of, a transaction date, items, and the inventory calculation field.

Let’s cover what each field means.

Transaction name: this one is more of a organizational or personal preference. This is where if you have a naming convention for files or anything else, it can be really helpful. In this Airtable base, I’ve made the naming convention of a transaction to be “# of Items” - ”Item” - “Date of Transaction”. This way, if you’re just quick glancing at the table, you can see quickly what a transaction might be about. Obviously, not all of the details, but that’s not what you’re looking for if you’re just quickly glancing through.

You could change this transaction name field to just a autonumber or even a barcode if you have that type of setup. You could also have a different formula for a naming convention other than the one I already provided in this template.

Person: Pretty simple, but could get complicated if you have other tools making transactions. If you don’t have an e-commerce platform making sales, this field is simple. It’s just whoever is taking items out at the time and they just select themselves.

If you do have an e-commerce platform making sales and shipping items, you would have to create an automation in Zapier between Airtable and the platform you’re using. That platform, for most I would guess, is Shopify. You could just have it not select a person or have a general account that represents the platform.

If you’re solely selling through your e-commerce platform, this field may not be that useful.

Number of Items: This one is pretty self explanatory. It’s just how many of a particular item are entering or leaving.

Adding: This field is simple once you understand it. This is to mark whether or not a transaction is for adding items into inventory or not. Since there’s most likely going to be more transactions to take rather than add items, I have it as add. Every time you add an item, it’s a click, so if the most common transaction doesn’t require an additional click, that’s the preference. You can flip it the other way around if you want. This field allows us to have adding items and subtracting items in the same table. We’ll build on this field in just a moment!

Campaign: Campaign is a linked record for a table that is optional. If you’re a venture that runs campaigns of any sort, you can link which transactions are part of what campaign. If you want to track how successful a particular campaign is, this can be a really helpful step. Campaign could also be converted into a program table or anything that helps you segment. Segmenting how transactions are occurring is helpful for predicting and iterating on your venture! Goes all the way back to your target markets!

Transaction Date: Another self explanatory field. It’s just when a particular transaction occurred. In this base, it’s the time the record was created, but if you want more flexibility in it, you could have it as just a standard date field. The time the record was created field is nice because again, it’s one less click.

Inventory Calculation: This field is essentially saying whether or not a transaction is adding or subtracting in terms of math. All it is taking the “Adding” field, and if it’s adding, have the number be a positive number. If it’s subtracting, have the number be subtracting. This important for keeping track of how much of a particular item you have in stock. How we do the calculation will be covered in just a moment in the Items table!

Items: Last, and the most important field in this table: the items field. This field is what item is being affected by this particular transaction. It’s a linked record to the Items table, so with that, let’s move on over to the Items table.

Items

This Items table is not the most active table in this base, but it’s most valuable table in this base. This table tells you how much inventory you have for every item you want to track in this inventory tracker.

One of the key parts to this table is that every variation of an item is a different item in this particular base. So, for example, if you have a t-shirt design with 3 different sizes, that’s 3 different items in this table.

If you want to get more accurate with naming, it’s 3 different SKUs aka Stock Keeping Unit. If you’re dealing with any type of retail and many items, SKUs are a pretty common term. If you wanted, you could rename this table to SKUs since that is the more accurate title if we’re being honest…

Anyways, let’s get into what each field means in this Items table.

Name: This name field is really similar to the name field in the Transactions table, you can make this whatever you would like. If you have a standard naming convention great. This is also where having a barcode can be really helpful. It’s a unique barcode for every SKU.

Price per Item: This one is pretty self explanatory. You may not need this field if other parts of your workflow already have this, but if you’re managing inventory for marketing materials in this, it can be helpful for knowing what items are more expensive. Additionally, you can see how much you’re spending on items for a particular campaign if you’re tagging the campaign in the transactions table as I mentioned earlier.

Type of item: I’ve left this field simple, but you could make it complicated if you have a lot of different items. In this template, I just made 4 basic products: a t-shirt, baseball cap, a hoodie, and a sticker. Since it’s basic, I’ve just made it a single select field.

If you have 50 different t-shirts though, this single select field probably isn’t great. This is where you could separate items from SKUs. Have a different table that is labeled as Items, and turn this table into SKUs. Then, you can have a linked record of the item, and SKUs are still generated here in this table based on the variation. In this linked record version, you would then be able to see all of the different SKUs for a particular item. Again, this can be a complicated field. If you’re not ready for it, don’t do it! You can always iterate into this more complicated structure later on.

Size & Variation: This field is simple and I just talked about it essentially. Just making unique SKUs based on the size or variation of a particular item. Could be another place where you could get more complicated with a separate table of all of the different sizes and variation combinations. Linked records once again!

Transactions: This field you most likely won’t ever touch in this particular table. It’ll always be edited from the Transactions table itself. This field will show you all of the transactions for this particular item. This might be a field you just hide if you don’t want to see it.

In Stock: This is the fun field! This is the field that is a rollup field and sums all of the inventory calculations together for that particular item in this table. This field tells you how much of a particular item or SKU that you have available. If you wanted to build automations where you have notifications of when items become out of stock, this is the field you would build the automation off of. Set it as if a number gets below a certain point, and it can send you an email or a Slack message. Lots of fun possibilities with this field.

Locations: This last field is locations. This is a linked record to the locations table. All this is telling you is where an item is stored. May not be that important if you don’t have that many items, but if you have a lot of different items and even just a few locations, this field is really helpful.

So, with that, let’s move on the Locations table!

Locations

The locations table is, well, stupidly simple. We’re definitely not going to spend much time talking about it like I have in the previous two tables.

All there is in this table is the location name, the room number, a photo of the location if you’d like, and the items in that particular location. All pretty self explanatory and not a lot to this. So, let’s move on to the last table and another really simple table: campaigns.

Campaigns

This campaigns table is an optional one, but can act as a results table in some ways. This table is a list of all of the different campaigns you may be running in your venture and allows you to see all of the different transactions related to a particular campaign.

The campaigns table is pretty bland here with only name of the campaign, owner of the campaign, start date, end date, notes, and a linked records field of transactions.

If you wanted to, you could create a rollup field from the transactions linked record and see how much in inventory you spent on a particular campaign, or even see how much revenue was generated by a particular campaign if you have revenue in the items table.

There’s only a list view in this table, but if you want a dashboard of sorts, you could create a gallery view here or create an Airtable interface that is an actual dashboard of results.

This Campaigns table is meant to act as a starting point, not an end point.

So, that’s the overview of an inventory tracker as an Airtable base. If you want to grab a free copy of it, you can use the link down below to get a copy and alter the Airtable template to your needs.