Streamline Your Billing with Contractor Invoice Template Google Docs for Supervision

Contractor invoice template google docs for Supervision

Do you want to create a Google Doc from data that lives inside of your Airtable base? Well, in the past, we've showcased how to do this with a large amount of data multiple records. But in this video, I want to drill into how you can do this on one specific record, create some sort of a templated version, maybe an invoice, for example. 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'm the owner of GAP Consulting, where we're going to help you save up to 20 hours of your time every week with no code tools and automation. If that's of interest, definitely check out our fundamental training for automation. In this training, we teach the building blocks so that you can get started with leveraging the power of automation to help you save time and eliminate those repetitive tasks that you do over and over again, if that's of interest to check out that free training at garethpronovost.com/webinar-registration. links will be below this video. But without further ado, let's hop on into our topic, we are talking about building a document inside of Google Docs from data that lives inside of air table. And as I said in the beginning, we're going to be focusing predominantly on the data that just lives in one specific record. Before we jump into this automation, we have to start by understanding the data structure of our database, we're going to be looking at an invoice example. And as you may already know, when you're working with invoices in air table, you have a minimum of generally three tables. So I've built those tables. Here, I have the invoice itself, the invoice is where we're going to store the customer, the email, but we're also connecting to those line items. Now important to note is that we're also able to see what the invoice total is. And this will become clear in a minute if you don't know where this comes from already. But we're using a roll up field that's looking at our invoice line items table. So on line items, you see here, I've got three different things that we sold on this same invoice to make this easier to see we can group by this field here. So you see that we have linked to three different services. And we have a quantity that's unique for that specific service. So in this case, 10 hours of hourly consulting, one custom project for online courses, and we're able to then bring in the price per unit, that's information that's going to live on the service table, we'll see this in just a moment, we're also then able to compute the line item total. So a simple formula that's looking at the quantity multiplied by the price per unit. Now something I want to point out here before moving on is that this particular field is formatted using this format that is in currency format. However, air table still thinks of this as just a number. And this is important, because if we want to actually pass dollars and cents on, then we need to actually format that number into a text string. So I've gone ahead and done that here, both for the price per unit in currency, and also the line item total, you know that these are actually strings and not numbers, because you'll notice that they have justification left inside of these fields. That's just a little pro tip for you if you're ever curious if you're looking at a string or a number. By contrast, you'll notice that these numbers, these currency formats here their justification, right. So air table thinks of these as numbers, and it thinks of these as a string. And it's a lot easier to pass a string because its formatting will be contained. Whereas the formatting of a number, in this case, the currency formatting of these prices here, that's not going to be maintained when we create our document. If you're curious about that formula, you can go ahead and pause the video and zoom in and capture what I've done here. This is a fairly complex formula. And it's not the point of this video. So I want to move on past this topic. But if you need it, it's here for you. Now moving on to our final table, we're going to take a look at services services is really straightforward. It is just the name of the service, obviously a link to the invoice line item and then we have a price of that service. So when we're ready to build an invoice, we are going to do it from our invoices table. Ideally, we would like to just click a button hit a checkbox or something along those lines, that's then going to trigger our automation. In this example, I'm going to use this checkbox where I simply check the box Create Invoice that would ideally then initiate my automation to build this document in Google Docs. So let's flip into our automations now and take a look at how we can set this automation up. The first part is going to be the trigger and I really like to use the record matches conditions trigger you'll notice that yes, I have included the condition that the Create Invoice is checked. However, I also want to make sure that this automation doesn't run without the information that it requires. So it's going to need a customer name. It's going to need a customer email. And of course we need to actually have a total for our invoice. So these are all parts of my condition that's going to set my trigger up for success. Now once my trigger is in place, my next step is To find the records that are connected to this invoice, the reason for this is in our example, we're going to need to go a layer deeper and look at the invoice line items so that we can include them on the invoice. So in this case, I'm saying look at the invoice line items table and find me any records where the invoice that is where it's connected to an invoice matches the invoice that just triggered this automation. So I've plugged in the record ID that triggered the automation here. What this does, if we were to just give this a quick test, it's going to bring back for us all of the different records that are on that line item table that have this invoice, this is going to be important for us so that we can break down the line item detail on our invoice. Now for our next step, we're going to actually build out and create that Google Doc. But before we actually do this here, I would suggest that first you create just an untitled document as a template if what you want to create. So flipping into my Google Doc, here, you see that I've created an example of what an invoice might look like, I want to include the invoice number up here, I want to include the customer information, the customer email, of course, it's always going to come from me who's creating this invoice. So this is not a variable here, then I want to list out my services, what did I sell, what's the price per unit, how many of those did I sell, and then what's the subtotal underneath all of the different services, we want to bring in the invoice total as well. And then maybe you've got some extra verbiage here that's going to close out your invoice. Thanks for your business, something along those lines. Now that I have this built out nicely as a template in my Google Doc, it's much easier to simply copy and paste this whole document into my airtable automation. So I simply copy this here, then flip back into my automation. And I can paste it into the content section here of the automation. Now you'll notice that anywhere where I have a variable, I've included these squiggly brackets, this is an easy way for me to make sure that I'm able to properly map all the right information from inside my automation itself. So here I have the invoice ID. And because it's in squiggly brackets, it's an indication to me that I have to fix it and replace it with the actual variable. So I will now delete the invoice ID and I hit the plus button here to open up the different records that We've accessed thus far in our automation. Now for this the invoice ID lives at the invoice level. And remember that that was the first step of our automation, where we triggered the automation from the invoice. So I can open up the first step. And if I scroll down just a bit, you see the invoice ID right here. So I can insert that here. Now I'm going to repeat this process for other variables, again, that live at the invoice level. So here I have a customer name variable, I can delete this again, hit the plus, go into that first step, scroll on down to find my customer. And plug it in here, I can also change the formatting. So I can highlight this and embolden that text just as it was done in my template. And that's going to come through on my document as well. Let's bring in the customer email here, delete that variable, hit the plus drill in, find the email and insert it here. That's almost it for the invoice because these services are actually things that we looked up in the previous step of our automation when we did the find records. So before moving on to that what I want to do is include that invoice total. Let's go ahead and highlight that variable here, delete, and plug in the invoice total. Now, as I mentioned earlier, we don't want to use the actual roll up field for the invoice. And you see here right where my mouse is that it is treating this as a number, it doesn't have that currency formatting, which I definitely want to include on my invoice. So instead, I want to choose the formula that I've implemented, that's maintaining that formatting so that it looks appropriate. That's my invoice total currency formula. So I'll insert that here. And again, just to reiterate, that's going to make sure that my currency formatting is maintained. Because again, it's a string, not an actual number to air table. Now before moving on, let's go ahead and just test this step right here. I haven't fixed up the services yet. But let's test the action. This is going to create a new Google Doc. So if I flip now into my Google Docs, I can now see my test document here that I just created. drill into that. And we are going to see most of this coming together pretty nicely. The only part that doesn't make sense yet is this little Services section. But if all you need to do is create a document from one single record, you can stop here because we've got that part nailed down. Now assuming that you want to actually create a full invoice, we do need to now bring in that line item information. So let's pop on back into our automation and we're going to go to this Services section here. First, I'm going to embolden this text just to make it pop a little bit more and I'm going to delete all of the line item details that I have right here as placeholders and in their place. I can come Up to that plus again. And rather than going to the invoice level, I'm gonna go to the invoice line item level, which is step two, where we found all of the line items connected for this particular invoice. And by hitting continue here and looking at records, I get a list of all the different things here, I can drill into either a grid or a list view, let's do list. So the first thing here is going to be the service itself. Then scrolling down, I need the price per unit currency, this is going to ensure that I get my currency formatting, also my line item total. And I also want my quantity. Once I'm happy with the selection of what's coming in, let's go ahead and insert this information here. And you see that just one little thing is added here. But it's going to list out all the records that we found. Let's give this one more test, test this action. And if we again, pop back now into our Google Docs, we see a new piece now created. Let's go ahead and drop in here and take a look at how that showed up for us. I don't particularly like the way it's listed out here because it's broken this information out chunk by chunk. But depending on your use case, you might want exactly this for your particular Google Doc. So it's nice to know that this is available. But I want to make a tweak to mind, I'm going to go back now to my automation, remove the list of records that I created. And instead, let's add that grid view of what we found. So I'll go again into records here. This is again, from my find step that I performed. When I found the records, I go to my grid connection here. And here's where I can toggle these things. Again, I'll say service, and I will go for price per unit line item. And again, find my quantity. You'll notice by scrolling all the way down that we can also include a link to the record, I don't want to do this imagining here that this is going for external purposes, they don't actually need a link to air table. So I'll remove that insert this section here. And we can test this action one final time flipping out into our Google Docs. And now we have yet another document created here that if we open up, we should see something more lined with what we're looking for. So there we have it, all of the information properly organized here, looking pretty sharp. As one final step for this automation, I would suggest bringing some of this information back into air table as well. So let's again flip back to our automation. And we will now update the invoice record inside of air table. So in order to get this setup, I simply say this is my action type update record. I'm going to update a record in my invoice table. And I have to tell it the record ID that we are using here, what invoice are we trying to update. And here's where I'll bring in the URL, the actual file that we created. Now, I originally tried adding this in as an attachment. And it didn't seem to work all that well. But let's give it a couple of tests here. Now one other thing I want to do is remove the checkbox on Create Invoice. This way I can create a new invoice if I need to. So instead of only bringing this file URL into the attachment field, I've also now included a new field for invoice link. This is a URL field type. And I'll try to do the same thing here. That is look at the document I just created and bring in that file URL. Let's go ahead and run this test and see what it does for us. Now that that's executed, we can run back into our air table base and take a look at this particular invoice, you'll notice that it's now unchecked, meaning I can check it again, which would retrigger the automation, I also have an attachment here. But if I open it up, it's not much of anything. So it doesn't look like this works in the attachment field type. However, you do see that I do have an invoice link here for the invoice I just created. So if I were to click on this, it will take me directly to that invoice, and I can access it here. So there it is, everything is working along every step. Let's test this whole process from the start with a new invoice. So let's first make sure that we flip our automation on, otherwise, it's not going to do much of anything for us. Now let's go back into our data structure. And let's add a new invoice here. I'll just imagine that it's going to the same person here. Now we can flip into our invoice line items. And we can start adding some new records here. So come on down to the bottom here and we'll add a new record. And we will bring this to invoice number two, which is here. Once we do that we now have a little section here because we've grouped by the invoice and we can say how many services they bought here. Maybe they got two custom projects. At $8,000 a pop we know that that'll bring the line item total to 16,000. And let's say they also bought some courses for us maybe our intermediate course they bought five of these. So you see all the math is being done for me on the back end. If I go back now to invoices, my total is updated here. This being the total of all of the line items that we've sold here and all I have to do is check Create Invoice, this will trigger the automation. And within just a moment, we saw that the information popped in here, I can now click on my invoice link and be taken directly to the invoice that was created for this new service. Everything is showing up properly here. So everything is working great. And this is how you build a document predominantly using data from just one record. But if you need to go a layer deeper, we've demonstrated that as well. I know we went fast and covered a lot in this video, but I hope you got a ton of value out of it and you start using this amazing tool because it saves a ton of time. Thanks a lot for watching. Be sure to subscribe to this channel if you want to make sure that you are on top of no code news, 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 and airtable 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.