How to Automate Inventory Tracking in Airtable

Airtable is well-suited for inventory tracking when the data structure is right. A single Products table with a manually updated stock field works for small inventories but breaks quickly: you lose history, you cannot see what changed when, and bulk updates require editing dozens of records individually.

The correct approach uses linked tables and rollup fields to calculate stock levels automatically from a running ledger of movements.

Airtable inventory dashboard showing products, stock movements, and low stock alerts

The Three-Table Structure

Products Table

Each record is one product. Fields:

  • Product Name (single line text or formula combining code + name)
  • SKU (single line text or autonumber-based formula see how to auto-generate invoice numbers in Airtable for the padded number pattern)
  • Category (single select)
  • Unit (single select: pcs, kg, L, etc.)
  • Reorder Threshold (number) the minimum stock level before a low-stock alert fires
  • Current Stock (rollup calculated from Stock Movements, see below)
  • Low Stock (formula): {Current Stock} < {Reorder Threshold}

Stock Movements Table

Each record is one stock event: a receipt, a dispatch, an adjustment, or a return. Fields:

  • Product (linked record to Products)
  • Movement Type (single select: Received, Dispatched, Adjusted, Returned)
  • Quantity (number always positive)
  • Signed Quantity (formula):
IF(OR({Movement Type} = "Received", {Movement Type} = "Returned"),
 {Quantity},
 -{Quantity}
)

Receipts and returns add to stock. Dispatches and adjustments subtract. The signed quantity is what the rollup sums.

  • Date (date)
  • Reference (single line text order number, delivery note, etc.)
  • Notes (long text)

Back in Products: Current Stock Rollup

Add a Rollup field to the Products table, rolling up through the Product linked field in Stock Movements, summing the Signed Quantity field:

SUM(values)

This field always reflects the live stock level as the sum of all movements. No manual updating needed. When a new movement is recorded, the product's Current Stock updates automatically.

Because it's a rollup, Airtable won't let you type a number into it directly, which is the point. Don't work around this by adding a separate manual stock field next to it. If a physical count doesn't match what Current Stock shows, the fix is to log an Adjustment movement for the difference, not to override the number. That way the ledger still explains every change to stock, including corrections.

Why the Ledger Approach Is Worth the Extra Table

A single editable stock number can't answer basic questions: how much came in last month, who dispatched the last 50 units, or why the count doesn't match what's on the shelf. The Stock Movements ledger fixes that by design.

  • Audit trail. Every change to stock is a record with a date, a type, and a reference, not an overwritten number.
  • Automatic calculations. Current Stock is always correct because it's computed from movements, not entered by hand.
  • Easier reporting. You can group Stock Movements by product, date, or type to see trends, without building anything extra.
  • Fewer errors. There's no way for someone to accidentally set stock to the wrong number, since the field isn't editable in the first place.

Recording Stock Changes

To record a receipt: create a new Stock Movements record, link it to the product, set Movement Type to Received, enter the quantity. The product's Current Stock increases immediately.

To record a dispatch: same process with Movement Type set to Dispatched. Stock decreases.

For frequent or repetitive entry, like receiving a delivery of 20 products in one sitting, a form simplifies the process since whoever's doing the counting doesn't need direct table access. Each submission still creates one Stock Movements record, so it's a faster way to enter many records in a row rather than a way to log several movements in a single record. See how to automatically link Airtable form submissions to existing or new records for the pattern of linking a form submission to the correct product automatically.

Low-Stock Alerts

With the Low Stock formula field on each product, setting up an alert is straightforward.

Create an automation:

  • Trigger: When record matches conditions on the Products table, where Low Stock is true
  • Action: Send email or Slack message with the product name, SKU, and current stock level

Add a Low Stock Alert Sent checkbox field and include a condition that it is unchecked, then check it after sending. This prevents repeated alerts for the same product. Reset the checkbox when stock is replenished above the threshold via a second automation.

Optional: Location Tracking

Add a Locations table if inventory is spread across multiple warehouses or storage areas. Each Stock Movement record can include a linked Location field indicating where the movement occurred, which means the same Stock Movements table already gives you a stock history per location, not just per product.

If you need a live Current Stock number broken out by location rather than just a total, add a Product Locations junction table with one record per product and location combination. Link each Stock Movement to the matching Product Locations record instead of, or in addition to, the product directly, then roll up Signed Quantity onto Product Locations the same way you did onto Products. For a quicker option without a new table, filter the Stock Movements view by location and read the totals from the summary bar at the bottom of the grid.

Recording Movements by Scanning

Manually creating a Stock Movements record for every receipt or dispatch works, but it's slow at any real volume. Scanning a barcode to trigger the movement is faster and reduces data entry mistakes, since the person handling stock doesn't have to type in a SKU or search for the right product.

Airtable's built in Barcode field only stores and displays a scanned value on a record. It identifies which product a scan belongs to, but it doesn't create Stock Movements records or update Current Stock on its own. To actually automate a movement from a scan, you need something that scans a barcode, matches it to a product, and creates the Stock Movements record for you.

This is what QuickScan is built for: it turns a barcode scan into a Stock Movements record automatically, so receiving or dispatching stock becomes scan the item, confirm the quantity, done. Worth a look if you're setting this system up for a warehouse or storeroom with any real volume of daily movements.

Where This System Can Grow

The Products and Stock Movements tables cover core inventory tracking on their own. As the setup matures, two natural extensions are Purchase Orders, to track what's on order from suppliers before it arrives as a Received movement, and Sales Orders, to tie a Dispatched movement back to the order that caused it. Both fit the same pattern: a table of linked records feeding movements, rather than editable numbers scattered across the base.