Create and Manage Your Airtable Invoice for Accounting Seamlessly

Airtable invoice for Accounting

One of the most popular business use cases for no code tools is invoicing. Being able to track all of your finances for a small to medium business have that all in one place is incredibly valuable. Well, that's exactly what we're going to be covering. In this video, I'm going to show you how to get set up building your own invoices from within air table. So if that's of interest, stick around, and let's get into it. Hey, welcome back to the channel. If you're new here, my name is Gareth. I'm the owner of gap consulting. And it's our mission to help you get organized and automated with no code tools, air tables, one of our favorite no code tools. And that's exactly what we're going to be discussing in this video, specifically how to use air table to put together some invoices. But before we get into the heart of the video, I want to invite you to join me for some free training if you're new to air table, or if you've been using it for a while and you just want to make sure that you understand its full functionality so that you left no stone unturned. Well, then I want you to join me for my air table Crash Course, this crash course is going to walk you through all the key features of the tool so that you make sure that you're getting everything you can out of it. Grab that crash course at https://.garethpronovost.com/airtable-crash-course. I will include that link below this video. But without further ado, let's hop on into the heart of things. We've got to talk about how to set up your database so that you can support invoices, we're going to build this from scratch. So we can start off and just come right from our air table dashboard. In the bottom left corner of our screen, we say create base, and we choose where we want this to go. This is going to be what workspace or folder this database is going to be stored in. In this case, I'll keep it here in my gap consulting workspace. So now that I've selected that, we're just going to say Create, and this is going to then start that original version. And of course, because we're building this from scratch, we're going to have to go through and put all the key elements in play. But the good news is invoices are relatively straightforward, and similar across all of the different types of nuance that you might have for your particular industry. So let's go into the general structure that will apply to invoices, it's pretty straightforward. First thing we want to do is just close down this side panel, and we're going to drop on into our first table. Now when we create an invoice, it's always sent to someone an entity. Now sometimes we might break this into two different tables, we might have contacts who relate to companies, for example, you might be doing business with company ABC, and you might have multiple points of contact at that company, but you only send invoices to one particular person. So if that's the structure of your business, you'll want to break these two things out to have companies and contacts. But it might not be the case, maybe you work with more of a b2c model business to consumer, in which case you will have just one table and it will be the person the invoice is going to for our example, I'm going to build the more robust option here, which includes both the people as well as the companies they work for. So let's check that out. So first, we're going to create our initial table for contacts. Now contacts are fairly straightforward, I am first going to get rid of all these excess fields, I don't need these fields at start, when we just create a new table from scratch, delete those by right clicking on the field and then come down to delete. But now inside of this, I want to set up the actual name of the person. So it's pretty typical that we'll collect a first name. And this will be a single line text field. We like single line text here because of course, it's just one line of text. So make that selection. And then similarly, we'll have a last name field. And we can copy this first name by holding down alt, grabbing it with the left mouse click and then releasing it. And it's going to keep the properties of the first one, and we'll just have to rename it. So this will be last name. So I've got my name field. And so if I were a person in this database, I would show up like this, Gareth Pronovost. And of course, we're going to want our primary field to concatenate this data. So we're going to take the first name, space, and then the last name, we can use a formula for this. And so this will be the full name field. And as I mentioned, a formula is what we will select here. And we'll just concatenate or string together multiple things, we will take that first name field, then a comma, followed by quotes and a space in between those quotes, which is telling air table that we need to include a space here, another comma and then we'll reference that last name field. And when we're all done, close out our parentheses and thereby complete our formula. So now I've got first name, space, last name, and we're going to see that automatically fills out the full name of the person based on what exists here. Now, other things that you'll probably want to collect some typically four contacts will be an email address. And you may opt to also grab a phone number. So if you so chose, you could add that element here as well. Note that these elements that I'm adding are specific to that contact. So this would be the contacts, particular email address. And of course, the other field would be the contact phone number. Now that we have this more or less figured out, we can link this to the company entity. So quick and easy way to do this is to just add another field and call it company. And here I will say link to another record as my field type. And now I don't already have this table created. So I will say I want to create this new table. And here again, I will make sure to name the new table companies or company. So I'll go ahead and drop that in here. And I will say that I do not want to allow a contact to link to multiple companies. Now the reason for this is because generally a contact will be associated with one company. When we create this field, though, we do need to go to the other side of the equation drop into companies, which is now the new table that's been added. And we'll look at this side of the linked relationship. And we want to make sure that this is toggled to multiple records, because as we already mentioned, it's very likely that we have multiple people that are associated with the same company in this example. Now for our particular use case, we want to link multiple people to the company. But generally we only want to send an invoice to one person, especially if the company has an accounts payable team, it's very common that we will need to include them and only them on the actual invoice. And of course, we might be working with somebody else within the company. So when we have this link to contacts here, this is just linking this company, let's say for example, gap consulting was here. And we would say okay, Gareth is one of the contacts here. And we could build that link. But we want to also make sure that we have a separate linked relationship for billing contact. And this particular link, again, is going to link to that contacts table. But here's where we can choose, maybe we only want to have one billing contact, and thereby make sure that we don't have multiple links here. That way, when we send out the invoice, we know it's only going to the billing contact. So we can go and create this field just like that. Now, to see this in full blown motion. Let's go back over here. And I'm going to add some more people here that might work at gap. So maybe I have someone named Adam and somebody named Mark. Well imagine that we have them here, all their information filled out last name, email phone number, notice that I have two linked relationships to company, the contact is linking to company, that's the first one we built. And this is where we have the billing link. So I might say this is the billing contact link to company. So in this way, I have two links to the exact same table. But the links represent different things, I only allow on the company side, one person to be listed as the billing contact, in this case, that might be Gareth. And over here, I might have multiple links to people that are all associated with the company. So in this way, I've demonstrated that we have three different contacts that we have that represent this company, but only one of them is the billing contact that's actually going to get this invoice. It's important, again to break these two different things out, especially for invoicing to make sure that we're not sending this to the wrong team. All right, so we have our contacts, and we have our companies. Now it's time to get a little bit more granular in terms of how we're going to actually build that invoice. And you might think it's just a simple single table here that we need one for invoices. But in fact, we need to build two more tables, one table for the line item detail on those invoices, and then another table for the actual invoice roll up that combines all of those different line item details. So let's get into it. First, I'm going to come up and I can add a new table here and say create blank table. And this will be line items. And let's go ahead and save that up. And again, I'm going to delete all those excess fields that start when we create a new table. And here we just basically want to map out the different line item elements that are going into whatever we are charging for now in some cases, you might also have another table that is going to hold all your different services. And so in this case, you could actually link to that specific service. In fact, let's walk through that example as well. I will add services here and again, choose link to another record. Again, this table doesn't yet exist. So I will create a new table. And in this case, a line item can only link to one service because a service is going to be specific on every line item. And so I'm going to turn off my link to multiple records, allowing a new line item to only connect to one service and create the field subsequently also creating my new table which I failed to name I should have named it as services. So let's make that quick estimate and map out some services. Maybe I have service one, and service two, and service three, whatever these things are. These could also be products, of course. So now I've mapped out some different services, but I need more information about them. One of the most common things you'll include is the amount that you charge for that particular thing. So I would bring in here the cost of service. And this will most likely be a currency field type, where we can just key in how much we charge for this thing, maybe service one is a $1,000, element service two is $2,000.03 $1,000. For service three, this is going to just keep the numbers easier to understand. So I've got my services built out, and those linked to my line items. Now I need to build yet one more table, which is the invoice which rolls up all of those line items that we've already established. So let's create one final table here for our invoice structure. From a blank table, I will say invoice, and again, remove the excess fields that I don't need here, go ahead and delete all these fields that start by default. Now, in our case, what am I going to need Well, a link from invoices now to align items. So I will establish that right here. And in this case, I've already built that table. So I can say link to another record and choose line items from the list. Now here I've got multiple record linking turned on because when I present an invoice, it is going to include multiple line items in that invoice, we might have sold multiple services that are getting invoiced all at once. So of course, we want to be able to highlight all of those different invoice items. Now that we're good here, let's actually build some stuff out and put together an invoice and make sure that it makes sense. I'm going to flip back over to line items. And of course, we see that it's currently linking to both services and invoice, no problem here, I'm going to select a service service one, and I'm going to select another service service two. So assuming that both of these services were sold on the same invoice, let's now flip over to invoice and create our first invoice. Maybe this first field should be the invoice ID. And so I can present the invoice ID let's call this 1001. I also need to choose what company this invoice is going to so I better build a link to my company. Of course, I already have my company tables. So I can establish my link to the company table. And of course, I'm only going to bring in one company per invoice, I'm not sending one invoice to multiple companies. So let's toggle this off. Now we are ready to create that field. So of course, I only have the one company in my example gap consulting. But who am I sending the invoice to adapt consulting? Well, if you recall, we can flip back over to company, I have a billing contact here that I want to look up. Moreover, I want to look up the billing contact email as well. In order to bring this into the invoice, I first need to bring it from the contacts table into the company table, the email lives at the contact level. And I need to pass it through every level of the database. First, the contact links to company so I better look up the billing email, use the lookup field here and choose the configuration from our contacts. So I'm looking at my Contact link. But I'm looking at my billing Contact link. Remember contacts links to three different people or more. And I want specifically who we are going to be sending the invoice to look at that link and bring in that email address. Let's create that field. So here we are, I'm only bringing in the email address for the person who's listed here is the billing contact. And I can now bring this element into the invoice. So now back on invoice because it lives in the company now I can look up billing email. Again, lookup field here is going to be perfect. Include this, I'm looking at the company relationship now. And I want to bring in that billing email. So we're all set here. And I can also bring in similarly, the billing contact again, I'm going to hold down alt and copy this field over and I'm going to rename this to billing contact. Remember when I copy a field, it's creating the same properties. So I don't have to go back in and use another lookup field, it already knows that but I better remap this now to the billing contact and save. So here we go, I can see that this company, all I have to do is link to the company to bring in the billing contact and the email of that person. But I'm not linked up to line items yet. Let's get back to work in the Line Items table. I've got my two surfaces that are both going to be sold on this same invoice invoice 1001. So now I've linked this up appropriately. But I need to name this line item. So I gotta go to this line item here and I'm going to call this the line item ID. And I'm going to use a quick formula here. And we're just going to run again a concatenate where I just want to see the invoice number and I'm in Again, spaced this out with maybe a dash, and then bring in the service. This is just providing a nice identifier in the primary field, the leftmost field for each of my line item elements. So I can see that this particular line item element is for invoice 1001, where we sold service one, we're not quite done, though, we still need to bring in the cost of these items. So I can look this up because it lives on the services table. Let's go ahead and bring it in, this is going to be the cost. And I can use again a lookup field. And let's look at the linked relationship we have to services and include the cost of services. Now whenever we're putting together an invoice we always want to with our line items also include quantities. So I may have a quantity field here. And this will be a simple number where I can key in how many of these things we sold, maybe we sold only one service one, but two service twos. Right now I need my subtotal for the line. And this is a simple formula as well, I'm just going to take the cost, multiply it by the quantity, and make sure that my formatting is in currency. Once I'm all set, we'll create the field. And we can therefore see that for this particular line item, we sold one thing at $1,000. That makes sense, we sold two things 2000 Each total of 4000. All of this information now is going to roll into the total for the invoice. Final step, follow along back to the invoice table, I'm going to roll in the total of the invoice by summing all of the services that we have linked here, I can use a roll up field type, I'm going to choose my link to line items. And again, I'm going to drill into my subtotal this time, and I'm going to perform a summation. Remember that we have one subtotal of $1,000, and a second subtotal of $4,000. Obviously, this invoice should show us a total of 5000, which once we go ahead and verify the formatting as currency, create this field and verify that our total is showing up appropriately. I know we went fast here, but there's a lot to unpack in terms of how to set up your invoice inside of a database. Once you master this invoice structure. Now it's time to go on to more advanced things like automatically creating these invoices or maybe using the Page Designer inside of air table or maybe even integrating with third party tools like QuickBooks or stripe to automatically bill your clients. There's a lot of potential here that we're going to cover in future videos. So make sure you're subscribed to the channel if you want to keep up to date on that. And in the meantime, keep on building but that's it. People at the How am I on time real quick time test? Who I'm not okay.