How to Automate Inventory Tracking with Airtable

Spreadsheet-based inventory tracking ends the same way every time. A SKU runs out and nobody notices until a customer complains. A different SKU sits gathering dust because nobody updated the count after the last delivery. Stock figures stop matching reality. Eventually the team gives up on the spreadsheet and starts walking around the warehouse with a clipboard.

Airtable sits comfortably between "spreadsheet that lies to you" and "full enterprise inventory system that costs $50,000 to implement." It handles real-time stock tracking, reorder alerts, supplier management, barcode scanning, and the ecommerce integrations most small businesses actually need — all on a base any non-developer can maintain.

This guide walks through the system end to end. You'll build it once, integrate it with your sales channels, and it'll run quietly in the background.

The Schema

Five tables cover the basic case. We'll add complexity in stages.

Products

• Name (primary)
• SKU (single-line text)
• Barcode (barcode field)
• Category (single-select or linked → Categories)
• Image (attachment)
• Cost (currency)
• Sale Price (currency)
• Reorder Threshold (number)
• Reorder Quantity (number)
• Current Stock (rollup — sum of Stock Movements Quantity)
• Status (formula — IF({Current Stock} <= {Reorder Threshold}, 'Needs Reorder', 'OK'))
• Supplier (linked → Suppliers)

Stock Movements

• ID (primary, autonumber)
• Product (linked → Products)
• Quantity (number — negative for outbound, positive for inbound)
• Type (single-select: Sale / Purchase / Return / Adjustment / Write-off)
• Reference (single-line text — order number, PO number, etc.)
• Notes
• Source (single-select: Shopify / Manual / POS / etc.)
• Date

Suppliers

• Name (primary)
• Contact Email
• Lead Time Days (number)
• Default Payment Terms

Purchase Orders

• PO Number (primary)
• Supplier (linked → Suppliers)
• Status (single-select: Draft / Sent / Acknowledged / In Transit / Received / Cancelled)
• Order Date, Expected Date, Received Date
• Total (rollup of PO Line Items Amount)

PO Line Items

• Description (primary)
• Purchase Order (linked → Purchase Orders)
• Product (linked → Products)
• Quantity Ordered, Quantity Received
• Unit Cost, Amount (formula)

The relational shape: Products link to Stock Movements (many), Suppliers (one), and indirectly to Purchase Orders through PO Line Items. For the patterns, see Linked Records Explained.

The Stock Movements Pattern

The single most important idea in this whole setup: never edit stock levels directly. Always create a stock movement.

A Stock Movement is one transaction that affects inventory:

• Sale: quantity -5 (five units shipped to a customer)
• Purchase: quantity +50 (fifty units received from a supplier)
• Return: quantity +1 (one unit returned by a customer)
• Adjustment: quantity -2 (two units written off after damage)

The Current Stock rollup sums all movements for a product. This means:

• You have a complete audit trail of every change
• You can answer "where did these units come from?" or "when did we last receive stock?"
• Stock can never get out of sync silently — every change is a visible record
• Reverting a mistake is trivial (delete the wrong movement, the rollup adjusts)

This pattern shows up in proper inventory systems for the same reasons. Airtable just makes it visual.

Setting Up the Reorder Alert

With the schema in place, the reorder logic is almost free.

The Status formula on Products:

IF({Current Stock} <= {Reorder Threshold}, 'Needs Reorder', 'OK')

Build a view called Needs Reorder filtered to Status = 'Needs Reorder', sorted by Reorder Threshold - Current Stock descending (most urgent first).

Now build a daily Make scenario:

1. Trigger: Schedule, daily at 8am.
2. Airtable Search Records on the Needs Reorder view.
3. If the result has any records, send a Slack message to the operations channel: "X products need reordering this morning. Open the dashboard: [link]."
4. Optionally, group by Supplier and send the supplier a draft purchase order email directly — but most teams prefer human review before sending.

For thresholds that adapt to actual sales rate, replace the static Reorder Threshold with a formula based on a 14-day moving average. That requires either a scheduled script that recomputes nightly, or a separate Sales History view rollup that feeds the threshold field.

Barcode Scanning in the Airtable App

This is the feature that wins over warehouse teams. The Airtable mobile app has a built-in barcode scanner that works on any phone camera.

To use it:

1. On the Products table, add a Barcode field type (not text — the dedicated Barcode type).
2. Populate barcodes on each product. You can scan them in with the app, type them, or paste from a supplier export.
3. Build a mobile interface with a Record Review layout filtered by barcode lookup.

The warehouse workflow becomes:

• Worker picks up a product
• Opens the Airtable app, taps Scan Barcode
• The matching product appears
• They tap a button: "Record Sale," "Record Return," "Record Damage"
• A new Stock Movement record is created with the right quantity sign

The button action is a button field that opens a pre-filled form or runs an automation. The whole interaction is a few seconds per item.

Integrating With Shopify

The most common ecommerce sync. For other platforms (WooCommerce, BigCommerce, Squarespace) the pattern is identical with their connector.

Outbound sales (Shopify → Airtable stock decrement):

1. Make Trigger: Shopify Watch New Orders.
2. Iterator: Shopify order's line items.
3. Airtable Search Records on Products by SKU (from the line item).
4. Filter: continue only if the product was found.
5. Airtable Create Record on Stock Movements: Product = matched product, Quantity = negative line item quantity, Type = Sale, Reference = Shopify order number, Source = Shopify.

The Current Stock rollup updates automatically.

Inbound stock (Purchase Order received → Shopify stock increment):

A second scenario watches Purchase Orders for status changes to Received:

1. Make Trigger: Airtable Watch Records on Purchase Orders, view Just Received.
2. Search PO Line Items linked to the PO.
3. Iterator over line items.
4. Airtable Create Record on Stock Movements: Quantity = positive line item Quantity Received, Type = Purchase.
5. Shopify Update Inventory Item: increment the Shopify inventory for the matching SKU.

This second scenario is what keeps your Shopify storefront accurate. Without it, your warehouse has 50 units but Shopify still thinks you have 5 and shows "Out of Stock" on the product page.

Two-way drift. If both Shopify and Airtable can update inventory independently (manual edits in Shopify admin, for example), you'll get conflicts. The cleanest discipline: pick one as the source of truth (usually Airtable), and treat Shopify's stock count as derived. All manual adjustments happen as Stock Movements in Airtable.

Multi-Warehouse Setup

The basic schema assumes one location. If you have multiple warehouses or storage locations, add complexity in stages:

Stage 1: Add a Locations table. Each Stock Movement gets a Location link.

Stage 2: Replace the simple Current Stock rollup on Products with a per-location calculation. You have two options:

• Conditional rollups. Add one rollup per location (Stock at Warehouse A, Stock at Warehouse B). The rollup sums Stock Movements where Location = the matching location. Works for 2-5 locations; gets unwieldy beyond that.
• Stock Levels table. Create a separate Stock Levels table with one record per (Product, Location). A rollup on each Stock Level record sums movements for that product+location combo. The Products table no longer has a single Current Stock — instead, you look at the Stock Levels table.

Stage 3: Add Transfers as a Stock Movement type. A transfer from Warehouse A to Warehouse B is two stock movements: -10 at A, +10 at B. The Make scenario creating the transfer fires both writes, but Make does not provide a transactional guarantee — the two writes can succeed or fail independently, so a crash or API error between them leaves a one-sided transfer in the database. To prevent that, design the scenario for resilience: give each transfer a unique Transfer ID and stamp it on both movement records (idempotency key) so retries don't double-write, then add an error handler on each Airtable create step that either retries the missing opposite movement or creates a compensating reversal of the side that did succeed. A separate scheduled scenario can also scan for orphan transfers (a movement whose Transfer ID has no counterpart) and flag them for human review.

For multi-warehouse, the Stock Levels table is worth the up-front work. It cleanly answers "how much do we have at each location?" without making the Products table sprawl.

Connecting to Accounting

The inventory side connects naturally to accounting in two places:

Cost of Goods Sold tracking. Each Sale stock movement multiplies the product's Cost by the quantity to give the COGS for that transaction. A rollup on Products gives lifetime COGS per SKU. A separate view gives COGS by date range for monthly accounting.

Inventory asset value. Current Stock × Cost = the asset value of that SKU on the books. A summary view or interface dashboard shows total inventory value across all SKUs — a number your accountant needs at every period end.

For pushing these numbers into your accounting tool, our invoice processing tutorial covers the Make patterns for Xero. The same patterns work for inventory adjustments — create a journal entry in Xero for monthly inventory revaluation, monthly COGS posting, etc.

Reports You'll Actually Use

Build these as Interface Designer pages on top of the base.

Daily Operations dashboard:

• Total SKUs (number)
• SKUs Needing Reorder (number, linked to the filtered list)
• Inventory Value (sum of Current Stock × Cost)
• Stock Movements Today (count from the Stock Movements table)
• A filtered list of today's movements

Slow-Movers report:

• Filtered list of Products where Current Stock > 0 and last Stock Movement was over 90 days ago
• Useful for identifying tied-up capital

Top Sellers report:

• Products grouped by total quantity sold in the last 30 days (via a rollup with date filter)
• Helps inform reorder quantity decisions

Stockout-Risk report:

• Products where Current Stock is below (daily sales rate × supplier lead time)
• A more sophisticated reorder signal than the simple threshold

The Slow-Movers and Stockout-Risk reports are where automated inventory tracking starts to make money for the business — visibility you couldn't get from a manual spreadsheet.

Edge Cases

A few realities of inventory that the basic schema doesn't immediately handle.

Lot tracking. If you need to track which batch a unit came from (food, pharma, electronics with serial numbers), add a Lots table. Each Stock Movement links to a Lot. Reporting can then trace which lot a sale came from.

Product variants. A T-shirt in S/M/L/XL × Black/White/Red is 12 SKUs. The cleanest pattern is each variant as a separate Product record. If your sales channel uses parent/variant structure (Shopify does), capture the variant SKU explicitly in the SKU field on each Airtable Product record so the Make scenario can match correctly.

Returns and damaged stock. Returns are positive stock movements. Damaged-on-return is a returned movement followed by a write-off movement — two records, clean trail.

Negative stock. A sale can drive Current Stock below zero if your channel oversells. Don't filter these out — show them in red on dashboards so the team can investigate. A negative stock value is a real-world event you want to see, not hide.

Bulk imports. When you first set up the base, you'll import current stock as one big batch of stock movements (one per product with Type = Adjustment and Notes = "Initial import"). Don't manually set Current Stock — let the rollup populate from the movements.

When to Outgrow This Setup

The pain points that signal it's time for a dedicated inventory system:

• More than 10,000 active SKUs
• Hundreds of stock movements per hour at peak
• Multi-warehouse with transfer logistics complex enough to need its own UI
• Compliance reporting (lot tracking with regulatory audit, FDA, etc.)
• Multi-currency accounting tightly integrated with stock valuation
• Need for demand forecasting that pulls from external signals

At that point, look at Cin7, Unleashed, Brightpearl, NetSuite. Airtable can still play a role as a working surface on top of those, but the system of record moves.

For most small businesses, the Airtable setup in this guide is enough for years. The schema we use today on client projects is essentially what's written here.

Where to Go Next

For the broader automation patterns powering this guide, see our Make automation guide — the iterator and search patterns used here are core. For invoice and PO workflows that hang off the inventory system, the invoice processing tutorial covers the Xero integration side.

For Shopify-specific module reference, Make's Shopify integration page lists every action with current API limits.