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:

@SanemMac_Sevgen

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:

1. Inventory Status
2. Restock Status
3. 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.

1. The primary field (Name)
2. Order Inventory
3. 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.

Hi @Ben.Young
This is really interesting! Incredible work.

I’m trying to do something very similar but somewhat more simplified but I can’t quite get the base you made to meet my requirements. I want to be able to show a live inventory (like you have in your Inventory sheet) but only for 8-10 unique products (no sizes required) and for each product’s inventory to update when a product is required.

For context, I have 8 different hardware solutions that get installed in a physical location. I want to be able to track my purchase orders of those hardwares as well as any upcoming/completed installations and know if I have enough product in stock for that installation. Do you think this is something your base could handle?

Here’s what I’m currently working with: Sign up - Airtable

Hey @Deena_Metz2!
Happy to see that this post still helps people out a bit!
Gimme a bit to look at these requirements and notes you provided.

Seeing as this thread is from quite a bit ago, I’ll message you directly about how we can approach a solution to get you what you need.

Feel free to proactively message me in the meantime if you have any other additional details you might want to toss at me for when I wake up.

Hi, Ben! Your inventory management is the exactly what I was looking for! Thank you so much for sharing your base, it really helped me to start building my own inventory base.

I just stuck with one thing: my products usually have a different price for each purchase. Let's say I created a purchase of product: x2, $100 each and then repurchased the same product at $110. In my Orders Table I have the following fields: Item's Retail Price (fixed, from Products Table), Item's Purchase Price (not fixed, from Purchases Table), and a column with formula for calculating the profit. For example, I already sold first 2 Products, which were purchased at $100. But how do I indicate that after selling the my two pieces purchased at $100, I want to use the next purchased cost ($110) for this item.