This video describes a system for handling inventory at multiple locations. What’s the unique problem you’re having with your inventory system?
Get the template:
Watch the video:
Hey!
I’m new to airtable, and I’m creating an inventory system that will be used for a small business me and my sister have started. We are expanding and doing things manually is not sustainable in the long run.
Our business looks like this, We order from manufacturer they send it to our main warehouse and then we work on consignment with some stores, and soon work with some big wholesale deals. we also sell on Shopify.
I want to make sure I can track the inventory moving around. I have plans to use Microsoft Power apps AI to take the PDF’s that are sent to our emails from stores of monthly updated sales and connect that to our sales channels. this way we can log all store sales with ought having to count and subtract manually.
Then I want to some how not sure yet, create a log of the new inventory restock to the stores. This way they have like an invoice or manifest of all the inventory that is being restock or extra that is being sent. I think ill have the AI read the email pdf that we send and once the email is send have the AI read it and adjust the inventory from our warehouse to the stores stock.
MAIN QUESTION:
The AI makes sense and I can imagine how that works as I have seen other videos. but not sure how to log it into air table. I have the products, received and sold created but not sure how to log movement of inventory from warehouse to store or store back to warehouse. also do I need separate tabs for received from factory or received from other locations.
I’m not the best at expressing myself by writing it down. I hope this makes sense. I would die for a phone call and talk it out with someone who knows what they are doing
The more I think about this and copied your multiple store inventory system, it works well but the issue is movement of inventory. Also I want to create a place to record gifted Items and wastages such as broken or lost goods
Also We are currently at 8 stores but planning on having 5 more in the next few months. then in the future 10 more. with this be sustainable?
Hi John, I think you’re right that this is more of a phone/Zoom call since you have a few different open-ended questions to answer. You can schedule a call with me here!
@johnnydavidw and I connected over the Zoom and discussed how adding individual Rollup fields for each new location is a lot of work. Is there a better way? One solution we discussed was combining the Sold and Received tables into one, which would allow some more options for building charts of in-stock product, by location, in an Interface page. But I think this will be a common question for folks who have watched the YouTube video, so I want to offer perhaps a more robust solution that keeps the Sold and Received tables the way they are, but makes the summaries of In-stock items by Product AND Location more dynamic.
To achieve this, you’ll create a Junction Table called “Product by Location” that links to the Location table and to the Product table. Then, in the Received and Sold tables, remove the links to Product and Location. Instead, create one link to “Product by Location”.
This way, the user filling out the form makes one selection of Product and Location combined, and the rollups are created in that “Product by Location” table. This makes it easy to add new locations and get summaries for those - just add a location in the “Location” table, then makes sure to add the Product x Location combos in the “Product by Location” table. If you still want a summary of product inventory regardless of location, you can do that by rolling up the “Product by Location” summaries from inside the Products table. Here is a template for what I described.
Hi John
I have a similar challenge but slightly different, we have Medications and each viel has an Expiration date, so we can still relate to the multiple locations but in reverse.
our challenge is a bit different as we have it as ‘Green T-Shirts’ in 2 in Brooklyn a 1 in Queens (2 Dopamines Expiring 7-1-2024 and 3 Expiring 9-1-2024)
Hi @sne012 , Welcome to the forum! So to track the same products but with different expiration dates, this can be treated almost identically to the above example with multiple locations. I made an example base where I just changed “Location” table to be the “Expiration Date” table. Now you can see that when you receive or sell an item from inventory, you pick a product AND an expiration date combo. Product x Expiration table shows the inventory of each product by expiration date. But there is still a Products table that will show you the total inventory regardless of expiration, if you want that too.