Airtable inventory baseline: cutoff date method
Fix negative Airtable inventory with a baseline stock take and cutoff date method. See the exact table structure, formulas, and depletion logic.
TABLE OF CONTENTS
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.
