How to Link Tables in Airtable: 102

if you're struggling to link tables correctly inside of air table then this video is for you we're gonna be going step by step through the very process that we use with our clients in setting up their databases correctly so if that's of interest to you you're in the right place stick around and let's get into the good stuff hi my name is Gareth proto vhost I'm the owner of gap consulting consulting group that helps businesses get automated and organized using air table and sappy er now in this video as I mentioned we're gonna be going into the most critical success indicator inside of air table and that is understanding how to properly link your data and listen before we get into it I need to you know emphasize how important this is because the reality is if you're using air table like a regular spreadsheet like it's Excel you're missing out on the great functionality of the software which is really the ability to link to related data so without further ado let's jump on into my screen and set one of these up on our own so when we set up sit down to build a new database you know if we're starting from scratch this is exactly what we see you know just one table without a name and and we just have these three fields and let's go ahead and create an example here where we have I you know we're building out a database and we're going to think through the different steps of do we need this to be a new table or not because the most common thing that I see used incorrectly an air table I find that people build new tables as if they would build a new sheet inside of Excel and the reality is you can most often keep that data in one place I'll give you an example I had a client once who had three different locations and each location that they had they created a different table for when in reality all that data could have lived in one table because it was the same type of data and so they could have kept it in one table and instead broken it out based on a status or a location indicator so in this example let's imagine that we have I you know consult we have a consulting practice where we have contacts and then we have consultations with people in those contacts and then we also set up you know some sort of billing for those contacts based on that those consultations so let's take a look at how we might envision that so of course if we at the high level there we might have our our contacts and as we mentioned another another part of this would be the consultations that we have with our contacts and the last part of this might be invoices sent to the contacts so a couple of things to think about here is how this data is related does the invoice belong to the contact or does it belong to the consultation do we charge for every consultation if so maybe the invoice belongs to the consultation or in some cases maybe we have free consultation so the invoices aren't charged for another big thing that we need to address is how did we determine these tables so let's get into that part first the way we determine these different tables first and foremost is is it a unique set of data or is it a point of data that lives inside of another data set give you an example of this invoices belong to contacts because each contact we would send an invoice to one or more invoices go to a contact and so invoices could be just a part within the contacts so let's imagine we have you know different examples here of contacts I'll use my own name for this example let's suppose I'm the contact inside of this database well we could create something that has invoice numbers with let's suppose a multi select drop-down and this would be let's say op you know invoice 1,001 and invoice 1002 and this could go on forever of course and we could select which of these invoices belong to each contact inside of the contacts table this is how we would put this in a spreadsheet right but when we're getting into the nitty-gritty of what makes air table awesome it's that relational database part and so if we're gonna really tap into that what we want to do is create a new set of data called invoices and the value in this is that when we drop into the invoices table we're gonna have more information on each invoice so rather than having this multi select drop-down where as in this example invoice is a data point inside of the data set of contacts instead we're going to have invoices as their own data set and so in this example let's suppose we had invoice numbers that's going to be the leftmost column which we call the primary field the primary field is where we I'm where we use a unique identifier in order to make each one of these records unique and so in this case we might have the invoice number because each number is a unique identifier for that invoice right so we've got these three examples of invoices and now what other data points do we collect at the invoice level well each invoice of course will have an amount let's say how many you know how what's the value of that invoice we'll have the the date that the invoice was sent and of course we'll have a date that the invoice is paid and then how do these invoices tie back to contacts and this is where we're going to set up the linked relationship and so the first step of that is of course naming the field as we have done contacts and now we're going to go down and select the type of data that lives in this field and in this case it's a link to another record so we're going to link to a record and you'll see here that we have options before us to either create a new table or to link to one of our pre-existing tables in this case we want to link to contacts now we have to answer a couple of questions in terms of how we want this data to be linked do we want to allow multiple records to be linked in the same place and this has to do with the relationship type between invoices and contacts if an invoice can be sent to multiple contacts one invoice can be sent to multiple contacts then we would say that we want this to be linked to multiple records but in the case of an invoice each single invoice only goes to one specific contact so we're going to turn this part off the other part that we can do is limit the selection to a view this is a bit more advanced and we have some other videos that are specific to this case so I'll let you find those I will post something over here that you can can check out so we're gonna go ahead and leave this for now and allow that link to be established and the important part of establishing a link is the reciprocal part of this relationship that is to say when we connect invoices to contacts the opposite must also be true if contacts are connected to invoices invoices must be connected to contacts and vice-versa so in this case now that we've created this connection here to the contacts table this connection is created automatically and we can go in here and refine this a little bit so now we have to go through the same illogical questions can one contact have multiple invoices and of course the answer to that is yes we could send number any number of invoices to one client or contact so we'll keep this on in this example and now we'll get rid of our multiple select field and if we send that invoice we can now create this here 1001 and so now when we go back to invoices we've established the relationship between this invoice and this contact and you see that when we link that data in one place it pulls through to all the different sections so we could imagine that if we had an invoice amount let's say five thousand dollars that invoice could be sent on March 10th perhaps it was paid on you know March 15th great now this this is awesome stuff to track because not only are we keeping track of the data but we also have this interconnected database or as we call it a relational database and it's very easy now to go back in the future we can come back to this and check on the invoices that went out to each client how long it took each invoice to be paid etc let's build something similar to consultations so perhaps we have a model where we also have consultations that are not paid for so like for example the model of our company we have free consultation options for people who are looking to perhaps secure our services and on those calls we prove our worth before we submit a proposal in in many cases and so in this example let's suppose that people were signing up for different consultations and there would be of course the date of consultation and we can set that here let's go ahead and get rid of these blank records and just create one let's suppose we had a new consultation scheduled for I don't know Wednesday of next week and in this example of course a contact would need to be present for that consultation so again we need to build that relationship to the contacts so again we name the field type and we choose to link to another record and we're going to pull that information back from contacts or create a new table in this case we want to pull it back and we're going to link here and again go through the logic in this case a single consultation will be held with a single contact and so we will not allow linking to multiple records here however the reciprocal relationship which you see is automatically created this we will allow to link to multiple records because perhaps we have multiple consultations with one contact let's go ahead and set that up and what that might look like so let's suppose we had a consultation scheduled for with this contact on this date well we could take notes of course and whatever other data points we wanted to collect we could include them here in other fields of this consultation so maybe we have a question where we are determining internally if this if this contact is a good fit for our services or you know any number of different things that we might take into consideration here so whatever whatever those different data points are those data points live at the data set level and so the data points become our columns and the data set is the table and so we then want to give this a unique name we can call this the primary ID which is what it is and in many cases what I prefer to do here is use a formula that's going to be a concatenation which is to say a combination of multiple strings and in this case I might take the date but I'll first wrap it with a date-time format and then I will add on top of that the contacts name and in between here of course give it a bit of a separator see if I got that formula right and so now this has a unique name the the value to doing this where we concatenate multiple pieces of data into one primary key is let's suppose this same contact were to schedule another consultation with us we would want to keep those separate and so it's quite easy to see which of these consultations is which by the primary key here and you'll see that once we establish that relationship again in one place it's automatically going into the other place as well so if you are getting stuck when you're setting up your database the big question to ask yourself is is the thing that I'm discussing right now a data point or a data set a data point is one specific piece of information that lives inside of a greater data set a data set is a large amount of information with multiple data points so think micro or macro if there are a lot of different things that you need to track on each level for example with an invoice you've got a date what to date state sent date paid amount who is responsible for paying it that's a data set invoices are the data set each point inside of that data was sent who's in charge of paying at all of those things those are the data points belonging to that data set all right as always I hope you found that to be super helpful if you did please be sure to click Subscribe so you don't miss out on future air table content we release new videos once a week and if there's any more advanced work that we might be able to help you out with be sure to swing by our website and check out a lot of the resources we have there and maybe even schedule a call and we can chat and get into the good stuff with you in the meantime best of luck as you continue to grow your empire [Applause] [Music] you

Channel: GAP Consulting

Views: 50,293

Keywords: airtable, tables, linking tables, connecting tables, Gareth Pronovost, Airtable Consultant, airtable training, airtable demo, airtable tutorial, GAP Consulting

Id: H9z85EwljNw

Length: 14min 20sec (860 seconds)

Published: Mon Mar 18 2019