If you're just learning
about air table in 2022, and you feel like everybody else
already has a massive headstart on you. Look no further than this video, because we are going to be talking
about the very foundations, the basics of what air table is and
how you can get to where you want to be with air table. Now,
before I go on, though, I do want to tell you that this is going
to be the first in many videos for the new year 2022, specifically
taking a look at my screen. You'll see that I have planned out
my entire January, 2022 content calendar talking about these intro topics. These are the five foundational
components of understanding air table, and we're going to be hammering
it all out in this video series. So if that's of interest stick
around and let's get into it, Welcome back to the channel. If
you're new here, my name is Gareth. I own GAP Consulting, where I'm going to help you save up
to 20 hours of your time every week using air table and automation. If that's of interest and you want
to learn more about how we do that, definitely check out
my automation webinar. You can sign up for instant
access by visiting me at Gareth pronovost.com/webinar-registration. And that is going to pop right into your
inbox with all the fundamental building blocks of automation, both in air table and out so that
you can start leveraging no code tools and no code automation.
But without further ado, let's get into how to
use air table here in 2022. Now hopping into my screen. The first thing you need to know about
air table is that everything is organized in workspaces and what
we do in a workspace, you can think of it essentially
as a folder for databases, and you can build on the free
plan as many databases as you want inside of that workspace, you have no limitation to your
workspace databases. So again, a base is a database and those
are stored inside of a workspace. So if you want to stay a
little bit more organized, simply add a new workspace and think
of it just as a folder for whatever databases you're going to build in there, but watch out because every
single workspace has its own paid tier. So if you've decided that you want to
unlock some of the more advanced air table features those things like
apps or additional colors, or maybe you want more records, this is all stuff you can check out on
air table's pricing page so that you can see what is involved
with upgrade. Generally, it's record count where people say, Hey, I really need to start paying for this
because I need to store more data in my database, but just know that every single
workspace that you set up inside of air table is its own page tier. So you can actually run up
quite a bill if you own, let's say three different workspaces,
one for business, one for personal, and one for fitness. And each of those workspaces contains
multiple databases while you're paying whatever paid tier for
each of those workspaces. Also note that these workspaces
can be on different paid tiers. So this is denoted
right here by this star. You can see that while I have multiple
workspaces or folders for storing all my different databases. Only one
of them has that star asterisk. And when I mouse over that, that
is the one that is on the pro plan. So be thoughtful about where
you're storing your databases, because all of the databases
that are in my GAP, Consulting workspace or on the pro plan, all of the other ones are on a free plan. And that's really important because
they're going to be faced with certain limitations, depending on what paid
tier you are on. Now, as I've said, inside of a workspace, you will store one or more
databases or an air table jargon. We just call them basis. Each
base is its own icon here, and you can choose these icons. Let's go ahead and set up
a new base from scratch. We scroll down to the
bottom of our workspace, wherever we want to include that base. And we select at a base
right then and there, it's going to open up that base inside
of our system and we can start by naming the base. And
then if we click up here, we just get the chance to name it. I'll call this example base
for 2022 and note that you can pick an icon for your base. So
if you wanted it to be, I don't know, a ladybug, we can go ahead
and make that selection here. And you also have the ability
to choose on appearance. So how do you want it to show up these
lighter colors are advanced colors only available on the pro plan.
If you're on a free version, you'll have to stick to the bolder colors, but I'll go with the light pink
and make that selection. Now, here is the base guide and
you can read right here, you can use this space to
kind of communicate with
others who you're going to share this base with, communicate
with them, what this base is for, what are you using this for? What
is its goal? So I can type in here. This is an example for 2022. And now when somebody opens up
this base for the first time, they are going to see that base guide
so that they know what they're getting into. Now on the right
hand side of your screen, you'll see that you can get
started with a lot of this stuff. There are many templates that
air tables provided for us. We can go right to air table universe
and start accessing those templates. Or we can just start working by
importing some existing data, either from Excel, a CSV copy
paste or Google sheets. Now, the issue that I take with installing
templates is that your workflow, your business is structured in
a way that is unique to you. And so by installing a template, especially if you're not really
well-versed in how to use air table, you'll very likely wind up with
a lot of stuff out of the box. So to speak that really doesn't
help you or work for you. So I don't generally recommend
templates for newbies, because if you're just starting out, you want to really become
familiar with the software. So the first thing to know is that
everything in air table is grouped by data sets. Now in Excel or Google sheets, you will use tabs at the
bottom of your spreadsheets, really kind of whimsically. And
they are just a new sheet of data. And you'll very likely be accustomed to
copying and pasting data from one sheet to another. But that does not
happen in a database in a database. Think of it this way, your data lives in one place and you
don't copy and paste it back and forth. The whole point of a database is that
you're going to be able to link your data together. So if you're copying and pasting
stuff from one table into the other, you're kind of defeating the purpose
of working with a database and you're better off with a spreadsheet. But
instead of going back to that spreadsheet, learn the better way, learn the faster way and only put
your data in one time and air table, and then learn how to link it to
the other things that's related to. So let's take a really easy example. Let's imagine that we have clients and
we offer services to those clients, and then we need to invoice those clients. Each of those three different things
is its own data, set, clients, services, invoices. Those are each a thing that we need
to account for inside of our database. So I'll start by building
a client's table. Now this is my data set for clients
and it's denoted by this tab up at the top called a table. And
it is named for clients. Now each column here
is a field and a table. We'll start off with a certain number
of fields in this case, name, notes, attachments, and status. Each field also keeps a certain
type of data. So in this case, this is single line text data. And we
can tell that actually by the icon, the capital letter, a,
this one here is long text, and that is N capital
a with the lines by it. This one here is four attachments. So yes, we can actually upload
attachments to air table. And this one here is
a single select field. So we can actually
select from some options. Now I want to build this
from scratch. So again, what I'm going to do is delete
these fields. I do that by right, clicking on the field, scrolling all the way down and
selecting delete now for my clients. It's pretty straightforward. I'll probably
just keep their first name on file, create a field for that. And you'll
notice just by hitting enter. I didn't even have to select that this
was single line text because that is the default field type. Now
I'll do it again here, last name and I just hit enter
and it takes me right here. But the other piece I might want for
my clients is their email address. So I'll create an email field and this
time I want to come down to the field type and actually look
up the email data type. And so once I create this field, check
this out. If I were in my own database, it would be like this. And when I see that I can actually
click on that email field type. And it's going to open up an email
from your default email server and address it to that
person right off the bat. So that's a really cool thing to know
because you'll definitely want to make sure that you're using the right type
of data in your fields so that you can unlock all this advanced
functionality. Now, the part that you'll think
that I might've skipped here, I didn't include a full name. Now this leftmost field is called the
primary field and the primary field is one that you want to make sure you spend a
little bit extra time with. In my case, I like to follow two rules
for the primary field. I want to make sure that it identifies
the record that exists in this table, but I also want to make
sure rule number two, that it is as unique as possible. So for clients generally,
since are people, the way we identify people
is by first and last name. And that's pretty darn unique. So
I will make this primary field, the leftmost column in my database. I'll make this the full name and
I already have all this data. I know the first name. I know the last
name. I don't need to write it again. So when I will use here is a formula
field. And inside my formula, this, by the way is very similar to how you
would write spreadsheet formulas in Excel or Google sheets, but I'll use concatenate here to
string together two or more pieces of text. And in this case, I will
call the first name field. I will then separate it with a space
inside of single or double quotations. And then I'll call on the last name field, and this is going to then combine
for me the first and last name. So I don't have to enter it again.
So if I have John DOE in my database, it'll take just a moment. But John DOE will show up here
full name in my full name field. Now I'm going to move on to creating
another table. If you recall, we also offer services. So we
click up to add or import again, you can import from another place, but I'll just start from scratch
because I think that's the best way for beginners in this new table.
I'll call this my services. And maybe we have three different services
in this case, service one, service, two and service three. What other things do we need to
know about these services? Well, we probably want to know, uh, the
cost of these services, right? So let's include the cost of
service. And in this case, we will use a currency
field type because yes, cost is dollars, or at least in our case, in the U S if you want to make a change, you can always update that currency
symbol to reflect your currency. And if you want to drill in on a greater
precision, you can do that as well. I'm going to go ahead and save this
up. Let's say service one is 150. The next service is 500 and
the last service is $2,000. We have our different services built.
Perfect. Now, remember these two things, clients and services, our
data sets. So in the last one, we want to be able to invoice our clients. And you might think that
it's just one simple table. So let's start with that because this is
kind of a beginner mistake that people will think when they're getting set up, let's say you wanted to
create an invoice 1001, and you would just denote it like this. I'll delete my other records here and
I'll delete my extra fields as I have before so that we can start
setting up an invoice. Now, you would say that an invoice relates to
your client and your invoice relates to your service. So you would imagine that you'd probably
have a linked relationship. And this, my friends is the true power of air
table and a relational database, because I can actually link my
invoices table to my client's tape, just by selecting this option here. Then I get the choice of can an invoice
be assigned to more than one client. And generally the answer there is no
right. An invoice only goes to one client. So I'll toggle that off and I'll
create this field. And now I can say, look invoice 1001. I
can assign it a client. And I see the clients in my database, and this is why I have my two
rules for the primary field, make it as unique as possible and make
it something that identifies that record. Because if you use email
address, for example, as a way to name your clients, it would be hard for you maybe to pick
the right client in this dropdown. But we can see that we're going
to assign that invoice to Gareth, and then we need to link
to services. And again, this is not the right way to do it. And I'll show you the
right way in just a moment. But if you were to do it this way,
you're going to get to this point. And you would say, yes, an invoice
might include multiple services. So you would create this field. And
then you would say that an invoice, maybe on this one, we offered service
one and we sold three service twos. And right there, I just gave away why this isn't the right
way to do it because how in the world, can you say how many service ones and
how many service twos you sold here? Because if I add a quantity field
here, yes, I can add a number field, but it's going to relate to the
entire invoice. If I put a three here, that means I sold, what three,
what? Three twos, three ones, three whole invoices. It's not
really the right way to do it. So if you're creating invoices
and if you're new to air table, the best skill that you can focus
on is what is called a junction table. So we're going to build a junction or
an intersection between services and invoices that breaks this down at a
higher level of granularity before it rolls into the invoice. So let's roll up our sleeves and
jump into our invoice detail. I'm going to create a
new table invoice detail. I'm going to start by deleting all
my extra fields as I've done before. And invoice detail is
going to link to a service. Here's my linked relationship
to my services table. But each service is only going
to go in one time per detail. And then it's also to link to my
invoice table. Now, bear with me. This might seem a little weird, but I
promise there's a method to the madness. Again, I turned off multiple
records and I create the field. Now I want the invoice to proceed the
service so I can move it around just by clicking and dragging my
column headers around. Now, let's go into invoice 1001,
gonna assign it here and again, here. Now each of these different
records represent its own line item. So inside of my service
field, I can then say, well, they bought service one and
a here, they bought service. Two rolling into here. Now I
can build a quantity field type. I'll use the number. And in
our example, we said, yes, they bought one service one and they
bought, I think, three service twos, right? So now we have service one in the
quantity of one assigned to the invoice and service two in a quantity of
three, assigned to the invoice. I'm going to give this a name, remember
my rules for the primary field. I'm going to call this invoice detail ID. And I will follow my own rules again, where I like to keep it
as unique as possible, but also make it something that
identifies what lives here. So I'm going to use an
invoice and the service in my concatenate field. And so I
can see this was invoice 1001, where they bought service one,
let's delete that extra one here. And I will group my view by this field. This folks again is a junction
table and it is a slightly advanced part, but critical for anyone
learning how to build an air table. Now let's roll back to our
invoice. If you recall, we already linked to the client, but we also linked invoices to
services and that was wrong. We shouldn't have done that because
we now have the detail page. So I'm going to delete my services link, but you'll notice that in doing so, air
table warms us. It says, Hey, hold on. You've got a linked relationship. And when you build a link from
services to invoice, well, invoice also links to services.
So if you delete one of those, you're gonna have to go and delete the
other one too. So be aware of that, but say yes, delete the field. I also don't need my quantity anymore
because I have quantity on my detail page. So I'm going to delete
this. Okay. So far so good. Let's flip into our services now
and you see that I have this single line text where I used
to link to my invoices. This is the other part
of that relationship. We have to go and delete the
relationship from both sides. So let's delete it here
as well. All right. So now going back into our invoices, I can now see all this information.
I have the proper structure in place, but how do I get a total
in my invoice? Sure, sure. I can see on my detail how
many they bought of that thing, but I have no idea how much I
should charge the client yet. Well, let's do the math and this is where
we're going to use look-ups and roll up field types. This is a way that we can look through
the different relationships that we've already built in our air table database
and pull data from one place in our database and get it into another.
So let's get into it first off, look up the price, the price of a service
lives at the service level, going back to services it's
right here, the cost of service. So how do we get it in there? Well,
we look it up cost of service, and we're looking at the
linked relationship that
comes from the service that we've connected to in our invoice detail. So the field type we use here is cold a
lookup field because we want to look up that information. And here,
we're going to say, well, we've linked to the invoice and
we've linked to the service. Which one do we want to look
at for this lookup field? Well, it's the service table, the service relationship that we
want to take advantage of here, and then inside the service table, what is the name of the field we want
to look up that is cost of service. So flipping into formatting, it already
knows that that's a dollar. And so, yes, it's going to keep the same formatting
here and we're going to create that field. And there is the cost
of those different services. If I change the service
here, it goes away. And if I make a different selection, it will show up with whatever
the cost of that service is. So that information is properly linked. Now we can create or
calculate the total for the detail. And so we'll use a formula here
and this is pretty straightforward. We have a quantity field and
we have a cost of service. So let's multiply those two things
together to come up with the cost for that particular line item. In this case, I simply start typing the word quantity
because that's the name of my quantity field. And I select that and I use an
asterisk for multiplication. And then I go in with cost of service
pro tip you'll notice that cost of service here is encased
with squiggly brackets. And the reason for that is there are
spaces in the name of this field. So in the case of quantity,
there is no space. I don't need those quickly brackets.
In the case of cost of service, there are spaces there. So yes, it has to be sandwiched
inside of squiggly brackets. So I'll go ahead and create this
field. The thing that it misses though, is that this should be a dollar amount
because I multiplied quantity a number times, cost of service,
uh, currency. And so, yes, I want this to also be a currency
dropping back into my field here. I can go into formatting and
bring in currency and save it up. So now I know the total of these services
because I offered three of this at 500 a pop, and I've got that here, but I still don't know
the total for my invoice. And this is where we are going
to use a roll up field type. So let's go into our invoices now
and we'll bring in invoice, total, a new field. And I use
the roll up field type. Now our roll up is very similar to
a lookup except a look up comes in and it brings something back
without manipulating it in any way. A roll-up is going to allow us to look
at something through a layer of depth in our database, but before
it brings it back, it allows us to do some magic to it, perform some sort of aggregation
function. So again, we have to tell it, what relationship are we
looking at in this case, our data lives in the
invoice detail table. So I make that selection
here, invoice detail. Now the field that I want to access is
the total amount for that line item that we just created. So I can look at my fields and I have
my total for detail that I created. Now, you'll remember in this
particular invoice case, I have two different totals, one for
service, one, which was around 150 bucks. And the second one for service
two, which was around 1500 bucks. So I need to sum those two
line item details together. And that's where my aggregation
formula comes into play. So down here, I can actually scroll down and look at
the different options and some values as the one I'm looking for
now, before we get to it, let's make sure we are formatted
and currency, which we are. I go ahead and create this
field. And there we go. My $1,500 line item, plus my $150 line item
is now rolled up here. So I know that this is a lot,
and obviously we went fast, but there's a lot for you to
catch up on, on air table in 2022. These are the fundamental building blocks. I hope you check out the upcoming
videos where we're going to go into more detail on some of the more advanced
features I look forward to connecting with you, then make sure you
subscribe to this channel. So you don't miss out on those. 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. So swing on by, and I look forward
to connecting with you soon.
