I'm trying to get my inventory to calculate right

[Ncec_Uia]

Hello,
We are trying to get our inventory to calculate right. I’m new to airtable and can’t seem to figure out what I need to do to get it to work out for me.
I have several items that will be used by a couple different customers daily and we want to make sure we know in real time how much stock we have.
I have attached my page and highlighted the Items. This is just a few of the similar items we have.
thank you for your help

[Ben_Young1]

Hey @Ncec_Uia!
Welcome in!

I’m pretty tired, so my writing might be scattered, but here’s what I’ve cooked up.

This reminds me of a thread from earlier this year.
The use case was similar (with key differences) to yours.

Here’s the base that I created for that post. Feel free to take a peek if you want to see it at scale.

---

Let’s get a bit more in-depth on your specific use case.
I created a demo base that you can reference and copy to use as a bit of a template or want to dig into how it works a bit more.

---

A critical part of building a database is understanding where certain pieces of information should live.
I don’t fully understand what your use case is or what information you’re working with, so I’ve filled in the gaps for the sake of my example.

I started by creating four tables.

1. Events

It’s pretty vague, so I’m just going to imagine that these are literal events with inventory associated with them. This is the cause of the in-and-out of your inventory items.

2. Customers

You didn’t have a customer table, but I saw the lookup field in your screenshot.
For this example, I’m going to create a customer table, as it will allow us to get reliable reporting information (if desired).

3. Inventory

Each record in this table represents a unique type of item (SKU) that you keep in your inventory.

Since our inventory also stores information about how much we have in stock, our inventory count should live here.

We’ll talk about the Estimated Inventory field later.

4. Check-In/Out

This table contains records of every check-in and check-out activity that affects your inventory.
This is your single source of truth.

---

So How Does It All Work?

Inventory

Start with your inventory.
I will create a new inventory called Straight Needle for a box of 1000 units I just received.
So I set my Last Inventory field to 1000 since I know my starting inventory is 1000.

Events

Now, I’m going to create a new event record.
I’ll call this new event Intravenous Therapy.

Check In/Out

Now that we have our event and the inventory we need for the event, we can start checking out the inventory required.

I need to check out 190 straight needles for the event.
So I’ll create a new record and fill in the fields.

Now that we have our checkout record for the straight needles, if we go back to the Inventory table, you’ll see that the Estimated Inventory field now estimates that we should have 810 units available.

Furthermore, if we go back to the Events table, we can see how many activity records are associated with the event.

---

To get the estimated inventory counts, I’ve created two rollup fields.

By doing this, we can see how many total units between all the activity records have been checked out of the inventory in the Total Usage field.
The same is true for the Total Return table.

---

The End Product

Now that we have our starting/corrected inventory, we can use the net outflow & inflow to calculate how much we should theoretically have left.

To do this, we’ll use a simple formula to do the math.
Here’s the one I wrote real quick for this:

"Available: "
&
(
    ({Last Inventory} - {Total Usage} + {Total Return})
)

Here’s what it looks like:

After that, you can hide the rollup fields. This will make sure everything looks clean and slick.

---

Other Fun Things!

Remember the customer table I briefly mentioned?
When you get comfortable crossing strings and building more intricate references to information, you can begin to leverage some really dope capabilities.

A tiny example of this is that by having all my data cleanly linked, I can easily report which “customer” has used the most of my inventory.

A step further would be to set up a field that tells me which inventory item they check out the most, etc.

---

Again, I’m tired, and this is probably relatively dense.
So if I’ve completely missed something or you’re confused about something I wrote/built, please don’t hesitate to reply with anything you have!

The demo base I built is also great if you want to play with it.

[Ben_Young1]

Something I forgot to explain is the Last Inventory field in the Inventory table.

I tend to write like I do since I find that forum posts are historically valuable for future users. Hence why I like to explain things the way I do. I’m not trying to be patronizing, I swear.

Something that is also worth noting is that inventory management like this might not even apply to your use case, in which case, you can totally ignore this.

If you’re curious about more of the intricacies in building a solution for inventory management, then my thoughts are below.

There are two ways to accurately track your inventory stock.

Method 1:

Restaurants, stores, etc use this method.
You receive a shipment of five hundred apples.

Three days later, you check your sales report and see that you’ve sold two hundred apples.
On paper, you have three hundred apples.

However, when you check your actual inventory, you see that you actually have two hundred and fifty apples.

Somewhere in the chain, you’ve lost fifty apples.
This is called shrinkage, and it allows you to track how much of your product you’re losing to unknown sources. They can be things like shoplifting, food waste, etc.

While this method is actually extremely reliable, it is really hard to scale since you must count everything in your inventory.

To keep an inventory count you must either use the second method, or you must accept that as time passes, your estimates on your inventory experience a gradual deviation until you pass updated figures.

Method 2:

If you look at the original thread that I referenced this solution from, you’ll see that the inventory automatically adjusts for both outflow and inflow.

This method is straightforward, but it requires that you have a reliable way of accounting for what you add to your inventory.

In your use case, if you have five hundred syringes, but someone checks out two hundred, your inventory will drop to three hundred.
But when you get a new shipment of five hundred syringes, you’d record the inflow of the five hundred syringes.
This would bring your total inventory to eight hundred.

While this method is great for selling merchandise or more substantial inventory, you would still be faced with occasionally having to do an inventory reconciliation to make sure that your inventory reflects your estimates followed by updating your inventory to reflect the true count.

[Ncec_Uia]

Thank you.
This is what I’m looking for, but I can’t get the formulas on what you were using. I tried to figure them out where I was close, but it didn’t look the same.
Thanks
Misty

[Ben_Young1]

Hello!

Could you possibly provide any screenshots or more information about how your base is structured?
We won’t really be able to provide more specific guidance without more context as to what you’re working with.

[Ncec_Uia]

Thanks I figured it out after I emailed you.

[Ben_Young1]

Ooooo!

This is great context, and the shared base view makes this much easier to digest.
Gimme a bit to play with this.

I already have a solution in mind.

[Ncec_Uia]

Hello,

Were you able to come up with a solution? If not, No problem.

Thanks
Misty