How to Link Tables in Airtable - from beginner to intermediate linking

Are you struggling to understand how linking tables works inside of air table, maybe you're brand new to using Airtable and connecting datasets like this, or maybe you've been doing it for a while, and you still struggle with the right type of link to use. Well, in either case, this is the right video for you, because we're going to be uncovering it all from beginner to intermediate table linking. So stick around, and let's get into it. Welcome back to the channel. If you're new here, my name is Gareth. I'm the owner here at GAP Cnsulting. And we make it our mission to help you organize and automate as much of your business as much of your life as possible, using no code tools. Now today, we're going to be talking about air table. So before we actually jump into table linking inside of air table, I want to offer you access to my Airtable Crash Course, it's a free crash course it's going to be delivered to you over a course of a couple of days. So if you're struggling to understand all the core elements of air table, this is the perfect place for you because it's going to give you all of the guidance you need step by step as we uncover all the different pieces of air table together. So check that out at garethpronovost.com/airtable-crash-course. And you can sign up for free there with your email address. But without further ado, let's hop on into my screen here and take a look at what we have so far. So I've got a couple of different tables set up. And it's important to note that a table is nothing more than a data set. So every table that we build, an air table is going to represent a set of data. So here I have clients. Here I have companies, orders, line items, and inventory. We're going to look at these in more detail in a moment. But first, let's start talking about the most simple table link that you can establish. And it is a direct link between two tables. So from one table to second table, you establish that link. And the thing to know about a linked table relationship and air table is that if you link one to two, then two is also linked to one, there's a reciprocal relationship in dealing with linked tables inside of air table. So you can't have one table linked to another and not include the other side of that link as well. So as soon as we put a link into existence from one to two, it's going to also exist from two to one. Now let's take an example here between clients and companies. Here I am in my client's table, I have person one, two, and three. And we would have a lot more information on these people as well, their email addresses their phone numbers, etc. But then flipping into companies, we're also going to see three different companies here, I've got company one, two, and three. And maybe we have some other information that we collect on companies as well. So we would say addresses for the companies, or maybe the 800 number for the company or the main line for the company. So lots of different information we would store here, but the important part here that we're going to focus on is building that linked relationship. It doesn't matter where we start, whether we start from the company perspective, or the client perspective, what we need to do is add a new field, which is just a column in our table. Here, I'm in the company's table, and I'm going to link it to the clients table. So in order to do this, first I name this field, I'm going to call this my link to clients right, or just clients. Now down here and field type, we have a couple of options, we can select link to another record. When we select this, we're going to see all the other tables that we have currently set up in our database. So here we would select clients if we wanted to link to them. Alternatively, we can create a new table from here as well. But we don't have to go about it this way, we don't have to start by linking to another record. Instead, I can just start typing in the name of that particular table, in this case clients. And there it is, I don't have to select it from the drop down list, as I did in the previous example. Now that's it just the link to clients, it's important to note that both of these approaches will get you the same result. So here I am linked to clients, it doesn't matter if I started with a link to another record, or I went straight to the table that I knew I wanted to find. In either case, I can go ahead and create this field. And when I do this, as I mentioned, the reciprocal of that relationship will also be established. So if I flip into my clients, now I see the opposite of that. Now I have my Lync two companies as well. So these two things are connected. If I were to add a company here to person one, let's say company one relates to person one, when I flip back into my companies, I'm going to see that company one is linked to person one. And so this is a two way street this linked to relationship. Let's dive in a little bit deeper and take a look at the settings inside of the linked relationship. If I look back at the settings inside of link to clients, I have this toggle here that says do you want to allow us to link to multiple records. And what this means is I can allow us to say that maybe a company has multiple clients connected to it, or from the other side of the equation. We might say that a client might be connected to multiple companies. So every time you establish a linked relationship inside of airtable this is an input Question for you to answer for that length relationship. And it's important for you to answer that question from both sides of the equation. So not only do we have to say can accompany connect to multiple clients, but we also have to ask the question from the other perspective and say, Can a client connects to multiple companies. And so imagine that in our CRM, or in our database structure that we were building, maybe we say that a company will only have one client, we don't have multiple contacts for every company that we work with. Well, in that case, we would actually toggle off multiple records here, this would make sure that we didn't accidentally link to multiple clients per company. And you see that here, I can't add another client since I've now toggled off allowing multiple records to be linked. But I can select a new client here, since a new client has not yet been connected. So if I were to pick person two, there, I now have established a linked relationship, but I cannot connect to more than one person. Now back on the other side of things on the clients side of the equation, maybe I would say, hey, you know what a client might represent multiple companies inside of my database. So person, one might be connected to company one, and company three. Well, if I click here, I see that I do allow multiple records to be linked here. So that would be perfect for this type of relationship. So here, when I select it here, I can click the plus and connects to yet another company. So here, I've linked to two companies. For this person one, going back to companies, I'm going to see that company one and company three are both connected to person one. So those are the basics for building any length relationship between two tables. Now, I want to point out that there are three different options in terms of the ways that you can build these connections, with or without multiple record links. If you toggle off multiple records for both of the tables, then you have what is called a one to one relationship, because in this case, one client would connect to one company, and one company would always connect to one client, there would be no multiple connections on that type of relationship. Now, the next type of relationship is what we've actually built here, a one to many relationship, because on one side of the equation, we've said, you can only connect one way for our example, on the company side, we only linked to one client. But the other side of that relationship on the client side of things, we are allowing multiple linkings to companies. So this is a one to many relationship, because on one side of the equation, you only get one connection on the other side of the equation, you get many connections, and so it's a one to many connection. Now the last type, as you might guess, is called a many to many. And this is where on both sides of the equation, we allow ourselves to link to multiple things. So in our example, we would say a client can connect to multiple companies, and a company can connect to multiple clients, that would be a many to many relationship. Those are the three different types that you can establish inside of air table using this toggle. And so when connecting your different tables, be sure to ask yourself that question, can this table connect to multiple things in this linked relationship, and be sure to ask yourself that question from the other side of the equation as well. Now for a slightly more advanced use case, for table linking, we're going to go quickly into what is called a junction table. Now a junction table is sometimes required if you can't build a direct relationship between the two tables already. So let's take a look at an example. Here, I have an orders table. So let's go ahead and label out some orders. I have ordered one, order to order three. So these are orders that people have put in for us. And maybe a client puts these orders in let's build a link to relationship to our clients. Here. I say who is the client, only one client is putting in that order at a time. So I'll toggle off multiple records here and create the field. Remember that we have to ask ourselves the question from the other side as well. So from the clients perspective, can a client put in multiple orders? And yes, they generally can. Generally speaking, a client will order from us multiple times throughout the lifecycle of that relationship. So I'll keep multiple records on from this perspective. I'll toggle it off from the orders perspective. And I'll assign a couple of clients here, client one or person one, person two, and person three, put in some orders. Okay. Now, what are they ordering? If we were to say, Well, let's look at our inventory here. Imagine that we have a small landscaping business and we have some wheelbarrows reclaimed wood flowers and spas that we sell just as some example items. And each of these things has its own price. Well, we can't build a direct relationship between orders and inventory. If we do which is the wrong way to do it. We will not be able to get the functionality that we want out of this. So as an example of how not to do it, if I create a direct relationship to my inventory here, and I build a link. Let's suppose that I said yes, multiple records because people can buy multiple things from us at a time. Sure. Create the field. What do they buy? Maybe on this one? After they brought in some reclaimed wood, and they also bought a spa. Okay, well, how do we know how much of each one of these they bought? How many times did they purchase reclaimed wood, maybe they wanted to have those rung up on their transaction. Well, you'll notice I can't actually choose reclaimed wood multiple times from the list, I can only linked to a record one time per cell. And so I can't link to reclaimed wood multiple times here. And this is why we don't want to actually build a directly linked relationship between orders and inventory. So let me go ahead and break this, I'm going to delete that field, because it's not what we want. For our particular use case, I have to delete the other side of it as well, if you're deleting a linked field, you have to delete it in two places. Remember, it exists twice, so we have to delete both parts of it. So what we want to build here is an intermediary table that's going to work in conjunction with the inventory and the orders and connect it all up so that we can actually utilize quantities as well, let's take a look at how we would do this by using the Line Items table here, it's going to function for us as a junction table, which creates the junction between the other two tables that we require. In order to do that, I need to build two linked relationships in this table. So let's set up a linked relationship to our order here. Here's my link to orders. And I'm only going to allow one order per record here and toggle that off, create the field. Now I need to do the same thing for my inventory, because that's the other piece that it's reaching out to. So here's my inventory table, my link to it. And again, toggle off multiple records, create the field. And now let's fill out what this might look like for an order. So we had order one, order one. And we were talking about how we had reclaimed wood and multiple instances of it for this particular order. And we also on order one had a spot purchase as well. So here we are inside of our junction table breaking down the line item detail of an order. And I can now include things like quantity, which might be a number field, in our case, I can use the integer field type here. And I can say they ordered three bundles of the reclaimed wood and one spa. So now I have quantities associated with these different elements for this particular order. Now very often with a junction table, I'll also want to utilize a concatenate formula in the primary key. So I'll call this my line item ID. And I'll use a formula field type here. And I'll run a quick concatenate, which is a way for us to string together other data points. And here I'll link up my order, and a space and a dash and a space and my inventory. Once I have that all set, I'll save this field up. And this is going to then automatically take on the name of the order and the particular inventory item that was shared in that line item so that you can see the full functionality of this junction table. Let's now bring in the price of these inventory items. So I can look it up from the inventory table so that we know what the cost of that item is. So inventory table, look at the unit price and bring that information back in. So here we see that yes, a bundle of reclaimed wood is $500. This particular spa is 14,000. And now I can multiply my price times my quantity to get my line item total. So here is our total in a formula, I'll take my quantity, multiply by my price, and create this field. Now once this is all set up, I should also include the right formatting. So let's go back in here and make sure we're in currency. And now you'll see the line item total of 1500. Because I have three over 500 apiece. So as a last step, I can now go to my orders table and roll up all that information. I know that I had two different line items for order one, a $1,500 reclaimed wood line item and a $14,000 spa. So if I run a total here, and I use a roll up field type, thanks to the way I've properly built my junction table, slightly more advanced table here. But I can look at my line items, I can look at the line item total here and run a summation. And again, make sure that I am in currency which I am and create the field. And here we have the total for that order 1500 plus 14,000. Let's go ahead and quickly build out some of our other orders as well, I might have order two here and bring in a second line item for order two, they might have purchased two wheelbarrows and four elements of flowers. So here we are, I can see that I have 240 plus 140, which of course is 380. And if I flip back into my orders table, that's the total for this order. Again, as a quick recap we got here by creating an intermediary table that's linking up to both our inventory and our orders. This is a slightly more advanced way of linking your tables but in some cases, it's absolutely necessary. So if you get stuck linking your tables, there's a good chance you need a junction table to get the job done. I know we went fast in this video, but I hope you got a ton of value out of it. Be sure to subscribe to this channel if you haven't already to make sure that you stay on top of no code news just like this, and I will see you in the next video. As always, I hope you found that to be extremely helpful. If you did and you'd like to learn more swing by our website and see how we can help. We offer a free air table crash course that will help you level up in air table quickly. And we also have some paid services, including hourly consultations with our experts. We have some online courses and a group coaching program and for advanced needs, we can build a bespoke solution for you from scratch to swing on by and I look forward to connecting with you soon.