automations.help
Book a teardown

Inventory & suppliers

Auto-generate purchase orders at the reorder point

Keep a reorder point and a supplier for every SKU in Airtable, read Shopify's live inventory on a schedule, and draft the purchase order automatically the moment stock drops to the line — then approve it before it sends. You reorder on lead time instead of finding out you're empty from a customer.

Difficulty
Intermediate
Time to build
4-7 hrs
Updated
  • Shopify
  • Airtable
  • n8n

The manual way today

Once a week someone opens Shopify's inventory report, eyeballs which SKUs look low, cross-checks a separate spreadsheet for who supplies each one and how much to order, then types up a purchase order per supplier and emails it off — if they remember to, and if the spreadsheet's lead times are still current.

The check only happens when someone has time for it, so a fast-selling SKU can cross zero between two reviews. You find out from a 'sold out' product page or a customer email, then eat the full lead-time wait with nothing on the shelf. The lost sales never show up in any report — the orders simply never got placed.

What running looks like

Every morning the system reads live Shopify inventory, subtracts what's already on order, and compares each SKU to its reorder point. Anything at or below the line gets rolled into a draft purchase order per supplier — quantities rounded to MOQ and case packs — and dropped in front of a person to approve. Approve it and it emails the supplier and marks the PO placed. You reorder on lead time, not on panic.

01The build

How to build it

  1. Make Airtable the brain for reorder data

    Needs a human

    One row per SKU with: SKU (your join key), product name, supplier, reorder point, reorder-up-to quantity, supplier lead time in days, MOQ, case/pack size, unit cost, and the supplier's order email. Add a linked Suppliers table and a Purchase Orders table. These numbers are judgment calls a person owns and revisits, so keep them out of code and in a table the merchandising owner can edit directly.

  2. Read live Shopify inventory on a morning schedule

    Runs itself

    In n8n, a Schedule node fires once each morning. Pull available units from Shopify's Admin GraphQL API (inventoryLevels at your fulfillment location), read the SKU rows from Airtable, and join the two on SKU — never on product title, which gets edited and isn't unique. Now every SKU has its on-hand count sitting next to its reorder point.

  3. Add back what's already on the way

    Runs itself

    Before comparing anything, sum the quantities on purchase orders in Airtable whose status is Placed but not yet Received, per SKU, and treat (on-hand + in-transit) as the real position. Skip this and a daily run will re-order the same SKU every morning until the truck finally shows up.

  4. Flag SKUs at the line and size the order

    Runs itself

    Filter to SKUs where (on-hand + in-transit) is at or below the reorder point. For each, compute the quantity needed to top back up to the reorder-up-to level, then round it up to the supplier's MOQ and to whole case/pack multiples — a request for 47 units when the case is 12 isn't an order anyone can place, so it becomes 48. Drop SKUs that round to zero.

  5. Group the flagged SKUs into one PO per supplier

    Runs itself

    Group by supplier so each supplier gets a single purchase order, not one per SKU. Create a record in the Purchase Orders table with status Draft: a generated PO number, the line items with quantities and unit costs, and a computed total. Link each line back to its SKU row so the audit trail stays intact.

  6. Put the draft in front of a person to approve

    Needs a human

    Surface each draft before anything leaves the building. An Airtable interface (or a daily summary email from n8n) shows the PO — supplier, line items, quantities, total — with an Approve action. The person sanity-checks quantities against what a report can't know: a promo next week, a supplier price change, a SKU they're about to discontinue. No PO emails a supplier on its own.

  7. Send to the supplier and mark the PO placed

    Runs itself

    When the PO is approved (status flipped to Approved, or the interface button clicked), n8n picks it up, renders it as a formatted email or PDF, and sends it to the supplier's order email. It then sets the PO status to Placed with the date — which is exactly what the in-transit step reads to avoid double-ordering on tomorrow's run.

  8. Guard against duplicates and velocity spikes

    Runs itself

    Two guardrails keep it honest. First, suppress a SKU from new drafts while it already has an open (Placed, not Received) PO covering it, so a slow delivery never spawns a second order. Second, derive each reorder point from recent velocity — average daily units over the trailing 30 days × lead time, plus a safety buffer — and flag any SKU selling far above its usual rate for a human to reorder early, before the static number catches up.

02The stack

What it’s built on

Shopify
System of record for live inventory. Its Admin API reports available units per SKU at each location — the real-time number the reorder check runs against.
Airtable
The brain and the paper trail: reorder points, suppliers, lead times, MOQs, and the purchase-order records themselves — all editable by the person who owns the numbers.Airtable's free tier covers a few hundred SKUs; once you pass its per-base row cap or want the interface and automation features, you'll want a paid plan. A Google Sheet runs the same logic too if you'd rather not add a tool.
n8n
The glue: the morning schedule, the Shopify pull, the Airtable join, the reorder math, the supplier grouping, and the send on approval.n8n is open-source and self-hostable for free; their cloud tier just saves you running it yourself. Zapier or Make can run this too — see the FAQ on cost at volume.

03Questions

Before you build

How do I set the reorder point for each SKU?

Start from the math: average daily units sold × your supplier's lead time in days, plus a safety buffer for a bad week. A SKU selling 10/day on a 14-day lead time needs roughly 140 units of cover before a reorder even arrives, so set the point above that. Static numbers drift as sales change, which is why the build can recompute them from your trailing Shopify sales instead of trusting a figure you set six months ago.

Won't a daily run order the same thing twice?

No, as long as it counts what's already on the way. Before comparing a SKU to its reorder point, the flow adds in the quantities on POs that are placed but not yet received, and it suppresses any SKU that already has an open PO. A slow delivery can't spawn a duplicate order.

Does it email my supplier automatically?

Not without a person. The automation drafts the PO and computes the quantities; a human approves it before n8n sends anything. Reorder quantities are exactly where local knowledge — a promo, a discontinuation, a price change — should override the math, so approval is a deliberate gate, not a rubber stamp.

Do I need Airtable, or can I keep this in a Google Sheet?

A Google Sheet runs the same logic and is the right call if you're avoiding another tool. Airtable earns its place once you want linked supplier records, a clean per-SKU view, MOQ and pack fields, and a purchase-order table with a status you can filter — the in-transit math leans on that status, and it's fiddly to keep straight in a flat sheet.

What about minimum order quantities and case packs?

They're handled before the PO is drafted. The order quantity gets rounded up to the supplier's MOQ and to whole case or pack multiples, so you never send an order a supplier will bounce — a request for 47 units when they ship in cases of 12 becomes 48.

Can I build this myself, or should you build it?

If you're comfortable with APIs and have a few hours, build it — every field, trigger, and edge case is written out above on purpose. If you'd rather not own the reorder math and the supplier-grouping logic, we'll build it on the Shopify, Airtable, and n8n accounts you already pay for, document it, and hand you the keys. You own the workflow outright — no monthly retainer, no platform you're locked into. It starts with a free 20-minute teardown where we map your reorder points and busywork, not a contract.