Streamline Your Workflow with a Google Drive Invoice for Management

Google drive invoice for Management

If you're ready to automate the creation of your invoices so that with a simple push of a button, you can automatically create your invoice document inside of Google Drive from your Airtable database. Well look no further in this video, I'm going to be breaking down this precise use case. 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 here at GAP Consulting. And it's our mission to help you get organized and automated with no code tools. Automation is one of the most powerful things that you can learn in today's job market because it allows you to automate those repetitive tasks that you do day in and day out. Do it all in the background so that you can focus on big picture, work and drive results. If you want to learn the key fundamentals of automation. Before we get into the video, I want to first invite you to join me for a free training, you can get that training at https://.garethpronovost.com/webinar-registration, I will be sure to include links wherever you found this video. But without further ado, let's hop into the heart of things. And we've got to talk about how you can set up an automation so that with just the push of a button, you can automatically create this PDF, this form this document inside of Google Drive. And we're going to drive all of this no pun intended from the push of a button inside of airtable. So let's get to work. First, you have to of course have the proper data schema for your database. Now we've gone into detail on this very topic in previous videos inside of this playlist. So if you're just joining us for the first time, be sure to check out the other videos associated with this one. So you understand this basic data schema. At a high level, we have contacts, these are the people that we work with, we have companies, these are the organizations, we have a line item detail because people buy multiple things from us, and we have our services that we offer. And then lastly, we have the invoice table. So these five tables are generally what you're going to see for an invoice. But the one that really makes all the magic happen is that final one is the invoice table. Let's quickly go into how we build out an invoice. And then we can get set up with the automation. So now that we have all the tables established to create a new invoice, we can just add a new record here and Airtable 1003 will be this one. And I can assign some line items. Well, I don't have line items yet for this invoice because it's a new invoice. So I have to create them, I'll create a new line item. And I have to choose a service for each line item. So maybe I sold service three. And then I have to of course say how many of this I sold. So maybe there were two service threes. So service three carries a cost of $3,000, I have two of them, the math is done in the background automatically knowing that this line item is going to have a $6,000 price tag. Alright, so here we are. Now I have this one line item, I can add multiple line items to my invoice. But let's say this is all we sold was to service three's whatever that is, and we're ready for this invoice. Well, of course, I have to assign a company. So who is this invoice is going to be sent to in this case, I will send it to myself. So I assigned the company. And then of course, we have one person assigned as the billing contact. And then we are able to know who that billing contacts email address is. And so that is all looked up based on the company that we assigned for the invoice. Here's my total. And of course, I also need to add a date submitted. So let's say we submitted that today. And a date due maybe we're on 30 day terms here. So this would be June 23. Given that we have submitted it on May 23. So that's it for the creation of the invoice we added everything we need. Now what we need to do is have an automation run right, so we can do it with the push of a button. Let's insert a new field here. I'll call this my Invoice button. And I will choose the button field type. Now what I want to do is actually run an automation when this happens. And so if you look down here, I don't have that exact option quite listed out here just on this particular scenario. So we have a couple of options. One thing we could do is have this open out in Page Designer, but it would not have the automation component that we're really looking for. So instead what I want to do, instead of creating the button here, inside of the data schema, I have to first create an invoice interface. So let's create an interface. And we will go ahead and click up here for interface and we will say start building. Now we can name this interface, whatever we'd like and choose a color and an icon and we will say next so from here we can choose come one of the templates are pre loaded templates. And for our purposes, I think that the record review will be perfectly sufficient. We'll go with record review where we pick an invoice on the side and we will go ahead and create that invoice based on whatever automations we're going to establish, we want to look at that invoice table. And we could apply a filter if you'd like. In our case, I don't need to do this and I'll move on to the next step. Now this is where we want to include all the data that will go into the invoice. And as you just saw me do manually where I had to add this information step by step to that invoice, well, we want to make sure that we include those components on our interface. So the invoice ID matters, the line items matter, the company matters, we want to look up the billing contact, we want to look up the billing email, we want to look up the total, we won't have access to the date submitted and the date due. Now the date paid is something that doesn't matter when we're creating the invoice and it would be nice to see the invoice itself the actual attachment that we've created for the invoice. So now that we've toggled pretty much all of this on, let's go ahead to the next stage in our interface. Now let's name this Create Invoice. And I'll click Finish. Now from this next page, I can kind of sort things out. So how do I want to work with this? Well, first and foremost, we're going to be creating this invoice and uploading it or sharing it back here. So I don't think I actually want to look at this on this particular interface. So I can just delete this part, right, I do want to be able to work with my line items. But of course, I also want to make sure that people can edit that information, they need to be able to add new line items here. And in order to do this, I click on the permissions and make it editable inside of the interface. Now I also want to make sure that people can open up the record details, that means they can open up the line item record itself and edit stuff inside of that. So when they click on that, they can go in here and change the quantity they can change the service etc. This is definitely important, as you saw on my example from earlier. So I'm happy with this, we can go ahead and move back. Now I'm gonna go into the date do also make this editable, the date submitted make this editable. Otherwise, what's the point of having this interface, people need to be able to change this information when they are creating and submitting it right. I also want to resize a lot of this stuff. So let me go ahead and do that here, I will move this component into the upper component here of the interface along with the building contact, move that up, I can move the line items down. Because these pieces are kind of associated together, right, the due date, the date submitted and the line items, these pieces are similarly related. And then the contact information the company, these pieces are also similarly related. Of course, I need to make sure the company is also editable, just as I did in my previous example where I assigned the company, if I don't have the capability of doing that, then we don't know who to send the invoice to. Of course, the total here is not editable, that will not be changeable. This total will be driven by the services themselves. Now the big piece that's missing is having a button that does all the magic. So in order to add a button to our interface, we have to go down to add element. And we can find all elements on the left hand side here. And we can search for button. And here it is I can put the button anywhere I want in the interface. I'll put it right up here at the top. And I also want to make sure that this header right here, this is the invoice number itself, this needs to be brought down into the actual interface so that I can edit that as well. So if it's up there in the title, I can't actually edit this. So I'm going to bring it down. And in fact, I'll bring down our buttons as well, and just move it right next to the invoice ID. Again, on the invoice Id make sure it's editable. And now we're going to get to the magic. Finally, let's go ahead and get to that button. And here we get to choose what happens if the button is pushed update record as the default action. But what I want to do is run an automation, this is where we're going to do cool stuff. And so the source, of course, is whatever invoice is here. Now, I can't choose an automation yet, because I don't have an automation built. So now that we've established the interface, and we've created the button on set interface, now I can go a layer deeper and create the automation. So I'm going to name this automation is going to be called create Google Doc. And let's go ahead and continue in automations. This is going to open up the automations component of Airtable. And you see that my triggers already been built out here when a button is clicked in my interface. So now that I have that capability. Now I can move on to the next step, which is going to be Google Drive. So if I search in my integrations here, I will find Google Docs, I will create a Google Doc and I will go ahead and select a Google Docs account. Now I already have mine set up but if you haven't set yours up, go ahead and connect your Google Docs account and or manage your connected accounts. I'll choose my account here and I select from Google Drive what that account is If you have a folder that you want all this to go into, you can do it there. And for our example, I will select this folder called invoice example. So I'll select here, and we're good to go. Now if you don't already have a folder setup, just pop into your Google Docs and create a folder for these documents to be saved inside. Now we have to establish the title of the document. And so generally speaking, we want to bring in dynamic information that is going to be unique to this particular invoice. So for example, the invoice ID is going to be a critical component here, I might also want to include the company name. So all of this is data that's available to me based on the record that triggered the automation. So the invoice ID is going to be different every time because every time we generate an invoice, it will be a different invoice. All right, now what content are we creating? Now when it comes to formatting this we're going to drop on in and just create an invoice. So I'll say something like invoice ID. And I can again play with static and dynamic data, static data is going to be the same every time. Dynamic data changes every time. So I'll bring in the invoice ID and I'll say something like payable by and here I can bring in the company information. So I'll look up that company again. And I can include the contact of that company. Remember, that was a lookup field that we had. And I can bring in the linked record here and bring in the name. So I'm bringing in the contact name, the billing contact name that's associated with this company. And I can also include the email address, for example. So let's go and bring that in here as well. And here's my billing email, just grab that value, that should be good. Now as a next step, I want to bring in the line item detail. But if you look at my dynamic option here, you'll notice that if I look at line items, I can look at my different linked relationships, the line item here, but I don't get a lot of opportunities in terms of how I want this to show up right here. So a quick little hack for us is to actually look up the line items and then to use them in some list output. So that sounds a little complicated. Let's go through that step by step, I'm going to remove this line item that I just added here. And let's add another action step here. We're going to go and we're going to find record, we want to look up detailed information about the line items that are assigned to this invoice, let's go step by step, we're going to look inside of line items, we are going to filter or find records based on the condition the condition we want is to establish that the invoice here the linked relationship from the line item to the invoice is exactly find it there. And now we need to switch over to dynamic data. And we're going to say that it is the Airtable record ID that triggered the automation. Now, this is confusing because Airtable is telling us that this doesn't make sense. This doesn't work. But let's go step by step here. When a button is clicked, let's choose a record. Let's choose this. One is the invoice we're using. Let's go to find records now and see if that makes sense. Can I test this action, it tells me it's invalid. However, if I test the action, it's going to bring back line item detail for the line items associated with the invoice 1001. So even though it's telling us that this is broken, you see that it in fact works. But now let's reorder this, we want to find those records before we create our Google Doc. Right. So now we can go into our Google Doc, we have our invoice ID we have our company, etc. Now we can come in and add additional components that we didn't have before, because we have the find record scenario that we have here. And we can go scroll on down here and see that we have a list of all of this information that came from the line item detail that we found. So we can go into the field values here. And we can drill in and we can include things like the quantity sold the subtotal, etc. Let's back up though. And at this point, we're going to say we want to bring this in as a grid, we will insert all the records that we found as a grid. And now we can choose what pieces we want to include here. So I can include things like the service that was sold, the cost of the service, the quantity, and then the subtotal. Once we're happy with what our selections here, we can also opt to include a link to the record or not. Now for our purposes, we're sharing an invoice with a third party that doesn't have access to our Airtable. So I will not include a link to the record and I will insert that component. It comes in as a grid of records. Now let's test the action. Let's make sure that this thing is working. Now of course we haven't included everything that we would include on an invoice. Right now we just have the invoice ID the payable, the billing contact, and then the sub line items, right. But did it work? Well we tested the action and we got this URL right Here, this is the file that we created. Let's highlight that copy, open up a new window and paste. So while that loads, this is what we just created, invoice ID 1001 payable by gap consulting comma Gareth Pronovost. Here's the email address, you'll notice that this formatting could be improved. But ultimately, we got the line item breakdown of those elements. Now, what do we want to do with this? Well as an final output, what we can do is take that URL, because that's what was just created here, right, we created this Google Doc, well, we can take that URL and put it back in our database. So let's go back into our data schema here and add a new field. This is my invoice URL, and it will be a URL field type. I'll click here, create the field. And now going back into automations. As a final step, I will add my update record. So remember, the thing that started this was an invoice started by pushing a button in interfaces. So now I'm going to update that invoice. So I select my invoice table, what record Am I updating? Well, it's the record that pushed the button in the first place right there. And what am I updating, I am updating my new field invoice URL, let's drop it in. This is going to be the URL that was created in that previous step. And there we go. So let's generate a preview in our example, it would have updated this field right here with the invoice URL, it's exactly what we want. When we're happy with this, we will turn on our automation and take it out for a test spin. Before we do we've got to go back to interfaces, though, go into our interface here. And I need to bring in that new piece that we just added the invoice URL. There it is. And once I'm happy with these changes, I will publish. All right, I will publish my changes one more time. There it is, I won't share it just yet. Now let's test this thing out. Here I am, I have invoice 1002, we see that it's going to testico. If I want to add another service I can. So here I can create a new item. I can say if they bought a new service, they bought service three from us. Let's go ahead and look at this. So I've got this one right here, they sold to this one right here sold three, this one right here sold zero, of course I haven't made this editable. So I would have to go back in to my interface app settings and allow myself to edit this quantity. In this example, though, I'll just exit out, I'll remove it so that we can move on. So now I've got my total here of 13,000. What I want to have happen, I want to push this button and have the thing go. So here we go. Trigger automation. Automation is working in the background. Google is having this Google Doc created. And just like that it was created. We now have that URL here. I can open it up. And as a final step in my automation, I could even share this with a client. Now of course this isn't fully baked, I would encourage you to make this a little prettier, spend a little more time formatting this but the general components are here on invoice created from the push of a button. I hope you got a ton of value out of this. If you did, please leave comments below. Make sure you've liked and subscribed and of course in the meantime, keep on building