Automated Inventory Management with Airtable PO Creation & Supplier Emails

In-depth description of this automation:

This is a fully automated daily supply chain and procurement workflow that keeps product stock levels healthy and suppliers updated, by automatically generating and emailing purchase orders (POs) and syncing PO statuses in Airtable.

Daily triggers:

Two schedule trigger nodes run. One runs at midnight (00:00) to manage low stock and create new purchase orders. Another runs at 1:00 AM to process existing pending purchase orders and email suppliers.

Step-by-step breakdown:

1. Get Products with low stock

Searches the “Products Table” in Airtable for items where stock_level is less than or equal to reorder_threshold. This detects products that need restocking.

2. Get supplier details

Fetches supplier data for each low-stock product using its supplier_id.

3. Calculate Dynamic Reorder Quantity

A JavaScript code block calculates an optimal reorder quantity using this formula:

average_daily_sales × (lead_time × 1.5) × safety_margin (1.2).

This adds a buffer so the new order covers both immediate and near-future demand.

4. Search existing purchase orders

Looks in the “Purchase Orders” table for active orders (status Pending or Sent) matching each product to prevent duplicate orders.

5. Remove duplicate product orders

JavaScript logic compares low-stock products with existing POs and filters out already-covered products, ensuring new orders are only created for uncovered items.

6. Create new purchase orders

For the final filtered list, new PO records are created in Airtable including product_name, product_id, reorder quantity, supplier info, and status as “Pending”.

Process existing pending purchase orders and email suppliers:

7. Get pending purchase orders

Searches Airtable for all POs with the status “Pending”.

8. Group products by supplier

JavaScript groups these POs by supplier_id and builds a product summary (total products, quantities) and an HTML email body with a styled table.

9. Send PO emails to suppliers

Uses Brevo (SendInBlue) to send supplier-specific emails with order details.

10. Update PO statuses to “Sent”

After email is sent, Airtable record IDs are updated to change the status of those POs from “Pending” to “Sent”.

Summary:

- Runs daily

- Dynamically calculates reorder quantity

- Avoids duplicate orders

- Creates and updates POs in Airtable

- Sends grouped PO summaries to suppliers by email

- Keeps entire procurement loop automated

Tables involved:

Products Table – stores stock, reorder thresholds, sales rate, supplier reference

Suppliers Table – stores contact info and metadata

Purchase Orders Table – stores PO records with status and quantities

This system ensures real-time stock management and automated suppli

er coordination for any business using Airtable and N8N.