Re: Auto update Inventory Based on Field input
Hello @SanemMac_Sevgen! Welcome in!
Inventory tracking is a great use case for Airtable.
However, it can be a bit tricky to get everything granularly configured exactly how you’d like it to be.
I have taken some base designs and modified it to try and create something similar to what you are describing.
The first table I have is my inventory table.
This table tracks every product I sell. It displays the total amount I have in my inventory and records the total amount I’ve sold.
This gives me my running count on my inventory.
The next table tracks my purchase orders.
This is where my inventory counts come from.
Every time I have a new purchase order of a product that arrives, the amount I’ve bought adds to the rollup count in the inventory table.
The next table is my actual sales orders.
Here you’ll see every sale I’ve made, along with the product ordered, and the quantity ordered.
With all of this combined, I can track the total quantity I have on hand at all times.
Purchase orders add to my inventory automatically, and sales orders subtract from my inventory.
Please let me know if you have any additional questions or concerns about this implementation!
If you’d like, I can toss together a simple example base for you to copy and play with.
Hello Ben,
Thank you for the prompt and detailed answer!
I also forgot to mention but, the heel count will drop depending on both heel type AND the size of the shoes. Since there are a couple of the same heel with different sizes.
For example HeelType 3456 for size 35. HeelType 3456 for size 41.
That would be a great help if you could share an example base. This will be a huge automation and step up for our family business if I could somehow implement this.
Here is a snapshot from our table. I will also do the same for the Molds but it will be the exactly same with the heels so if I could just understand it for the heels it will be perfect:
Apologies for the delay in the response here!
Here’s a base that I’ve built for you to use as a starting point.
Alternatively, you can just poke around and see how it’s configured.
Here’s how it works:
Products & Manufacturers
I created a Manufacturers table.
Each record corresponds to a given brand.
In addition, you’ll see a few simple fields that take the rest of the data in the base and give you a high-level look at your profits.
We’ll take a look at where that data comes from in a bit.
There are also placeholders for a few things like copies of invoices and contact info for dedicated ordering numbers.
Next is the Products table.
This table contains every unique design that you’re inventory holds.
We also have the price for each design, pictures of the designs, and a link to their respective manufacturer.
I’ll explain the shoe sizes in the Inventory table.
Inventory & Purchase Orders
So, I’ve built this base with a bit of a set inventory-to-sale pipeline.
Firstly, you place a purchase order with a manufacturer.
Those orders are tracked in the Purchase Orders table.
You’ll see linked fields to indicate the SKU that you ordered and the Manufacturer you ordered from.
In addition, there is an order quantity field called Units Ordered. There are also fields for things like shipping statuses and invoice payments.
The Inventory Table
The inventory table was an interesting challenge.
So… each SKU naturally represents a single, unique item. There can be multiple SKUs with the same shoe design, but each one will have a different shoe size.
For Example:
- SKU 19905 is a Slide Sandal by Veneta in a size 5 US/35 EU.
- SKU 00950 is a Slide Sandal by Veneta in a size 6 US /36 EU.
This allows you to track every single unique item you have in your inventory, regardless of the design.
The shoe sizing is done using a single select field since it seems fairly universal.
The price field is a lookup of the price field in the Products table. This prevents you from having to manually enter the price of every single SKU.
We’ll talk about the rest of the fields after we go through…
Sale Orders
This is where the magic happens…
You have records for each sale order.
Each order has the product ordered, as well as the quantity.
Additionally, you have other relevant tracking data and customer information, etc.
How Does It Come Together?
When your purchase orders are marked as Received (in the purchase orders table), it will meet the criteria for the rollup field in the Inventory table also called Received.
This means that your inventory count will not update until you mark it as received, and will accurately reflect your inventory’s supply counts per SKU.
Now, there’s also a rollup field called Sold.
This field is a rollup of the number of units ordered per customer sale record.
This works because each customer order is tied to a unique SKU. So we just take a running count of the Units field and we get an accurate count of what we’ve sold.
In order to calculate your total real-time inventory count per SKU, we just build it like this:
{Received} - {Sold} = Running Inventory Count
That formula works just fine.
You’ll notice that I formatted the formula to display a few dynamic messages depending on a few factors.
In practice, if you’re inventory count is 0, then the field will display a “ :x: Out of Stock!” message.
If (Amount Sold) > (Inventory), then the “ :warning: Active Backorders!” message will display.
Here’s the formula for the Inventory Status field:
IF(
{Received} > 0,
"Inventory:" & "\n" & {Received} & " Units",
IF(
{Received} - {Sold} = 0,
"❌ Out of Stock!",
IF(
AND(
{Received} = 0,
{Sold} > 0
),
"⚠ Active Backorders!"
)
)
)
Handling Backorders
Now, backorders were something I didn’t think about in my original design.
Simply put, if a sale is made that exceeds the total inventory, then the Inventory record will return the backorder alert message I noted above.
On the Sale Orders table, there is an Order Inventory formula that displays a fulfillment message.
If your inventory is stable and ready to fulfill the order, it will display the Clear to Fulfill message.
If there are issues with your inventory’s ability to supply the order, a backorder status message will display.
Here’s the formula:
IF(
OR(
NOT(
{Inventory Status}
),
{Inventory Status} = "❌ Out of Stock!",
{Inventory Status} = "⚠ Active Backorders!"
),
"⚠ Backordered!",
"✅ Clear to Fulfill!"
)
My Formulas
Here are all the formulas I used in this base
Purchase Orders Table
The primary field is a formula that just summarizes the record fields for easier reference.
"#" & {Order} & "\n" &
{Manufacturer} & "\n" &
"SKU " & {SKU} & " x" & {Units Ordered} & "\n" &
IF(
AND(
{Shipment Status} = 'Received',
{Invoice Status} = 'Due'
),
"❌ Payment Due",
IF(
AND(
{Invoice Status} = 'Paid',
{Shipment Status} = 'Received'
),
"✅ Paid",
IF(
AND(
{Invoice Status} = 'Awaiting Shipping',
{Shipment Status} = 'Not Shipped'
),
"⏱ Processing",
IF(
{Shipment Status} = "In Transit",
"🚚 En Route"
)
)
)
)
Manufacturers Table
The only formula here is the Net +/- field.
There are actually two hidden fields that provide the data here.
Here are all of the fields:
The formula is just a simple: {Total Revenue} - {Lifetime Purchases}
There are other, more useful ways to summarize financial performance, but I decided to not get too far into it since I didn’t know what you’d find valuable.
Products Table
There are no formulas used in this table. Only rollups.
Inventory Table
There are three formulas in this table:
- Inventory Status
- Restock Status
- Total Available
Here are all of the fields:
Total Available
Just a simple {Received} - {Sold}
Inventory Status
IF(
{Received} > 0,
"Inventory:" & "\n" & {Received} & " Units",
IF(
{Received} - {Sold} = 0,
"❌ Out of Stock!",
IF(
AND(
{Received} = 0,
{Sold} > 0
),
"⚠ Active Backorders!"
)
)
)
Restock Status
IF(
{Units En Route} > {On Backorder},
"🚚 Restock In Progress"
)
Sale Orders Table
There are three formula fields in this table.
- The primary field (Name)
- Order Inventory
- Order Total
Primary Field
"#" & {Order ID} & "\n" &
"SKU: " & {Item} & "\n"
& "Qty: " & {Units} & "\n" &
{Customer}
Order Inventory
IF(
OR(
NOT(
{Inventory Status}
),
{Inventory Status} = "❌ Out of Stock!",
{Inventory Status} = "⚠ Active Backorders!"
),
"⚠ Backordered!",
"✅ Clear to Fulfill!"
)
Order Total
IF(
AND(
{Price per Unit},
{Units} > 0
),
{Units} * {Price per Unit}
)
Other Notes
There are probably a few things I forgot to touch on, but again, let me know if there’s something you are curious to tweak or are confused about.
Feel free to copy that base I created and shared.
The formulas I wrote are largely me practicing my formula construction, but if you’re curious about how something is written, let me know and I’ll break it down for you.
