Daily purchasing desk for Airtable and Shopify stock

Build me a purchasing desk that my ops buyer opens every morning. Airtable is the system of record for suppliers, products, reorder points and purchase orders. Shopify supplies live stock and unit cost. Gmail sends the order to the supplier. There is no schedule and no trigger: the buyer opens the app, reviews what is short, and issues orders.

Assume the Airtable base has a Products table (SKU, supplier link, reorder point, lead time in days, minimum order quantity, case pack, weekly usage, discontinued checkbox, and the Shopify inventory item id for that SKU), a Suppliers table (name, contact email, optional order notes), a Purchase Orders table (supplier link, status, order date, expected arrival date, total cost), and a PO Line Items table (link to the purchase order, SKU, quantity ordered, unit cost, line total). If a field is missing, degrade gracefully rather than failing the whole view.

The main view is a reorder list. Drive it from Airtable List Records on the Products table, which is the roster of everything we buy, and filter out any row flagged discontinued before anything else happens. Collect the Shopify inventory item ids from the surviving rows and read live stock with Shopify List Inventory Levels, passing the ids in batches rather than one call per SKU, and use Shopify Get Inventory Item for SKU and unit cost. Read the supplier name and contact email through the linked Suppliers records. Keep the Airtable read volume modest because Airtable allows only about five requests per second per base.

Show a line for every SKU whose on-hand quantity is at or below its reorder point. Group the lines by supplier, and within each supplier sort by days of cover ascending so the most urgent item is at the top. Days of cover is on-hand quantity divided by average daily usage, where average daily usage is weekly usage divided by seven. Each line shows SKU, product name, on hand, reorder point, days of cover, lead time, unit cost, and an editable suggested quantity. Highlight any line where days of cover is less than the supplier lead time, because that item will run out before a replacement can arrive.

Calculate the suggested quantity as enough stock to cover demand across the lead time plus a coverage buffer, minus what is already on hand. Then round it up to satisfy both the minimum order quantity and the case pack: never suggest less than the minimum order quantity, and always round up to a whole multiple of the case pack. Never suggest a negative or zero quantity on a line that is showing. The buyer can type over the suggested quantity on any line, and edits must persist while they move between suppliers in the same session.

Each supplier group has a Create purchase order button. It writes one Purchase Orders record in Airtable with Create Records, linked to that supplier record, with status set to Draft and the order date set to today, then writes one PO Line Items record per line with Create Records, each linked back to the purchase order. Airtable accepts at most ten records per create call, so chunk the line items into batches of ten. Create exactly one purchase order per supplier per run: if the buyer clicks twice, do not write a second order, open the one that already exists. Show the order total as the sum of quantity times unit cost.

Each created order has an Email supplier button. It sends the order through Gmail Send a Message to the contact email stored on the linked Airtable supplier row, with a readable subject naming the purchase order, and a body listing SKU, quantity and unit cost per line plus the order total and the delivery address. After the send succeeds, use Airtable Update Record to flip the purchase order status to Ordered and set the expected arrival date to today plus the supplier lead time. If the supplier row has no contact email, disable the button and say why rather than failing silently.

A second view called Open orders lists in-flight purchase orders read from Airtable List Records, filtered to statuses that are not yet closed, showing supplier, order date, expected arrival date, line count, total cost, and a flag for anything past its expected arrival. Opening an order shows its line items. Each line takes a received quantity, defaulting to the quantity ordered so a clean delivery is one click.

The Receive action closes the order with Airtable Update Record, setting status to Received and stamping the received date, and pushes stock back into Shopify. For each received line, read the current level with Shopify List Inventory Levels and write the new on-hand total with Shopify Set Inventory Level at the configured receiving location, using the inventory item id from the Airtable product row. Shopify Set Inventory Level takes an absolute quantity, so add the received count to what is currently on hand; alternatively use Shopify Adjust Inventory Level to apply the received count as a relative delta, which is safer if stock may have moved during the day. Let the receiving location be configured once in the app rather than chosen per order. Support partial receipts: if received quantities are below what was ordered, keep the order open and record what arrived.

Three rules matter throughout. Suggested quantities always round up to both minimum order quantity and case pack. One purchase order per supplier per run. Any SKU flagged discontinued in Airtable never appears anywhere on the reorder list, even when its stock is below the reorder point. Show clear empty states when nothing is below its reorder point and when there are no open orders, and surface a plain error on the line if a Shopify read or write fails rather than blanking the whole table.