Transcription
This is a full tutorial where I describe to you first what Airtable is and then go into how to actually use it. I even made this handy table of contents that you can use to reference later.
Airtable has incredibly passionate users, but if you ask one of them how it actually works, you often don't get a very satisfying response. So, first and most importantly, why is Airtable so special? It's actually because it has a talent that we as humans are also quite good at. I'm talking about the human ability to understand relationships. Think of three of your friends who have a dog. Even by trying to think of friends with dogs, you might have thought of those three firsts because they were related through other similarities, like maybe they're friends with each other or they live in a similar place. It's pretty cool that you can call up all of these people and categories so quickly. But if I ask you to list every single brunch you've ever been to with those friends, you'd probably be a lot less good at that.
And that's where a spreadsheet traditionally really shines. A spreadsheet can hold a whole big list of tiny details, but a spreadsheet is generally just good at holding one type of information. So, for example, like every line is one brunch. If you wanted to record information about your friends, that would be a new tab or a new table, and information about their dogs would be a third tab. Airtable is the Goldilocks in this scenario because, like you, it understands relationships, but like a spreadsheet, it can record all of those tiny details.
Relational databases like Airtable are not new. They're the basis of pretty much everything on the internet, but Airtable is the first one of these that is easy to use as a spreadsheet. It even looks like a spreadsheet, but it's better. Let me show you how Airtable actually works. And we're actually going to use the friends and brunch analogy. Except, because you're probably using Airtable for work, let's say that those friends are actually clients and the brunches are meetings that might turn into a sale.
What you're looking at here is my Airtable workspace, and a workspace is basically like a folder that holds all of your Airtable databases, or bases for short. If I wanted to create a new base, I could click this little button here to create a new base. For this tutorial, I actually created a base for us already and put in some example data. So I'm going to open it up by clicking this JulianCRM, and you can see that I already made a table for clients, one for meetings, and one for the client's dogs. If I wanted to create a new table, like I did for these three, I would click this plus button here and click "Create a blank table."
Now, Airtable is set up like a well-designed spreadsheet. And what I mean by that is there is one line of headers here, and then each row or record is a new line of information or package of information that starts with this leftmost column here, which identifies kind of what that line is about. So, in the example of clients, this is a client, right? So we've got the client name here, and then we've got identifying information about the client to the right. And then when we go to the next line, that is a new client.
In Airtable, a column is called a field, and a row is called a record. You also specify what type of data each field is. So, for example, this first name field here, if I open it up, I can see that it is a single line text field. And over here, the birthday field is a date field, meaning I can actually double-click here and use this date picker to pick a date, or I can just type it as well. Airtable even has attachment fields where you can actually drag and drop something like a picture into a cell. And so we're going to use these pictures here that I've dropped in later on when we build our interface to build client profiles where we can actually see their picture. Attachment fields are also super useful for holding things like invoices or receipts.
If I want to create a new field, I would click this little plus button here, and then I could name the field and pick the field type. And we'll do that for a couple of fields later. In Airtable, we can also write formulas. So this name formula, all the way on the left here, is a concatenation of the first name and last name with a space in between. And so this formula, once we pick formula type, it gives us this little window here where we can start typing. And in Airtable, a column is applied to an entire field or an entire column. And one of the benefits of that is that we can actually use the field names in our formula. So instead of using some confusing coordinate like C5, we just say, "I want to take the first, and then a space, and then last name." If I click save, then I've got the person's full name.
So that's our clients table here. And if I go into the meetings table, you can see I've got a date and a location of the meeting. And I've done the same thing here that I did in the other table. I concatenated these two things together to get a more unique identification of each meeting. Once we've linked this table to the client. And then in the dogs table, we've got the dog's name and then the type of dog.
And this is where things start to get interesting, because now we're going to establish the relationships between these three tables. So if I go into clients here, I'm going to create a new field. This field is going to be called meetings. And I'm going to click this first option, "Link to another record." And when I do that, then it gives me the option of the other tables that I can link to. So I'm going to click meetings here, and I'm going to allow linking to multiple records, meaning yes, one client can have multiple meetings associated with them. And then I will create the field. Just skip this for now.
And what that's done now is, if I go ahead and click this little plus button within a cell for Danny Rojas, then it gives me a list of all of the meetings in the other table. And so now I can just say, "Okay, I know I met with Danny on 8/16 and also on 8/13." When I go over into meetings here, on the flip side of that, I can now see for both of these meetings, they're associated with Danny Rojas. And it's worth mentioning that just like you can have multiple meetings per client, you can also have multiple clients per meeting. So, for example, if I wanted to add a client to this meeting that I had on 8/16, I could actually do it. I could do it here or in the first table. But here, I'm going to add Keely to the meeting. And so they're they're both here on this meeting. And then if I go back into clients, I can see that Keely's got this meeting associated with her now.
So I'm going to go ahead and fill some more of these out so that every meeting is associated with a client, and then we'll move on. All right, so I'm going to try to make a little bit of room here so we can get in our new fields that we're going to add. So next, of course, I want to establish the relationship to the dogs. Every client's got a dog. So we're going to go in here, and I'm actually just going to skip field naming even, because if I click "Link to another record" and then click dogs again, I guess I can allow linking to multiple records in case someone's got multiple dogs. If I hit "Create field," Airtable is pretty smart. I can skip this step. It's going to automatically name the field "dogs." It knows that that's probably what I was gonna name it. Now I can see the list of all the dogs, and I'm going to go ahead and link all the dogs up.
So the last thing about establishing relationships is that they can allow us to aggregate important information when we want it. For example, if I want to know in this table, for some reason, what kind of dog each client has, I can go ahead and just create a lookup here. And so this is going to be called, let's see, "Dog Type," and it's going to be a lookup field type. If I choose dogs here, it's going to look up, and it gives me the option of which linked record to look up from. And then I can choose the field that I want to look up. So, in this case, it's the type. If I hit "Create field" here, it's going to now pull in all the types of the dogs.
Leveling, probably more importantly, I could use this to say, "Look up the last date that I met with a client." So if I want to do that, I actually would use a field called a rollup, which is used basically for looking up information when you know that there are multiple values and you might want to average those values, or get the latest of those values, or pick one out of a bunch of different options. This is going to give you. So, in this case, I am going to call this "Latest Meeting," and then I will start typing "rollup" here to find my rollup field type. And so the rollup field type also asks me to pick which linked record I want to look up. So I'm going to go to meetings here, and then I can choose the date. So looking at the date in the linked meetings. And here you can see that it actually guessed which formula I wanted to use with a rollup. We use a formula to tell it how we want to aggregate the data. And it's wrong. I don't want to use "organique array." Unique would give us all the unique values, meaning in this case, all of the unique dates that we've met with that particular client. What I want is the latest one. So what I'm actually going to use is the max function. And for any of these functions, "values" just lowercase "values" represents the the information that it's sending to us that we're going to aggregate. So if I type this "max" here with "values" in parentheses, hit "Create field," it's going to give me the latest meeting I had with each client. Pretty cool, right?
By the way, sometimes I might want to just see a subset of this information. And in a spreadsheet, often you kind of copy the whole table, paste into another table, and then sort it and maybe remove the stuff you don't want to see. In Airtable, we create a view for that and just filter it. So, for example, let's say I only want to see clients who have a sheepdog. I'm going to click this little grid view button here to create a new grid view. Call this "People with Sheepdog," create new view. And then I'm going to go ahead and filter where dog type has any of "Sheepdog." Now I can see just the clients with the sheepdogs here. And these fields, if I edited them, are still going to edit the same data, right? It's all the same data. We're just looking at a different way. If I change Jamie Tart's birthday to March 30th rather than 31st, if I go back into the grid view, then uh, his birthday is changed here as well. Let's change it back to his real birthday. You can see that over here.
We've got a number of really useful views. The one other one that I'm going to call attention to in this tutorial is the form view. Let's say you have a colleague who is often taking notes and recording information about your meetings. You might not need to give them access to this whole base, but you want to have a way for them to enter that meeting info and have it end up here. So if I click this form view and create new view here, it is going to automatically create a form for me with all of the same fields. And now you can probably get an idea of why they're called fields, because we we use them in different contexts, not just as a table. But it's already created this nice form for us. And when they fill out this form, that information will just end up as a new record in your table. So, in this case, I think that all of these fields are probably useful. Although, you know, are they going to have the picture when they entered in? Maybe not. So maybe I'll remove picture here, and I'll let them fill out the rest. In order to share that with someone, I would just click this "Share form" button here, and then I can copy this link. And if I were to actually click this, this is what it would look like if your colleague opened it up and filled out the form. One other advantage of form views is that you don't need to be logged in to fill out this information. So this person does not have to have an Airtable account. They can simply click a link. Anyone can click a link and then fill this out and hit submit, and that information will end up in the table.
Now I want to pause for a second because you could get a lifetime of value out of just the features that I've showed you so far. And as a beginner, I encourage you to start simple and to just start using something that you're going to get a lot of value out of. So if this sparked a cool idea for you, by all means, go create your idea. You can always come back and see the rest of the features. But I do want to make sure and include these other features because the stuff I'm going to show you next is really so cool.
So the next thing I want to do is I want to use this setup to actually save me time at work and automate some of the processes that I have to do manually right now. And the first one, one of the really low-hanging fruits here, is following up with clients after the meeting. So if I go into the meetings table here, I can see that I've got the date of the meeting. And it would be really cool if two weeks, exactly two weeks after every meeting I had with a client, Airtable automatically emailed them for me, just as a follow-up, maybe thanking them for their time, or so we can keep the conversation going.
So in order to do that, we need to get all the information we want into this table. And then we're going to create the automation. Actually clicking the buttons to create the automation is probably the easiest part of this, but we always need to do a little bit of setup first to make sure we have all the info we need. So in this case, we've got the date of the meeting. We're going to need to calculate the date where we send the email, which is two weeks after this date. And then we also need the client's email address, and we might as well pull in their name as well, so that we can address the email to them. So let's make some room here. And the first thing I'm going to do is create our date field. So this is going to be a "Follow-up Date," and we're going to create a formula here. And the formula that we're going to use is called "Date Add." So if I start typing "Date Add," it's going to pop up from here. And the "Date Add" function takes a date time, which is what a date field is, and then we give it a number. So in this case, it's going to be 14 days, and then a unit. We also could do two weeks, but I'm gonna do 14 days. So let's go ahead and start typing "date." Here, this is how we actually specify this date field here. We can just type it in, and then when I click it, you can see that it's purple. So the green is for the function, purple shows you that you're referencing a field in the table. And if I hit comma here, I can now say 14 comma, and then within quotations, and you can use either single quotations or double quotations. I like to use single ones because it's only one button to press instead of two. And I'm going to say "days" here, and then close the parentheses. And if I go ahead and create the field, it is going to give me two weeks from that date. Now, it by default gives you a time as well. If I want to get rid of that time, I can go into the formatting here and then untoggle this box that includes the time. Click save.
So now I've got my follow-up date here. I want to bring in the client's email address and their first name. So let's go ahead and create a lookup field. This is going to be called "Email," or maybe "Client Email," and it's going to be a lookup. We'll look up in "Clients Email Address." Create field. Remember that we sometimes have multiple clients per meeting. And so if we create this rollup here, it's actually going to put multiple emails for us, which actually will work in this case. If I want to send an email to both people at once, which is how we'll set this one up, this will work this way. If we wanted to send individual emails, we can also do that, but we would set it up differently. And then lastly, we are going to get their name. So we'll do "Client First Name." And I'm going to create a lookup in "Clients First Name." Create field. And so in this case, you know, we're going to create an email that's gonna be addressed to, so it's going to say "Hi Sam," or it's going to say "Hi Danny, Keely." I think that's good. You know, again, we could set it up so that it's emailing people individually, and then it would just say "Hi Danny, following up with you." But in this case, I'm going to roll with the group emails here.
So up at the top here, I also want to Now call your attention to these three pills here. And these pills are really cool because they really show you kind of like the overarching structure of Airtable and how there are really three pillars of how this whole system works. We've got data, which is what we focused on totally so far, right? This is your database, your structure, your relationships. And if you think about actually like what it takes to create software, because I didn't want to freak you out at the beginning, but really what Airtable is for ultimately, or what it allows you to do, is to create your own application, create your own software. And the three things that you need to create software are data, logic, and a graphical interface. And that's true if you think about any app you use. You have a graphical interface with just say a Google search. So you have your Google search bar, then you're going to type in data, and there is logic behind it that is then going to go find the results that you're looking for and then display it for you. And where it's going to find the results is the data layer. So obviously, that's a much more complicated version of this. But in this case, we're doing the same thing.
So we are now going to move on to our logic. And in Airtable, logic is automations. I would also count formulas as logic, but they've set it up here, I think, really so that we can think about it in this way. And so I'm going to go ahead and click on the automations here. And any automation that we use starts with a trigger, and then it has actions that we're going to take. So in our case, the trigger is going to be when the date that two weeks later date matches today's date. And so I can do that by actually, let's name this first. This is going to be "Two-Week Follow-up." And I can go in and add a trigger here. And the trigger is going to be "When record matches conditions," which then allows me to go over here and pick the table. So I want to look at the meetings table. And then when the follow-up date is today, it's going to trigger this automation.
So now that I got my trigger, I can add my action. And in this case, the action is to send an email. You can see other actions are "Create a record," "Update a record," "Find records," and we can even run scripts, connect with other applications like Slack and Microsoft Teams. So there's lots of options here. In this case, I we actually can send an email that goes from Airtable, so it comes from an Airtable.com domain. But we can also link it with our Gmail account and send from our Gmail account. This is also more compatible with the free plan. And so if you're starting out with a free plan, the Gmail option is a good option. I'm going to go ahead and use it. So if I click this "Send email" here, then I can connect it. I've already connected to my account, so I can just select my account here. And this is where I can then dynamically use those details that we set up. So the "To" field is going to look in that record that matches the conditions from the trigger, right? So basically, anytime we use this little plus button here to take what we call dynamic information that's coming from somewhere else, we have this "Use data from" window, which shows us where it came from. So what I want here is the client's email address. So I just start typing "email," and now I can see this "Client Email" that we set up before. And then I want the value, just meaning like the text value of that email. For the subject, I'm going to say "Checking In." And then in the message, I will say "Hi," and then I want their first name. So I can go in here and find the "First Name Client First Name." Comma, "It was great to see you a couple weeks ago. I'd love to meet again soon. Pass Julian." And that's it. We've created our automation. I can go here and generate a preview, which is then going to ask me to run tests and actually pick an example record. So I can pick this this record because it actually was two weeks ago. And then it's going to run the tests here and then show me what the email will look like. So we can see this particular one is going to go to three people: Danny Rojas, Jamie Tart, Rebecca Welton. And then it's going to say "Hi Danny, Jamie, Rebecca. It was great to meet you a couple weeks ago. Love to meet again soon." Looks great. To turn this thing on, I just click this toggle here, and this will now send an email two weeks after each meeting in the system.
So now that we're saving all that time, I also want to just design a much nicer way to look at my clients' information because, you know, spreadsheets are great, but let's be honest, this is not the most pretty way I can look at this information. So I'm going to go ahead and create an interface here by clicking this "Interfaces" pill, click "Start building," say this is "Client Dashboard," go ahead and click next. And I am going to use. We've got lots of different options of how we could set up our interface and layouts. I'm going to choose the "Record Review" layout, click next. Gives me options for how to initially sort the information. I'm just going to go ahead and click next. And then let's see what I want to keep in here. I want to have their name, their picture. Don't need first and last because the name already has that in it. Email address is good. Let's say birthday, dogs, latest meeting. And I'm going to go ahead and click next here. And lastly, it's going to ask me for the page name. And this is because when you create an interface, you can have multiple pages for that interface. And so this one, let's say "Client Summary." Go ahead and click "Finish." And man, that was just so quick to get to this minimum viable product here that we've created. And of course, we could create a lot more pages and different pieces to this. But if I just go ahead and click "Publish" now, I've got this nice format here where I can see each person and I can see the information about this person, the latest meeting I had with them, etc. And you can see that these things are currently not editable. But if I actually go back into "Edit" and then I click on these here and on the side, I toggle them to be editable. So I'm going to do this with each of these things. For a linked record, that means it's going to allow users to link existing records. And for latest meeting, of course, that's not editable, right? Because that's a calculated field. So now if I go ahead and publish, I've got this nice format here. But then if I wanna update Keely's email address, I can go ahead and just go in here and change that.
Interfaces, just like the data layer, can be used to create forms as well. So if I actually go back into "Edit" again, one other really great enhancement I can make to this is if I click on my record picker over here, I can then scroll to the bottom here and allow users to create new clients. And that's going to create a form for me here. That if I go ahead and publish, I can now click this little plus button, or someone else who's logged in, and then they can fill this out to create a new client.
So I think I've given you enough to chew on for one lesson. I'm going to go ahead and go back to our data layer because this is really, you know, where things start and where we want to anchor ourselves. So should you use Airtable in your business? If there's a tool out there that does exactly what you need, you'd probably use just that. Airtable is for people who need more flexibility. It's about creating your own software that meets your own unique needs. And typically, in the past, that function has been served by spreadsheets. Because if you're a really large company and you have a huge budget, you have the ability to really to hire engineers and create custom-built software for your application. But most teams, or even teams within those large companies who need to move faster, will create their own systems with spreadsheets. And Airtable is now a solution that sits in between the spreadsheets and the custom code. Because it is much faster to iterate with than the custom code, you can see how quickly we just set up the system that we just did. But obviously, it is much more structured and has many more features than you get out of a spreadsheet.
So if you like this video and you want to learn more, check out my channel for lots of specific build tutorials on how to create a really great inventory system in Airtable, how to use it for hiring, for collecting RSVPs, lots of different specific use cases. And if you're a part of a business who could use professional help in creating your Airtable systems or maintaining them, I also offer services for that, and you can learn more about those services in the video description below. So I hope you have a great time exploring Airtable, and I will see you next time.