How to Build an Airtable Inventory Tracker With AI Reorder Notes

Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Build an Airtable Inventory Tracker With AI Reorder Notes.
Coding Liquids tutorial cover featuring Sagnik Bhattacharya for How to Build an Airtable Inventory Tracker With AI Reorder Notes.

Build four linked tables in Airtable: Products, Suppliers, Stock movements and Reorders. Stock on hand is a rollup of movements, and a formula flags any product at or below its reorder point. A weekly scheduled automation turns each supplier's flagged lines into a Reorders record, where a field agent drafts the order email for you to approve and send.

The Complete Excel Course with AI Integration

From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.

By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner

Buy now

The split of work matters more than the AI. Formulas decide whether to order and how many; the AI only writes the message around numbers it is told to copy. Ask a language model to pick quantities and you get confident figures with no stock logic behind them. Ask it to turn "KP-2231, order 18" into a polite, correct email and it saves you ten minutes per supplier, every week.

What tables does an Airtable inventory tracker need?

An Airtable inventory tracker needs four linked tables, Products, Suppliers, Stock movements and Reorders, and stock on hand is never typed in. The illustration throughout is an independent kitchenware shop with 240 products from six suppliers, run by the owner and a part-time manager. The tables:

TableOne record is...Key fields
Productsa product you sellName, Supplier code, Barcode, Supplier (link), Reorder point, Par level, Case size, Stock on hand (rollup), Low stock (formula), Suggested order (formula), Reorder line (formula)
Suppliersa supplierName, Contact first name, Order email, Account number, Products (link), Items low (rollup), Reorder lines (rollup)
Stock movementsone change to stockDate, Product (link), Type, Quantity, Signed quantity (formula)
Reordersone order to one supplierSupplier (link), Lines, Created, Draft note (field agent), Approved (checkbox), Sent on

Nobody types a stock figure into Products. Every delivery, sale, breakage and stocktake correction is a row in Stock movements, and Stock on hand adds them up. That is the difference between a tracker and a spreadsheet of numbers people overwrite: when a figure looks wrong, the movements show why.

Stock movements needs one formula. Type is a single select with four options (Delivery in, Sale, Waste or damage, Count correction), and Quantity is always entered as a positive number except for count corrections, which carry their own sign. Signed quantity turns that into a plus or minus:

SWITCH({Type},
  "Delivery in", {Quantity},
  "Sale", -{Quantity},
  "Waste or damage", -{Quantity},
  "Count correction", {Quantity},
  0)

In Products, Stock on hand is a rollup of the linked movements' Signed quantity with the aggregation formula SUM(values). Start each product with a Count correction row equal to your opening stocktake.

How do you flag low stock and suggest an order in Airtable?

Airtable flags low stock by comparing stock on hand with a reorder point, using three numbers per product that you set, not the AI:

  • Reorder point: the stock level that should trigger an order. A workable starting rule is weekly sales multiplied by the supplier's lead time in weeks plus a safety margin.
  • Par level: what you want on the shelf after a delivery arrives.
  • Case size: the supplier's minimum pack, so orders round up to whole cases.

The formulas in Products:

Low stock:        IF({Stock on hand} <= {Reorder point}, 1, 0)

Suggested order:  IF({Low stock},
                     CEILING(MAX({Par level} - {Stock on hand}, 0), {Case size}),
                     0)

Reorder line:     IF({Low stock},
                     {Supplier code} & " | " & Name & " | on hand " &
                     {Stock on hand} & " | order " & {Suggested order} &
                     " (" & {Case size} & " per case)",
                     "")

CEILING with a second argument rounds up to the nearest multiple of it, which is exactly case rounding. Here is a filled-in set for four of the shop's products, with illustrative sales and lead times:

ProductSells a weekLead timeReorder pointPar levelCaseOn handSuggested order
Enamel pie dish 24cm41 week6186518 (13 rounded up to 3 cases)
Beech spoon set31 week51412212 (12 is one case)
Glass storage jar 1L62 weeks153012724 (23 rounded up to 2 cases)
Cast-iron skillet 26cm13 weeks46250 (not low)

The pie dish shows the arithmetic: par 18 minus 5 on hand is 13, and the next multiple of 6 is 18. The skillet shows why slow lines need a reorder point above zero: with a three-week lead time, waiting until it runs out means three weeks with none on the shelf.

In Suppliers, two rollups gather the lines. Items low is a rollup of Products' Low stock with SUM(values). Reorder lines is a rollup of Reorder line using the option to only include linked records that meet certain conditions (Low stock is 1), with the aggregation formula ARRAYJOIN(ARRAYCOMPACT(values), " ; "). ARRAYCOMPACT drops the empty strings from products that aren't low, so the result reads as a clean list.

If you want to get better at the formulas and rollups behind a tracker like this, try The Complete Excel Course with AI Integration on Coding Liquids.

How do you get sales into Airtable without filling the base?

Import sales into Airtable as one row per product per week, not one row per item sold, because record limits fill quickly. Plan this part before anything else: it is a record-limit problem before it is a workflow problem. Airtable's Free plan allows 1,000 records per base and Team allows 50,000. Do the sum for the shop:

How sales are recordedRows a yearTeam's 50,000 lasts
One row per item sold (about 90 a day, 6 days a week)about 28,000 sales rows, plus deliveries and correctionsunder two years, then archiving every year
One row per product per day sold (about 60 a day)about 18,700, plus deliveries and correctionsa little over two years
One row per product per week from a till export (about 150 a week)about 7,800 sales rows; about 11,000 with deliveries and correctionsmore than four years

Weekly is the sensible choice for a shop that orders weekly. Every Monday morning, export units sold by item from the till as a CSV and import it into Stock movements as Sale rows, matching on Supplier code or Barcode. The import takes about ten minutes once the column mapping is saved in your head; the week's sales then land in one go, just before the reorder automation runs.

Deliveries and counts go in at the shelf. A Barcode field lets staff scan items with the Airtable mobile app on iOS or Android, which opens the matching product record, so logging a delivery is scan, add a Delivery in row, type the quantity. For counting itself, running a stocktake faster with a phone scanner covers the routine, and every count difference goes in as a Count correction row so the history stays intact.

Why use a scheduled automation to build the reorders?

A scheduled Monday automation builds the weekly reorders more reliably than a trigger on the Low stock flag. The obvious design is an automation that fires when a product's Low stock flag turns to 1. Airtable's own help page on the When record matches conditions trigger warns against it: a condition built on a rollup can be checked before the rollup updates, and a missed match isn't retried. Its suggested alternative is an At scheduled time trigger with a Find records action, which also suits how a small shop orders: once a week, in one batch.

  1. Create an automation with the trigger At scheduled time, set for Monday at 7am, after the weekly sales import.
  2. Add a Find records action on Suppliers, finding records where Items low is greater than 0. The default limit is 1,000 records per run, far beyond six suppliers. Test runs preview only the first 10 records found; live runs pass them all.
  3. Add a repeating group (Airtable's feature for running actions once per found record) over the found suppliers.
  4. Inside it, add Create record in Reorders: link Supplier to the current supplier and copy its Reorder lines text into Lines.

Copying the text matters. Lines is a snapshot of what was low at 7am on Monday, so the draft and the approved order describe the same thing even if three more pie dishes sell before lunch. It also keeps AI costs down: the field agent reads a field that never changes, so it runs once per reorder rather than every time a sale moves the stock.

Check the first few Mondays' Reorders against the Suppliers view by eye. If a supplier with low items has no Reorders record, the Find records conditions are wrong; if a record has empty Lines, the conditional rollup is.

How does the field agent draft the supplier note?

A field agent in the Reorders table drafts each supplier note from the Lines text, using only the products, codes and quantities it is given. In Reorders, click + next to the last field, choose Build a field agent from the Field agents section, keep the Long text type, and pick a model marked Low cost in the Model dropdown. Field agents are on Airtable's paid plans. If you haven't set one up before, the walkthrough on summarising and tagging quote requests covers the menus and re-run rules in more depth. The instructions, with fields inserted by typing { and the field name:

Write a short reorder email from [shop name] to {Contact first name}
at {Supplier}. Our account number is {Account number}.

Use exactly the products, codes and quantities in {Lines}. Do not add,
remove, merge, round or reorder any line. Put each product on its own
line as: quantity x product name (supplier code).

Ask them to confirm the delivery date and to tell us if anything is out
of stock or has changed price. Do not mention prices, discounts or
promotions. Sign off as [your name]. Keep it under 120 words.

Contact first name and Account number are lookup fields in Reorders that pull from the linked supplier. Under Settings, turn on Run automatically and save, so each new Reorders record gets its draft without anyone clicking. Create one Reorders record by hand and check that the draft appears before trusting the Monday run.

For the Monday run in this illustration, the Lines field for the first supplier read: KP-2231 | Enamel pie dish 24cm | on hand 5 | order 18 (6 per case) ; KP-1180 | Beech spoon set | on hand 2 | order 12 (12 per case) ; KP-3307 | Glass storage jar 1L | on hand 7 | order 24 (12 per case). The draft (illustrative):

Hi [first name],

Please could we order the following on account [account number]:

18 x Enamel pie dish 24cm (KP-2231)
12 x Beech spoon set (KP-1180)
24 x Glass storage jar 1L (KP-3307)

Could you confirm the delivery date, and let us know if anything is
out of stock or has changed price?

Thanks,
[your name]

That is the version after one fix. The first draft added "as per your autumn promotion" to the opening line, because the first version of the instructions also referenced a Supplier notes field where the owner had once written about a seasonal offer. The agent did what it was told and used everything it was given. The fix was to reference only the fields the email should draw on and to ban any mention of prices or promotions outright. Read the first ten drafts in full; after that, read the quantity lines every time and skim the rest.

The credits are small. Six suppliers ordering weekly is at most about 26 reorders a month. If the Preview shows, say, 5 credits per draft, that is about 130 credits against the 30,000 a month two Team seats bring (15,000 each, pooled across the workspace). The real cost of the AI here is the habit of checking it.

How do you approve and send reorder emails safely?

A reorder email reaches a supplier only after a person ticks Approved, and a second automation then sends it. Airtable's guidance for one-time actions is a dedicated ready checkbox, and this is a one-time action if anything is: an order email sent twice is a double delivery.

  1. Create a view in Reorders called To approve: Approved unticked, sorted by Created.
  2. The owner reads each draft, edits it in the cell if needed, and ticks Approved. Editing the cell is safe: Airtable never overwrites a cell a person has edited.
  3. A second automation uses When record matches conditions with the single condition Approved is ticked, then Send email to the supplier's Order email, with the Draft note as the body.
  4. Add an Update record action that stamps today's date in Sent on, so the To approve view and your records show what went out when.

Know what the Send email action does before a supplier sees it. Messages come from Airtable's own no-reply address; you can set a From name and a Reply-to address, and recipients see "[shop name] (via Airtable)". Set Reply-to to your shop address so the supplier's confirmation reaches you. On Team the action can email 100 unique non-collaborator addresses a day, plenty for six suppliers. On Free it can only email verified collaborators in the base, so it can't reach a supplier at all. If you would rather orders come from your own mailbox, Airtable's Gmail and Outlook send actions do that.

How do you dry-run the reorder chain before the first real Monday?

Dry-run the reorder chain with a test supplier that has your own email address, which takes about twenty minutes and catches most set-up mistakes:

  1. Add a supplier called Test supplier with your own email address as its Order email and your name as Contact first name.
  2. Link one real product to it temporarily, or create a dummy product with a reorder point of 5, a par level of 10, a case size of 1 and an opening Count correction of 4.
  3. Check the product shows Low stock 1, Suggested order 6, and that Test supplier's Items low reads 1 and its Reorder lines shows the line.
  4. Test the scheduled automation's steps in the automation editor. One Reorders record should appear for Test supplier, and shortly afterwards the Draft note should fill in.
  5. Tick Approved and check your inbox: the From name, the "via Airtable" label, that Reply-to is your shop address, and that the quantity matches 6 exactly.
  6. Delete the test records, or relink the product to its real supplier, before the first real Monday.

If the draft never appears, Run automatically is probably off on the agent. If the email never arrives, look at the second automation's run history. One trap if you test on the Free plan: the test can succeed, because you are a verified collaborator in the base, while every real supplier email fails, because Free only emails collaborators.

What does an Airtable inventory tracker cost a two-person shop?

For two editors, the example shop pays $40 a month for Airtable Team billed annually ($48 monthly), and AI credits, automation runs and records stay inside the plan's limits.

For the illustrative kitchenware shop, with the owner and the part-time manager as the only editors:

ItemMonthlyNotes
Airtable Team, 2 seats$40 billed annually ($48 monthly)$20 a seat annually, $24 monthly; staff who only view can be read-only
AI credits$0 extraAbout 130 of 30,000 pooled credits a month
Automation runs$0 extraAbout 4 or 5 scheduled runs plus about 26 approval runs, against Team's 25,000
Records$0 extraAbout 11,000 a year against 50,000 per base
Owner's timeabout 1 hour a week10 minutes importing sales, 15 approving drafts, the rest on deliveries and counts

Before the tracker, the owner's routine was a Sunday-evening walk round the shelves with a notepad, then six emails written from memory on Monday, with the jars forgotten about every few weeks because they live in the stockroom. After it, Monday starts with up to six drafts waiting in To approve, each listing exactly what is low with case-rounded quantities. The time saved is roughly an hour a week; the bigger gain is that the stockroom lines get ordered too.

Setup takes about a day spread over a week: an afternoon for the tables and formulas, a stocktake for opening counts, an hour for the two automations and the agent, and a few Mondays of checking everything by hand. If you're still unsure whether this belongs in Airtable at all, when a spreadsheet is enough instead of Airtable is the honest comparison.

If you want help fitting this into how your shop actually runs, I offer one-to-one AI consultations for small businesses.

Stock-outs the tracker caused, and the checks that catch them

A correction entered with the wrong sign

A stocktake finds four fewer glass jars than the base says. Someone enters a Count correction of 4 instead of -4, so Stock on hand is now eight too high, the jars never reach their reorder point, and the shelf runs empty two weeks later. The owner found out when a customer asked for the jars and the base said 11 in stock. Two checks catch it. Add a view of Count correction rows larger than 10 either way, reviewed after each count, and read each correction against the paper count before you save it. Persistent gaps between counts and the base have their own causes, covered in why your stock count and system disagree.

A product with no supplier

A new line added in a hurry can miss the Supplier link. It goes low, flags correctly, and appears in no supplier's Reorder lines, so nothing is ever ordered. Keep a view in Products filtered to Low stock is 1 and Supplier is empty, and look at it every Monday before approving drafts.

Reorder points nobody revisited

Reorder points set in spring are wrong by December. A product that sold 4 a week now sells 10, and a reorder point of 6 gives you less than a week's cover on a one-week lead time. Put a quarterly review in the diary: sort Products by units sold in the last eight weeks, recalculate the busiest 30, and leave the slow ones alone. For seasonal patterns beyond simple averages, forecasting stock and reorders with AI goes further.

A draft approved without reading the quantities

By week six the drafts all look right, and the owner starts ticking Approved from the list view. Then a sales import goes in twice, every Signed quantity for that week doubles, and the drafts dutifully ask for twice the usual stock. The agent copied the lines exactly, as instructed; the error was upstream. The habit that catches it is reading the quantity lines against what you'd expect, every time, and checking that each Monday has one sales import, not two.

Questions about running stock in Airtable

How do I build an inventory tracker in Airtable?

Build four linked tables: Products, Suppliers, Stock movements and Reorders. Make stock on hand a rollup of movements, add a formula that flags products at or below their reorder point, and run a weekly scheduled automation that creates one Reorders record per supplier. A field agent then drafts the order email for you to approve before it is sent.

Can Airtable pull sales straight from my till?

Usually not directly. Most tills export sales by item as a CSV, which you import into the Stock movements table weekly. If your till connects to Zapier or Make, a scheduled flow can create the weekly rows for you, but check how many records and tasks that uses before switching it on, because a row per sale fills a base quickly.

Is the free Airtable plan enough for this tracker?

Not for long. Free allows 1,000 records per base, which a shop logging weekly sales fills within weeks, and Airtable lists field agents on paid plans. On Free, Airtable's own Send email action only reaches verified collaborators in the base, so supplier emails would need the Gmail or Outlook action instead. Team, at $20 a seat a month billed annually, deals with all three.

Should the AI decide how much to order?

No. Keep the quantity in a formula you can read and check, based on par levels and case sizes you set. Let the AI write the message around those numbers. A language model asked to choose quantities will produce plausible figures with no stock logic behind them, and you would have to check every one anyway.

Further reads

Show 3 more related tutorials

Sources: Airtable help pages on using Airtable AI in fields, Airtable AI billing, the formula field functions reference, rollup fields, the barcode field, the When record matches conditions trigger, the Find records action and the Send email action; Airtable pricing page. All checked 17 Nov 2026.

Have a specific process in mind?

Describe the job in your own words on a 1:1 call and I'll show you what AI you can use for it with the tools you already pay for.

Book a 1:1 call with me