Setting up inventory counts & purchase orders
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.
