Flag low stock in NocoDB and draft supplier purchase orders

Every weekday at 8am, review my NocoDB inventory table and flag every item where stock on hand has fallen below its reorder point.

Use the NocoDB List Table Records operation to pull the inventory table, paging through every row rather than stopping at the first page. For each row, compare stock on hand against that item's own reorder point. Skip any item that is already marked as on order, so the same shortage is not chased twice.

Group the flagged items by supplier, and rank them by how urgent the shortfall is. Judge urgency by how far below the reorder point the item has fallen as a proportion of that reorder point, not by raw units, so a small item that is nearly out ranks above a large item that is only slightly short. If the table records a supplier lead time, let a long lead time push an item further up the ranking.

For each supplier that has at least one flagged item, use the Gmail Create a Draft operation to write a draft purchase order addressed to that supplier's contact email. Give it a subject line that identifies it as a purchase order with the supplier name and today's date. In the body, list each item with its identifier or SKU, current stock on hand, reorder point, and a suggested order quantity that brings it comfortably back above the reorder point. Create these as drafts only and never send them. Purchase orders commit money, so I review and send each one myself.

Then post one consolidated message to my operations channel using the Slack Send a Message operation. Include how many items were flagged, how many suppliers are affected, the handful of most urgent items by name, and a note that the drafts are waiting in Gmail for review. Send a single summary message, not one message per supplier. If an item was flagged but has no supplier or no contact email, call it out separately so it does not get lost.

Finally, use the NocoDB Update Table Records operation to set a "Reorder flagged" status and today's date on each flagged row. Only update rows that were actually flagged in this run. If a supplier's Gmail draft could not be created, say so in the Slack message and leave those rows unflagged, so the next morning's run picks them up again.

If nothing is below its reorder point, create no drafts and post a single short all clear line to the operations channel so I know the check ran.