Entrepreneurship

Simple Retail Inventory

May 10, 2021

Airtable is an excellent platform for retail inventory management - in fact, Tesla uses it to keep inventory of vehicles leaving the factory. At it's core, inventory is a very simple math equation. The current inventory is the amount of units you bought or created, minus the amount you sold. However, when you have a lot of different items in your inventory, managing with a spreadsheet get's complicated - you either need to write a bunch of complicated SUMIF() formulas that subtract sales from received inventory, or you update inventory amounts manually, which is ripe for mistakes because there's no record of changes. Did I enter that last sale?

Enter the Airtable database. Instead of SUMIF formulas, we can make use of linked fields. For the purpose of this tutorial, I'll use the example of a clothing store. A simple inventory has three tables:

Products
Received
Sold

Your "Products" table holds all of the information about individual products. You can add as many different fields as you want, like clothing type (T-Shirt, Pants), color, size. Try to make the first field, usually called "Name," unique. I usually write a formula that combines the other fields into a full description, like "Green T-Shirts Size L." The "Received" table will track the date, product and quantity each time you receive (or create) some new inventory. The "Sold" table tracks the date, product, quantity each time you make a sale. The key to making the inventory work is to link the product field in the "Received" and "Sold" tables to the "Name" field in the "Products" table. Having done that, you can use Rollup fields in the "Products" table to sum received and sol items, and then simply subtract sold from received to get the current inventory. Check out the embedded example below, and feel free to copy it to start your own by clicking "copy base" in the bottom right corner.