Setting up inventory counts & purchase orders

[FullCircleFoods]

Hi all,

I run a small grocery store, and we’re gradually migrating our system of strung-together Excel spreadsheets over to Airtable.

We have already added tables for our 4,500 products, departments, categories, vendors, and brands – everything flows really nicely and I’m amazed at how flexible Airtable can be.

But now I’m hitting a roadblock trying to figure out how to structure data for two things:

• Inventory counts
• Purchase orders

First, let me show you our current spreadsheet for one of our vendors, Best Baa Dairy. We order sheep yogurt from them every couple weeks.

When we’re about to place the order, we do a quick inventory – looking at what is on the shelf and writing down the current stock in our Count column.

Then, we look at that Count column, plus the previous orders, to get a sense of the trend. If we ordered 12 containers of yogurt last time, and now there is only 1 on the shelf, I definitely want to order more than 12.

As you can see, it’s important to view past orders and inventory trends to help me decide what number I should put in the Order column.

As time goes on, we repeat columns S, T, U to the right – and continue that forever.

At first glance, it looks like count and order should be a property of the Product record. But then I’d have hundreds of field names, like May1-count, May1-order, May29-count, May29-order… and there’s no way to automatically group those fields by date.

Another option, is to have an Orders table, and each record will have a field for each possible product. But as I mentioned there are 4,500 products and that list will keep growing.

Thank you for reading this far, I hope that gives you a good idea of my problem. I wonder if anyone has come up with a creative solution to a similar situation.

[Andre_Zijlstra]

Hi,

I guess it is mandatory to fully understand how the trend is determined. Amount and Time are important of course. These 2 components are fuel for a formula that can be used in the ordering process.

In Excel the columns are added to the right. In Airtable those adds are records going down and from these records data is drawn. A lot is possible, but before you do that I would say that defining the trend is mandatory.

Regards,
André

[FullCircleFoods]

Defining the trend is an art rather than a science – but let’s say the formula for the current order should be: {previous count} + {previous order} - {current inventory}. So in the screenshot above the order for May 29th should be 11. (I ordered 16 though)

Anyway, I think this is straying from the original question.

Are you suggesting I have a Orders table, with each column being a product? That would require adding 4500 columns manually.

And when I add a new product I would also need to manually add that column to the Orders table.

So how can the Yogurt plain product get linked to its column in the Orders table?

[Ronald_Javet]

Hi there, i made a base for a grocery store here in chile that i think may inspire you to solve the problem. We dont use it for inventory, we use woocommerce for that, but we do register orders and providers on a daily basis in a way that may help you. Here`s the link to check it out: https://airtable.com/shrOGo4JXK6rQ5tMb
i think im going to expire the link in a week just in case. Let me know if it helps or if you have doubts :winking_face:

good luck.

rj

[W_Vann_Hall]

Edit: I started this response shortly after you posted your request, but evidently never completed it. It bubbled back up to the top a few moments ago…

---

I think you’re too locked into the spreadsheet mentality, which is going to be increasingly frustrating. For one thing

Full_Circle_Foods:

As time goes on, we repeat columns S, T, U to the right – and continue that forever.

you won’t be able to continue that forever, as Airtable supports a maximum number of columns far less than Excel’s limit of 16,384. But that’s OK: It doesn’t need to, as its status as a database allows it to expand in ways unavailable to a two-dimensional spreadsheet.

The quick answer to your question is that where you currently have a set of columns you clone and modify every few weeks you will instead make use of Airtable’s linked-record functionality. You were heading in the right direction when you talked about making {Count} and {Order} part of the [Product] table; however, rather than create an explicit field for each inventory date, with each inventory you’ll dynamically create the necessary records, tag them with the appropriate date, and link them to, well, whatever table makes most sense.

Essentially, those three recurring fields from your spreadsheet — {Count}, {Order}, and {Cost} — somehow need to be encapsulated in, oh, an [Inventory] record type. Then, every couple of weeks, you’ll create a new linked record and timestamp it. You’ll enter counts and orders as you do now, the totals roll up to however you generate the actual order to the vendor — and in another two weeks, you’ll create a new linked record again.

I don’t know what data model is most appropriate, as I don’t know your business. Perhaps you’ll want to generate a biweekly linked record per vendor, with per-item linked records descending from it. Or maybe you’ll want to generate a datestamped linked record per item every two weeks. (If so, there areexamples out there demonstrating how to have Airtable generate linked records automatically, as well as ones showing how to configure Airtable so that it can write its own formula code.

I know it sounds daunting — but the system you intend to replace wasn’t knocked out one day after lunch. It represents a substantial investment of time and business intelligence. Re-implementing it in Airtable will not be a casual matter…

[FullCircleFoods]

Thanks, @W_Vann_Hall. Your explanation solved my mental block and helped me see things more in the “Airtable way”.

The key for me was understanding how Linked Records, Lookup fields, and Rollup fields can be combined to achieve what I wanted.

I created a table called Purchase Orders. Each record represents a single purchase order for a particular vendor. For example, the June 28th order we placed with Best Baa Dairy:

The Cost field adds up all the relevant line items in the PO Line Items table:

I will use the Airtable API to create more a more user-friendly frontend for our staff to enter the Count and Order (units) fields for each line item.

---

I’m excited to deepen the data connections across this base – for example, each record in the Special Orders table will be linked to its corresponding Purchase Order. Then when we receive the shipment, we can see which Special Orders we need to set aside, and which products can go on the shelf.

(Surprisingly, we had a difficult time finding a retail POS software that could accomodate this workflow. I’m thrilled it’s going to be possible with Airtable.)

[W_Vann_Hall]

Happy to have been of assistance. I still think the spreadsheet metaphor is a two-edged sword for Airtable: It’s a perfect elevator pitch for the product and no doubt makes it seem much friendlier to folks deciding on its use, but it makes it easy to become frustrated when Airtable turns out not to be a spreadsheet. I personally find a spreadsheet’s being spatially referential often to be to its detriment — who hasn’t had a sheet totally bollixed by a user performing a poorly considered sort or row insertion? — and in many ways Airtable’s freedom from such constraints is a significant advantage. The danger is one might abandon the product before having a chance to realize that.

FullCircleFoods:

I’m excited to deepen the data connections across this base – for example, each record in the Special Orders table will be linked to its corresponding Purchase Order . Then when we receive the shipment, we can see which Special Orders we need to set aside, and which products can go on the shelf.

Exactly!

[Ryan_Wychopen]

This is helping me with our inventory tracking so thank you. My question is: what do you do to receive items and what if you receive partial items? We typically put orders of 10-50 items but get anywhere from 2-5 different deliveries because part of the orders are stocked at a nearby vendor’s warehouse, whereas the rest is shipped from another location.

[FullCircleFoods]

We typically don’t receive partial orders. So our situations might be a little different. Perhaps you could add a Status column to the PO Line Items table, and then you’d have to mark each line item whether it’s been received or not.

[Gabriela_Moraes]

Hi there,

I am struggling with my Airtable Inventory base as well.
Can you share your with me to see if it fits?

Tks

Gabi