Airtable inventory baseline: cutoff date method

In Airtable, the fastest way to fix mysterious negative inventory caused by imported historical orders is to create a baseline stock take (a point-in-time snapshot) and then calculate purchases and sales only after a cutoff date. This turns a messy “all-time” inventory math problem into a clean “from here forward” system you can trust.

Why negative inventory happens (and why hiding it breaks your system)

If you import historical sales/orders without matching historical receipts, stock takes, or purchase orders, your rollups will legitimately go negative.

A common quick fix is:

Calculated On Hand = IF(On Hand < 0, 0, On Hand)

That looks better, but it creates a real operational problem:

If you’re at -1000 and you receive +900, your true balance is still -100… but your display stays at 0, so you can’t tell how much you need to buy to get back above zero.

The baseline method (the “from here forward” reset)

The baseline method is a controlled reset that preserves day-to-day accuracy going forward without requiring you to perfectly reconstruct the past.

Step 1: Pick a cutoff date

Pick today or yesterday as your cutoff date.

Use yesterday if you want to avoid partial-day edge cases.
Use today if you’re doing the baseline as part of a live implementation session and can clearly communicate “everything before now is ignored.”

Step 2: Create a Baseline Stock Take table (or baseline records)

Create a table (or record type) that represents a physical stock take snapshot.

Minimum fields:

Product (link to Products)
Baseline Qty (number)
Baseline Date (date)

How to populate Baseline Qty:

Use your best-known “actual count” today.
For any SKU that is negative due to missing history, set Baseline Qty to 0 (or the true physical count if it exists).

Step 3: Calculate movements only after the cutoff

Now your inventory math becomes:

On Hand (Current) = Baseline Qty + Purchases After Cutoff − Depletions After Cutoff

Where:

Purchases After Cutoff = sum of received quantities where Received Date ≥ Cutoff
Depletions After Cutoff = sum of depleted quantities where Deplete Date ≥ Cutoff

If you don’t have “Received Date” / “Deplete Date” fields yet, add them. Dates are the key that makes the cutoff method possible.

Airtable implementation pattern (tables + fields)

A common structure:

Products (one record per SKU)

Key fields:

Baseline Qty (lookup/rollup from Stock Takes)
Purchases After Cutoff (rollup)
Depletions After Cutoff (rollup)
On Hand (formula)
Reorder Needed? (formula)

Stock Takes (baseline + periodic counts)

Key fields:

Date
Product (link)
Counted Qty
Is Baseline? (checkbox)

Then create a lookup/rollup in Products for:

Latest Baseline Qty (rollup that selects the baseline record you want to use)

Purchase Orders (incoming)

Key fields:

Received Date (date)
Status (single select)
Line Items (linked to PO Lines) OR inline qty fields

Sales / Depletion (outgoing)

Key fields:

Deplete Date (date)
Status (single select)
Line Items (linked to Order Lines)

How to handle status-based depletion (only certain orders reduce stock)

Many teams want orders to “reserve” inventory without reducing available inventory until a later status.

Example: only deplete inventory when an order is:

Production complete
Awaiting pickup
Shipped

Implementation options:

Option A: Deplete Qty field that becomes 0 unless status qualifies

In your Order Lines (or Sales table), create:

Deplete Qty = IF(Status in qualifying statuses, Qty, 0)

Then roll up Deplete Qty into Products.

Option B: Two numbers: Reserved vs Available

If you need both:

Reserved Qty (earlier statuses)
Depleted Qty (later statuses)

Create two conditional fields and roll up both.

Edge cases and how to handle them

1) Old orders that ship next month

If an order was created months ago but ships next month, don’t filter by “Created Date.”

Use Ship Date / Fulfillment Date / Deplete Date for depletion.

2) Long-lead purchase orders

Likewise, don’t filter purchases by “PO created date.”

Use Received Date (or a status transition date) for additions.

3) Unit conversions (boxes → pieces)

If you buy in one unit but consume in another:

Store the base unit for inventory math (e.g., pieces)
Add a Conversion Factor per product
Convert line quantities into the base unit before rolling up

Example formulas (simple and practical)

These are concept patterns—adjust field names to your base.

On Hand (Products)

On Hand = Baseline Qty + Purchases After Cutoff − Depletions After Cutoff

Display On Hand (if you must avoid negatives)

If you need a “pretty” number, do it in a separate field:

Display On Hand = MAX(0, On Hand)

Keep the real On Hand available for ops.

Reorder Qty Needed

Reorder Qty = MAX(0, Reorder Point − Display On Hand)

What to do next (implementation checklist)

Choose cutoff date (today vs yesterday)

Create baseline stock take snapshot

Add Received Date and Deplete Date fields

Move inventory math to “after cutoff” rollups

Implement status-based depletion logic

Add a separate Display On Hand field if stakeholders need it

Get help building this

Setting up an Airtable inventory baseline usually breaks at the rollup logic — specifically when status-based depletion interacts with the cutoff date filter. If you’ve hit that wall, book a ZoomFlow session — one of our consultants can debug it with you live and ship the working version in the same call.