Inventory Management in Airtable (Barcode Field, Zapier Webhooks, Button Field)

Inventory

Aron: [00:00:00] all right. We're back. Seems like everything is working. It's been a little while. I've streamed, but really excited to welcome everyone back. If you're here, let me know in the chat and really excited to bring you the session on using or creating an inventory tracking system in irritable. Feel like it's been ages since I've streamed, but really excited to be here again.

[00:00:45]Here's our agenda for today as always going to be checking off our checklist. Yeah. Yeah, just starting with a quick base overview. So our goal today is really, I recently purchased one of these bad boys for fun, which is a scanner. There we go. I won't blind you, but it works properly.

[00:01:03] But, and our goal for today is understanding what we can do in your table around venture tracking and management. And mainly I just wanted to play around with the barcode field in a barcode. So our goal for Tam first, we're just gonna go over the base itself. And then we're going to dive into the barcode field.

[00:01:21] Why would you use a barcode field? A little bit of history of the barcode and then automate some steps. We have this inventory tracker and I'd love to be able to scan an item, create a purchase order for that item and then potentially an invoice and things like that. So we're going to be creating the basics around.

[00:01:40] So as it was a start with a quick base overview. So I'm here in Airtable. Let me close that. And we have our inventory tracking base. So this actually comes from the template gallery. So you can find it in inventory tracking. We don't really need more than this for our use case. And essentially we have all of our product inventory here which has a product ID.

[00:02:05] Some information and most importantly, a barcode. So I'll dive into the barcode in just a minute a manufacturer, but really, what's important in this use case is really understanding purchase orders and sales orders. So you always want your purchase orders to be greater than your sales orders.

[00:02:25] Hey, Rohatyn. Good to see you. Thanks for joining. And so essentially for each product in your invention you create purchase orders and sales orders. So we roll up the number of units ordered from the purchase orders does give us our total inventory and then we have the units sold. So we create sales orders for each time we sell that unit.

[00:02:53] And then we roll that up into units. So essentially you're inventory on hand is the difference between units ordered and units sold. And so we want to do today is imagine that this is our kind of POS system and that someone coming in, we can just scan the item and then create a purchase order with one click for that item.

[00:03:16] So imagine we're selling at a food market or something like that. Something really simple that can serve as a basis. For creating a point of sale system in table. But as I said, frankly, this was more of a reason for me to play around with the barcode field and my new cheap barcode scanner.

[00:03:37] Okay. So that hopefully is a base overview. Let me know in the chat, if that's clear, happy to dive into any of the tables a little more and have a little more in-depth overview of any of the tables. Okay, so let's talk about the box. So I have here some, a barcode, and essentially what a barcode is under each barcode.

[00:04:00] You have these numbers. So barcodes are actually just numbers that are easily readable by a scanner. And what this scanner does is actually just read that barcode, transfer it into numbers, and then acts as an input as a keyboard. For whatever system it's tied to. So barcodes originated, I think in the fifties or forties where someone wanted to track prices for all of their different types of products that we're selling.

[00:04:32] And the first thing that was ever scanned was a Wrigley gum and really it's around, being able to price items efficiently and quickly. So essentially when you think about it, this is a key that lets you find. Whatever item is being scanned in your database. So air table being a database and irritable, having a barcode field, lets you create this kind of a really simple point of sale system.

[00:04:59] Hey Joe, I imagine that's Joe from Finn. Sweet. Good to see you. And so essentially when you think about. If your bar code, if this is just the keyboard in that little barcode is just your key. Essentially scanning it should search in your database. So essentially, when you see a barcode, any database is actually just a bunch of numbers and that is because the something translates.

[00:05:25]The scanner translates this into numbers and lets you see. So we're going to do two things today with this barcode. So first where we're going to introduce it, I've talked about happy to answer any questions around the barcode field. Then we're going to search and add new products.

[00:05:46]Initially when I started looking into. Barcodes. I assumed that each barcode was unique to each product. So if I had 10 of these, I would have 10 different barcodes, but that's not the case. So these are usually at the item or product level. So every time you have this case of sweets it'll have the same barcode and how you can know this is that if you search for a book and you scan the book, usually you'll be able to Google that book and find the exact.

[00:06:15]Barcodes are at the item level. So they're at the inventory level. So if I want to search for a product, let's say our goal is I'm selling products and I want to scan a product and find the item in my database so I can quote a price, right? So our prices are in the product inventory table. The price is right there.

[00:06:36] So what I should be able to do is scan and find the right item. Now, remember your barcode scanner is a keyboard. So we're going to do control F to find this barcode. And now I'm actually going to go ahead and scan this, this barcode, I got to hit it because it's a very cheap scanner. So

[00:06:58] if it works properly, give me a second.

[00:07:08] That's what I get for getting a cheap scanner.

[00:07:15] There we go. And you notice two things happen there. It might be a little small here. That the number was inputted, right? So that when the scan was successful, it inputted to where my cursor was. So I had clicked control F it finds that item and because I was searching, I can pull up that.

[00:07:34]So this is a way let's say you're scanning items. You can pull up the right record and then potentially create an invoice or create a new sales order based off of that scan. And we'll do that in just a second. So essentially what I've done now is I've found a way to go from barcode to my database.

[00:07:55] So as long as I'm control F I scan the. It comes into my database and I find the record that it's relevant to. So that was step one. Now let's go ahead and add a new product in here. So I'm just going to copy paste everything about the previous record like that. And I'm not sure Rohatyn if you're talking about. My scanner, it frankly never works. This was the cheapest scanner I could find. And so let's give this a different product ID like that. So we're just adding in a new item. Let's say this one's a hundred dollars. And essentially all you have to do is add in a new barcode.

[00:08:42] So just like before now I was searching items. Let's go ahead and. Add in, let's say we have a new product in our inventory. Let's go ahead and open that up. And then we had to, add in the barcode. So remember that barcodes that are usually at the product level. And so I'm going to go ahead and have, I don't know if I can show you this.

[00:09:03] I have to hit it. Yeah.

[00:09:20] Should've taken a different item. Give me a sec here.

[00:09:26] There we go. I apologize for that. And so now I've added the barcode for this new item. So now this is added in my database and I could search that item whenever someone comes in. So always remember have a better scanner than I do. And hopefully you don't have to hit it a few times before. We've added a new item, added a barcode and we're able to add this item to our inventory.

[00:09:48] So those that was a quick introduction into the barcode field. So searching for new products and adding new products. So remember searching, you just do control F you scan the barcode, it'll find the item, and then adding new products, you add a record and you simply open up that record and scan that barcode to be able to add it to your address.

[00:10:09] Now the fun part is not heading in mentoring. It's actually, okay. Let's say someone comes to me and says, okay, I want to buy this item. How do I create a sales order automatically based on that scan or at least much easier than saying, okay how we do this today is we go into a sales order and we say, okay, what was that?

[00:10:31] We just scan, we scan this. So I have to add this sales order. I added in here. I had the date, I link it to the right product. That's a pain. So what we want to do is because we found that item by scanning. We want to create a way to just quickly click and add a sale. So we're actually going to be using a few fields in a few cool new fields.

[00:11:03] And we're going to use Zapier to create this automation, right? So our goal is that when I scan an item, I can quickly add a button that says purchased this item sales order. And that's going to create that whole process that I've just shown quickly and efficiently. So let me know in the chat, if that's interesting to you, I don't know.

[00:11:23] I've gotten so much fun of showing. Field. So this was enough fun for me, but really excited about the second part. Cool. So let me hide some fields here. There's too much information. So we don't care about the type colors style location. We do care about barcode in venture. You, we care about manufacturer.

[00:11:45] We don't care about either purchase order or sales order. Okay, great. So essentially what we want to do is a way that when I find this record the one I scanned, I want to be able to click a button to create a sales order. That is a linked record in another table. So we're going to leverage a new field called the button field.

[00:12:08] So create sales order.

[00:12:13] Go to button field. So button is in open beta field. So I can, I don't have the link with me, but if you search, you can go to the community. You could automatically get this new field type in air table. And essentially what it is actually initially a URL. So it says label is open URL. What we want to use, create sales order.

[00:12:38] Let's give it a green, this little green like that in our action right now we have open URL. That's what we want. And remember that when we click this button, what we want to happen is to create a new record in our sales order table. So I'm just going to write, create sales order table. So let's just do that.

[00:13:03]But essentially we have a button and now what we want this button to do is to create a table, a record in another table. So when you're thinking of, okay, we need to automate this you need to usually think of Xavier. So let's go ahead and think of how do we do this in Zapier? So essentially what we want is when this is clicked, we want to send information Tuesday, peer to say, Hey, the product ID

[00:13:30]Was just sold. Please go ahead and create a record in the sales order with that as a lead record. So let's go ahead and do that and how we're going to do that ease essentially through web hooks. So I'm gonna start this from scratch. Recreate a zap and we're gonna call this create sales. So let me know if so far the button field's been clear, it's a URL.

[00:14:01]That's what it does. It opens a URL, but by opening a URL, we can actually send a web hook Tuesday peer with information about what is being sold. So we're going to use web hook here. Whoop. That's not how you write web hook web hooks by Xavier choose trigger event. We're going to catch a hook, continue that.

[00:14:22] So it's going to give us a URL. So let's go ahead and copy this URL. And initially we're not going to put any information into this web host. We're just going to say HTTP. So just go send a web hook that says the question. Mark says that it's the start of the data you want to send. And we're going to say data equal test.

[00:14:46] Save that. So essentially when I click on create sales order, now what it should do is open. What's the URL of that web hook. And then Zapier should catch that web hook and say, okay, I've received some data and we're going to pass information in that URL. So let's see if it caught that trigger. So see, it's a query string data is test.

[00:15:17] So now, no matter what we click here, if I click another one, It still says data equal test. Let's refresh this a little more. Oh, it's fun. The same one. Okay. Let me give it some something else that the data tests you

[00:15:42] we're opening that up. Let's load more. And then it just found test too. So if you've been following me so far, this should give you some ideas of how we're going to pass information into zip here from the existing record of the barcode we've just sent. So what we're going to do is actually pass the record information into the web hook so that which record was clicked or which item was sold when that person, the sales person clicked on that.

[00:16:13]So let me know if you're with me, let me know if that makes sense. So essentially we're going to change the parameters of this web hook to add information from our our sale. Now, what we need to know is just the product ID. That's really what we need to send to Xavier. We need to send the product ID so that in the sales order, let me see.

[00:16:38] We just need to create a new record. We can put today's date and then we want to link to product. And then all of the other fields. So quantity we're going to assume is one, but you can make that variable prices, a lookup a is a calculation. And so we just need to see sales platform. We'll configure that in save here.

[00:16:57] So essentially all we need to send is this product ID. So let's go ahead and do that. Let me know if that makes sense. Customize field type. So product ID. So essentially I'm just going to send the product ID of the record that is being sold. So it's good to do this product ID. Let's save that. And let's just test this, so essentially, if I find this right here, this is our little scan, create sales. Yeah. That was going to open a URL. If I find this one, it should have the product ID of what I just sold. Query string product ID 1 0 0, 0 8 or one eight. So that is exactly what we wanted. And now what we can go ahead and do is create a new record into our new table.

[00:17:48] Hey Colleen. Good to see you. It's been awhile. So let's go ahead and create a new one. Sorry, air table action. Create record. Continue. There we go.

[00:18:09] Base. We want to use the inventory tracking base and then table. We want to create a new record in the sales order. So date. I forget. So you have today's date, zip, your meta. There we go. Zap Metta human. Now

[00:18:33] what that does is just inputs today's date. So this is some metadata. The zip here has around when this zap was run. So you can put that into a Into a field, right? So product, that's what we're getting from product one. So we're going to get that clear shrink from the web hook quantity. We're just going to assume that it's one.

[00:18:57] So one, there we go. Price, ease of lookup revenue as a calculation and sales platform, let's assume that our POS is at a farmer's market. So remember our goal here is to create an automated way to track inventory, to do sales. So we're just going to assume that this zap is specific to a farmer's market.

[00:19:17] So let me just go ahead and go into that single select farmer's market. Copy paste that. And think I learned over the weekend when I was setting this up is that you could add emojis into Zapier. Cool. So essentially what we're getting to is that when I click this button for any of these different records, any of these products, it should automatically create a sales order, which we're tracking through the sales order tab or table.

[00:19:54] And I'll talk a little bit about all the open, all the things that this opens up that I find really cool in just a moment. So let's. Let's test this out. Let's go continue, test and review. So let's just see if it created the right one, boom 1 0, 0 18, and create our linked record. What happens now is that we have now have two sales orders and we've sold two units and our inventory is now down to two units.

[00:20:27] So all of that is done with a click. Let's do a few more. So let's just turn this off. Okay, there we go. Let's turn this on. All right. Let's try this one. Hopefully that works catches the web hook. See that update. We update to three. That's really cool. I don't know if you saw that. Let me do it again.

[00:20:54] Let's just do it again. It comes like here and then updates to four. How is that? Is that we're always creating a new sales order for every single linked record. So those last two were four 17. The one before was four 18. Cool. Okay. Let's and all this together. And then we'll talk a little bit about other things that this opens up new memory around inventory management, invoicing, and a bunch of others.

[00:21:23] But let me know if you have any questions. Now's a good time. There's kind of one step left before I talk about and wrapping this up, but let's just do one quick loop of everything. I control F I want to find an item. So let's say these are my, I wish I could show you, but it doesn't. I got to stick it on something, but I am scanning here.

[00:21:43] So I'm just scanning this right here. So I've got to hit my scanner because it's cheap.

[00:21:50] There we go. I scan, whoops. There's a lot of stuff though. It doesn't make sense.

[00:21:57] There we go. I scan, I find the right item and then I really do is create sales order that opens up a new tab and it updates my sales order. And I only have one unit remaining. So that was a two click way. Imagine you have thousands and thousands of items. You could use air table as your backend for your inventory management.

[00:22:21]So I was quite excited about that. I dunno if you were now let's talk about, just a few things that this will open up and that's where we'll end for today. So imagine okay, so right now, you want to attract. Your inventory. So right now, what I know is that I know in this space that I have one unit, but what I'd love is that when we're close to zero, maybe under three, we create a notification that says, Hey, you need to order more of X, Y, Z.

[00:22:56] Right? So that's another functionality that we could expand on because we could send this information into the web hook. So let's go ahead and. Add additional information. So this is just an example. I'm not going to go too deep into this, but I want it to give you an example. So let's say our next step is remaining quantities quantity equal. And I need to put an ad percent say I'm sending a second equal, right? Let's actually create a number of fields. There's just remaining inventory.

[00:23:43] A formula field that is units ordered,

[00:23:53] ordered

[00:23:57] minus units sold. This is just the number, part of this right? Coleen ass. How is inventory being deducted? So essentially we have sales orders which have a quantity, and then we have purchase orders and both of those are linked records. So we arrive at two roll-ups, have units ordered units sold.

[00:24:18] And the difference between the two are inventory. So essentially every time I click this, it removes one from the inventory as the web hook in Zapier. Now what we want to send is sending the remaining inventory. So let's go ahead and go back to our web hook. Like this quantity equals remaining inventory like that.

[00:24:45] So let's save that. It was missing a little 10%. There we go. So let me just make sure, so essentially what I should get in the web hook. If I click on this product ID is five and the product ID right here. So let's go ahead and do it. So let's go back here, catch the hook, find data,

[00:25:13] load more. And then I have quantity equal five. So that's one more. So now I'm at four, right? So right here, I'm at four units right here. Cause I just created a new sales order, but this is essentially saying before selling the last item, you had five. So what you can do is an if pass.

[00:25:44]That checks whether what inventory level you are at and then sends a notification. So let's say let's edit this one and just says, path name is a scent. Yeah. Low inventory.

[00:26:05] So let's say cash. Quantity

[00:26:13]Less than five. And so then you could send a slack notification after that depending on your inventory level. So that's one way another thing that you could do, and I'm not going to go into this is just for fun. You could also start sending it in. So you could start after the step, you can not only create a a notification, but you can also kind of create invoices or do things like that.

[00:26:41] So web hook really opens up a bunch of information, really getting closer to that app functionality directly out of air table. Cool. So that's really what I wanted to show you. So I'm going to stay a couple more. Yeah. Happy to answer any questions around button, field, web hooks, any ideas that you have otherwise yeah, I'll let you go.

[00:27:08] So let me try this new camera holding. There you go. Oh my God. Huge. Sorry. If I'm like looking here is that's where my chat is like that. Okay, cool. So let me know if there's any questions. Such fun to do again. Next week I'm probably going to be showing how to, how I track my time using the scripting block and different blocks.

[00:27:34] And after that, maybe we'll do finances, but I'd love to bring you on. So if you have a cool build that you're interested in and you'd like to show it off on the stream, let me know. I'm always open to any.

[00:27:51] Cool. All right. Any questions before I sign off today?

[00:28:03] Nope. Okay, cool. You all know how to find me on Twitter? Parent Corp on Twitter, a R O N K O R on Twitter. Yeah, reach out. This was super fun. Hope to see you next. And as always happy.

‍