The Right Way to Build an Inventory in Airtable
Vložit
- čas přidán 24. 07. 2024
- ⭐️ Get the Airtable template - www.3rings.co/resource/clothi...
Airtable is an excellent platform for retail inventory management - in fact, Tesla uses it to keep inventory of vehicles leaving the factory. At it's core, inventory is a very simple math equation. The current inventory is the amount of units you bought or created, minus the amount you sold. However, when you have a lot of different items in your inventory, managing with a spreadsheet get's complicated - did I enter that last sale?
Enter the Airtable database. Using linked records, you can easily set up a system to track what products come in and out of your inventory, and know what's in stock.
⭐️ Part of a business or enterprise team?
Access my services - www.3rings.co
⭐️ Product Comparisons
Airtable vs Excel - • Airtable vs Excel and ...
Airtable vs Notion - • Is Airtable better tha...
⭐️ Airtable Basics
Airtable for Beginners - • Learn Airtable in 8 mi...
Linked Records - • Understanding Airtable...
How Dates Work - • How Dates Work in Airt...
Sign up for Airtable with my affiliate link (thank you!) - airtable.com/invite/r/R3UWDio5
To Do List Template - www.3rings.co/resource/list-e...
Inventory Template - www.3rings.co/resource/clothi...
Expense Reimbursements Template - www.3rings.co/resource/expens...
0:00 - Intro
0:20 - The end result
0:47 - Products
2:38 - Received and Sold
7:56 - Formula for what's in stock
11:45 - Gallery with buttons
16:22 - Connect customers
18:39 - What to watch next - Věda a technologie
When you find a video with the perfect solution and then realize you have to now go back and watch every other video on the channel to catch up to the video you just found.... Great work.
This is amazing! Your videos are so professional and well done.
Thank you so much!! Your way of explaining everything is just amazing. Hope you can keep making videos like this 🤩
Thank you for sharing, it means a lot!
Hey Julian thank you so much for creating the video, straightforward and easy to grasp
Welcome!
Crystal clear! Awesome tutorial!
@allandiazjr.3368 Glad it was helpful!
Julian, your videos are so helpful and giving me so many ideas on how I can use Airtable for our business. It makes me so hopeful on how easy Airtable can make our work if we set it up properly. Thank you and keep doing this.
Thank you Anu, I'm so glad they're useful for you!
Excelent! I just found you and I will see your videos. It is so difficult to find someone that can explain or interact in this platform with Airtable. Thank you for you contribution!
Thank you Frederico, I’m glad you find the videos helpful!!
Hey Julian, thank you so much for this video it has helped me create an inventory management system for 8 of my warehouses! I just recently saw how to include all of them in one Airtable, so awesome! However, I do have a question about how to override certain data. Say one warehouse does inventory counts once a week on one specific product. However, by the next week they have not sold any of that material, but still count it and submit a new form. I am now showing double the amount of inventory for one product. Is there a way to override each input with new data week by week? I tried to watch the video about the RSVP, but I couldn't get it to work for me in that manner.
Thank you
I'm so happy to hear that! You would definitely need to handle things differently than in the RSVP video, because the inventory system is just summarizing items coming in and out. So in order to correct the total, you'd need to submit the corrected amounts (probably to a new table which can compare against the totals in Airtable) and then set up an automation that will add or subtract the correct amounts by creating new receiving/sold records. OR, maybe your "in stock" amounts are kept separate from sold and received records, in which case a system like the RSVPs would work. If you try one of these ideas, feel free to post about it here and ask questions: community.3rings.co/ Or, if you'd rather, you can hire me to set it up for you 🙂www.3rings.co/services
Hello Julian,
Many thanks for the video. I need something similiar, but with the food products. I use a lot of products that each have seperate date of expiration. What I want in the system is manage those products by their expiration dates, add and substract those that have been used. Do you maybe have a solution for that?
This is awesome. Thank you! How would you use this same structure, but factor in multiple warehouse locations? (And product moving between warehouses from time to time)
Hi Stacey! Here's a new video that shows how I'd handle multiple locations. czcams.com/video/MIfyxMfVFzU/video.html
To move inventory, I'd ship inventory from one store and receive it in the other one. If you want to be fancy, you could set up an automation that creates a shipped record in one store if you receive it from another. Happy to walk through that in more detail if you're interested
Thank you so much @@julian_post!!
Hi Julian, I really appreciated watching the video and your explanation. How would you limit the sold inventory form so that no one can request to buy more than what is in stock already, ex: someone fills out the form to buy 100 t-shirts when theres only 50 in stock right now?
What about using I? I want to receive my item with a barcode. When it's sold I want to scan the barcode and it auto-populates for me what was sold. And from the barcode standpoint I would just want to scan and enter the sold price.
I have to say your advice is so clear and right on! Thanks, Julian!
Question: After watching your other video about healthy Airtable principles, I'm curious about avoiding duplication.
Here you say that were duplicating the received table and then just change a field to have the sold table. Couldn't you also just use one transactional table with a select field for receive or sell? (in or out)
With each the two forms, you could drive the respective switch setting.
Great question! You're absolutely right. I typically set up inventories this way for people who will be entering received and sold records in the table view, because I (subjectively) think this makes it a little less likely that they'll accidentally add a received instead of sold, or forget to specify received vs. sold. From an architecture perspective, keeping both sold and received records in one table is simpler and achieves the same goal. If you do it this way, you could make use of prefilled and hidden form fields to use the same form, but have a "Received" link and a "Sold" link that automatically tags it with the correct designation.
How about the sold/orders have multiple items in them? Like green, yellow and blue tshirts? How are batch items handled in a single order? Pls help
Julian, this is AWESOME! Thanks! Question... what would be the easiest way to create more of a heirarchy of products. For example, if each product was comprised of individual components? (i.e. A widget that is the product, but the widget is comprised of multiple 'components')
I made a video for that! Here it is: czcams.com/video/u9bgpDuCTdo/video.html
@@julian_post Awesome, thank you!
Julian, thanks for this video. I have a unique situation, where I would like to record multiple items sold in 1 form, instead of having to fill out a form for each product sold under the same sales order. For example, I may have a sales order ID #1 , where underneath it I will sell Water, Snapple, and Diet Coke. I would like to add the quantities and prices of those items within the form, without having to fill out a form for each item. Can that be done in AT?
This is a very common problem, but unfortunately it's not possible with Airtable forms. However, you can do it quite well with Airtable interfaces. I'm planning a video on this soon...
Hello, can you provide the formula used for the url. I tried copying what you did & nothing. I'm not sure if maybe my spacing is off or what.
Great video Julian. Can you show us the formula to add the date received automatically onto the forms connected to gallery cards. I have tried a few variations and I am clearly getting something wrong
Hey Lloyd, sure! Just copy and paste your question into my community thread about this video. Much easier to share formulas and discuss - community.3rings.co/t/the-right-way-to-build-an-inventory-in-airtable-template-and-discussion/10
@@julian_post Fantastic! Thanks Julian. I will do that.
Thanks for this video. About the price per unit : doesn't have to be the same each time I receive a green t-shirt (for example it can cost 2$ in march and 2.5$ in april ?) ? thxx !!
Hi @FannySto-ky1cn ! I made a video about tracking price changes: czcams.com/video/AUrbR8K4KNs/video.html
What if you need the items sized?
How do you fix it when someone uses a form and enters the data incorrectly, is there a way to use the form to update the record they submitted so they can fix the error?
Yes there is! I made a video that shows how to do that: czcams.com/video/BkAY7OHEgdc/video.html
thanks for this!
will you be creating a scenario for backyard farm database? From seed to harvest, expenses and profit. If anyone here have any suggestions please let me know :D
My friend Taylor shared her Airtable crop planner and other farm planning info here: www.patreon.com/habitfarming?fan_landing=true I highly recommend, she's an Airtable + organization wizard!
@@julian_post I'll check it out! Thank you☺️
Hi Julian, thank you for this video, it's super clear, helps me a lot to create my Airtable database to organize my nursery (I grow fruit trees). One question: in your example how would you handle it if you had different sizes for your products, such as S, M, L, XL ? I guess you would have one line per unique product, like Green T-Shirt - Size S, Green T-Shirt Size M, etc. But then I'm wondering how you'd keep things organized in the gallery view, having a card per Color / Product / Size doesn't look optimal, it would be nice to have a small table in each card with the size and quantities. Hope I'm clear! Many thanks!
Hi Jeremy, very cool - I used to work in the farming world. You can achieve what you're describing by creating what I'd call a summary table. Make a copy of the Products table with all the same fields and records, and call it 'Product Summary'. In the original Products table, add a new line for every unique combination of size, color, type. You will still reference this table in your sold and received tables to add/remove inventory. Also in your original Products table, create a linked record field to the summary table, and then link each S,M,L etc to the correct record (which doesn't have a size) in the Product Summary table. Now in the summary table, you can create Rollup field types that summarize inventory of each product by size using the controls in the Rollup to sum only records that are size S etc. In your gallery view, you can toggle each of those rollups on to see S,M,L etc. If you run into any issues trying this out, post some screenshots here and I'll help you out :) community.3rings.co/t/the-right-way-to-build-an-inventory-in-airtable/10
@@julian_post Hi Julian many thanks for your super quick and very useful answer. I gave it a go on your template, and I've posted some screenshots on the 3rings forum to make sure I understood well. I'm not native english speaker, so sometimes I may do some misinterpretation!
@ replied in the forum!
Thanks for the tutorial. I am having a problem with one thing I am trying. I have the CRM in one base and the Products database in another base. I want to link every purchase they have done into the following fields in the CRM: all orders, last date order, lifetime revenue . But I am not getting there, I am so confused right now. Anyone a tip how I can do this? I basically use the setup from this video. Thanks in advance.
Sounds like you’re syncing a table from one base into the other? Follow the template link in the video description which will bring you to my community forum post about this video. Then reply in the thread with some screenshots of where you’re at and where you’re stuck - I’ll help you troubleshoot
Seems super straightforward, but I can't seem to get the products table to populate its associated records from received or sold. Those two fields appeared kind of out of nowhere so I can't tell where I'm going wrong there. Any help would be super appreciated!
Take a couple screenshots and post them in the forum thread here - I'll help you get it sorted out community.3rings.co/t/the-right-way-to-build-an-inventory-in-airtable/10
I am at a stuck point in the button portion of the video. I am typing the formula exactly the same, but it is not prefilling the form. Any solution here? To be up front I did not use all of the same wording to make my table as I am not building it out for clothing. However, I did use "Product ID" for my table as well. Everything else is the video that I have followed has work flawlessly!
hmm hard to say without more information... what's the button formula you wrote?
Form is not listed under Create on my version of Airtable. It is listed below but only for Form View. When I create a Form view, it lists it under Interfaces but not under the Data tab. Any suggestions? Also, why do I need 2 columns each of received records and sold records? Then I hide the first two columns I already created?
Yes you can hide the received and sold quantity fields, but they're needed to make the final calculation. Airtable has rolled out interface forms, which is what you've been (unintentionally) creating. You can still create the traditional-style form view. When you create your next form, just read the language really carefully and click the button to create a form view. Because it's new, they're trying to push you toward making it an interface form.
@@julian_post thank you very much, I'll look into this again. 👍 Love this step by step tutorial by the way! Great tool for us newbiginners!
16:24 Why into a separated base?
Hi, was wondering how would you solve a scenario, where I need to have multiple products on 1 record and their quantities differ, I would need to have multiple products with different quantities for a single invoice, so far I cannot find a solution to this problem,
thanks :D
Edit: I made a tutorial how to do this here: czcams.com/video/J9rm4QWg-Nc/video.html
I actually have one of my own inventories set up like this. I would set it up exactly as it is in this video, then create another table for "Orders" which is linked to "Sold." Then you can create multiple records in the "Sold" table with different products and amounts (same as before), and link each one to a specific "Order". The "Order" table can rollup the total cost of the order, the items, etc.
@@julian_post Thanks!
Hello @@julian_post, I think you should make a tutorial for this.
@@Dolceykogei Actually, I just did! Watch it here: czcams.com/video/J9rm4QWg-Nc/video.html
@julian post the part 2 video didnt solve it. Is there any other way to have the form first then pick products with fillable quantities and that will reflect in sold?
How do you prevent your inventory to go negative while shipping out items ?
I suggest setting up an automation that notifies you by email when an item is low in stock. That way you have a warning ahead of time to replenish inventory of that item!
@@julian_post the best would be to prevent the entry with an alert that mentioned the remaining quantity. Is that something possible?
@@trudelo21 there isn't a way to prevent or delete a new record (entry) in Airtable, but you could set up the automation to change the order quantity to zero, then send the alert that the item is out of stock
So regina george was ur customer? How lucky you are 😂
Regina George is my most popular customer lol