Airtable Inventory and Order Management: A Stock Ledger That Actually Balances

Airtable is a good home for inventory and order management right up to the point where it isn't, and most teams find the edge the hard way: stock counts that drift, a reorder email that fires twice, or a "quantity on hand" rollup that silently counts a cancelled order. This tutorial builds a stock and order system in Airtable that stays honest — the data model, the movement-ledger pattern that keeps counts correct, reorder-point automation, barcode receiving, and the signs that you have outgrown the platform.

The model: products, movements, orders

The single biggest mistake is storing Quantity on hand as an editable number field on the Products table. Two people edit it in the same minute, one overwrites the other, and nothing in the base tells you which was right. Instead, treat stock as a derived value from an immutable ledger.

Four tables:

• Products — one row per SKU. Fields: SKU (primary, single line text), Name, Unit, Reorder point (number), Reorder quantity (number), Default supplier (link to Suppliers), Lead time days (number), Status (single select: Active / Discontinued).
• Stock Movements — one row per physical event. Fields: Movement ID (autonumber or formula), Product (link to Products), Quantity (number, signed: positive for receipts, negative for issues), Type (single select: Receipt / Sale / Return / Adjustment / Transfer / Scrap), Location (link to Locations), Date (date), Reference (link to Purchase Orders or Sales Orders), Entered by (created-by), Note.
• Purchase Orders and Sales Orders — headers, with a PO Lines / Order Lines table each (junction tables linking an order to products with a quantity and price). If that pattern is unfamiliar, read our guide to linked records and junction tables first.
• Locations — warehouses, vans, shelves. Even single-site operations benefit from Main vs Quarantine vs Customer site.

Stock on hand is then a rollup on Products: SUM(values) over the linked Stock Movements' Quantity. It cannot be edited by hand, it cannot be overwritten by a race, and every unit is traceable to the row that moved it.

Why signed quantities beat in/out fields

Some tutorials use separate Qty in and Qty out number fields. Signed quantities are better because the rollup is one formula instead of two, and because a correction is just another movement with the opposite sign. Never delete or edit a movement after the fact — post a reversing Adjustment row with a note. That is what makes a stock count auditable six months later when finance asks why March's closing balance moved.

To keep data entry safe, add a formula field Direction check:

IF(
  AND({Type} = "Sale", {Quantity} > 0),
  "⚠ Sales should be negative",
  IF(AND({Type} = "Receipt", {Quantity} < 0), "⚠ Receipts should be positive", "")
)

Filter a view to non-blank Direction check and you have a live error queue.

Available vs on hand vs committed

On hand is not what you can sell. Three numbers matter, and conflating them is how you oversell:

• On hand = rollup of all Stock Movements.
• Committed = rollup of open Sales Order Lines (quantity where the parent order status is Confirmed but not Shipped).
• On order = rollup of open Purchase Order Lines not yet received.
• Available to promise = formula: {On hand} - {Committed}.

Build Committed with a rollup that uses a conditional filter on the linked Order Lines (rollups can filter on linked-record field values), or carry a Counts toward committed checkbox formula on the line itself and roll up SUM(values) of a number field that is zero when the checkbox is false. The second approach is uglier but more reliable across plan tiers.

Reorder-point automation that does not spam

The naive automation — "when On hand is below Reorder point, email purchasing" — fires every time any movement recalculates the rollup while the product sits below its threshold. You get forty emails for one low SKU.

Make it idempotent with a state field:

1. Add Reorder status (single select: OK / Flagged / PO raised) to Products.
2. Automation A: trigger when record matches conditions — Available to promise ≤ Reorder point AND Reorder status is OK AND Status is Active. Actions: update Reorder status to Flagged, then notify the purchasing channel with SKU, available quantity, suggested order quantity, and supplier lead time.
3. Automation B: when a Purchase Order Line is created for that product, set Reorder status to PO raised.
4. Automation C: when a receipt movement lands and Available to promise is above the reorder point, set Reorder status back to OK.

The condition on Reorder status is the whole trick: the state field is the latch that stops the automation re-firing. The same pattern solves most "my automation ran 200 times" complaints — see automations that fail silently for the monitoring side.

For a smarter threshold, compute the reorder point rather than typing it: ROUND({Average daily usage} * {Lead time days} * 1.3, 0), where average daily usage is a rollup of the last 90 days of negative movements divided by 90. Review it quarterly.

Receiving against a purchase order

Receiving is where accuracy is won or lost. The workflow that holds up on a warehouse floor:

1. An Interface Designer record-review page lists open PO Lines for the selected PO with Qty ordered and Qty received so far (a rollup of linked receipt movements).
2. The receiver enters Qty received now in a form on that page.
3. An automation creates a Stock Movement (Type = Receipt, positive quantity, Reference = the PO Line, Location = the receiving location) and clears the input field.
4. A formula on the PO Line sets Line status to Open / Partial / Complete by comparing received to ordered. A rollup on the PO header sets the order status when every line is complete.

Never let the receiver type into the stock total. They type into an event; the total follows.

Barcode scanning works well here: a scan into a single-line-text field on a mobile form, matched to a product by SKU in a Find Records automation step. Airtable's mobile app supports barcode fields, and cheap bluetooth scanners act as keyboards, which means they work with any text input. The caveat is offline behaviour — see Airtable for field teams before promising scanning in a dead-zone warehouse.

Cycle counts instead of annual stocktakes

Add a Cycle Counts table: Product, Location, Counted quantity, System quantity (lookup captured at count time via automation), Variance (formula), Counted by, Date. When a count is submitted and the variance is non-zero, an automation posts an Adjustment movement for exactly the variance and links it to the count row. Now every adjustment has a documented reason, and Variance over time is your data-quality metric. Count your top 20 SKUs by movement volume monthly and everything else quarterly; that beats one chaotic December weekend.

Costing, briefly

If finance needs stock valuation, store Unit cost on the movement, not only on the product — the cost of what you received in March is not the cost of what you received in October. Weighted average cost is then a formula over the movement ledger, and FIFO is possible with a script but gets painful. If you need strict FIFO layers, multi-currency landed cost, or statutory inventory accounting, Airtable is the wrong tool for the ledger; keep Airtable for operations and sync valuations into the accounting system.

Scale limits to plan for

The ledger pattern trades record count for correctness, and record count is Airtable's constraint. Rough guidance:

• A base with hundreds of thousands of movement rows will feel slow in grid views and expensive to roll up. Plan an archive routine from day one: each month, move movements older than 24 months to an archive base and store the opening balance per product/location as a single Opening Balance movement so the rollup stays correct.
• Per-base record limits vary by plan; check yours before you model a high-volume e-commerce operation with 5,000 orders a week.
• Rollups over very large linked-record sets are a common cause of sluggish bases — our performance tuning guide covers the diagnostics.

Signs you have outgrown it: sub-second availability checks required by a storefront, serial-number or lot-level traceability across thousands of units, multi-warehouse allocation logic, or more than a few concurrent receivers hammering the same records. At that point Airtable becomes the front end and a real inventory system — or a Postgres service — becomes the source of truth. We wrote about that decision separately.

A sane build order

1. Products, Locations, Stock Movements, rollup for on hand. Enter a week of movements by hand and reconcile against reality.
2. Purchase Orders and PO Lines with the receiving interface.
3. Sales Orders, committed quantity, available to promise.
4. Reorder automation with the latch field.
5. Cycle counts and the variance report.
6. Archiving and costing once the first five are boring.

Resist shipping steps 4 to 6 in week one. An inventory base that people trust is built by proving the ledger balances before any automation starts making decisions on top of it.

BaseBrainers builds operations and inventory systems on Airtable, including the integrations into accounting and e-commerce platforms that keep the numbers in step. If your stock counts have stopped matching the shelf, tell us what is happening and we will take a look.