Create Your Perfect Invoice Maker Template for R&D with Ease

Invoice maker template for R&D

I'm really excited to build an invoice generator with you today using air tables interfaces because in the past we could build our Junction tables and we could have all of our grids that we have with our information but it wasn't always the most intuitive experience but now with air table interfaces it feels so much more natural to be able to generate invoices hi I'm Dan Leman from automation helpers.com an air table implementation partner I always think it's fun to start with the end in mind so you can see what it is that we're building and then we'll go through the steps to create the voice generator together so I'm inside of my interface and this is what it looks like here I have two pages over on the left hand side but really we can do this mostly with the single invoice generator page we could have a list to be able to look at the invoices that we've created but on the invoice generator what we're going to do is we can press this plus button to create an invoice and now we're keeping it really simple we're just saying who is this invoice for what account so we'll choose their account we'll create the invoice and now this this is going to create the structure of the invoice for us so it's generated this new ah16 it's attached to the syncable account you can see that we have a bill to address that was dynamically populated because we have this pulling from the account that we linked it to and then if you notice as I was talking this actually populated this note to client as well as our due date information for us because we have an Automation in the background that's running to do this so here we could actually override our note to the client if we want but we don't have to so instead we can have the automation run it for us it's pulling in the first name of the billing contact so that we don't have an extra step but it's great if we do want to be able to override that later we've got our invoice and due date this is a calculation using payment terms so if we're running net 15 or net 30 in the background and then here's the part where we can add our invoice lines now this is the thing that I was talking about can get a little bit confusing sometimes if we're doing this outside of interfaces but look how s this is in interfaces so assuming we want to sell this person or this company multiple products we just click on add-on product and then from here we can choose from our list of products that we have so I'll choose blue widget we type in a quantity that we have so maybe I have 10 the price is already calculated that's pulling from our products automatically calculates the amount to $500 multiplying that and then we can see our total down at the bottom and this works if we add in additional products maybe I have the red widget that we're buying and we're going to purchase 20 of those and then again this is going to calculate those values update it and we can see our new Total at the bottom so this is all it is but there's really a lot that's happening in the background to make this happen and to get it so that it looks really nice on a page it looks like an invoice we don't have to go out and do something that feels really outside of the realm of creating that invoice okay so let's look high level at the data structure that we have to support this we've got an invoices t table as you could imagine because this is the core record that we're creating a new invoice each time and that invoice ties to different invoice lines invoice lines if you're familiar with that term Junction table is really like an intersection between the invoice and then we have multiple products so the table that stores the information between the invoice and the products is what we call the invoice line and this has information about the quantity how many we're choosing for that invoice of that product and then we're subtotaling our amounts here then we have our products and on our products we can have a status if we want to have active or inactive products but really the most important stuff here is that we have a price that we're signing to it and perhaps we want to have a unit of measure I'm not actually using this in the invoice but that's something that we could do as well next we have our accounts so as you can imagine we want to invoice a particular customer that we have that customers information is part of the account record such as their bill to address their payment term and things like that and then we have the contact record and right now I'm not storing a lot of information you could of course store much more information about this contact but all we're doing is we're recording a name to tie them back to the account so that we can pull in that information in those customer notes like we talked about so let's go ahead and create a new interface I'm going to call this invoice generator you might want to do the same as well click on next and we're going to start with a blank interface now if this is the first time that you're building an interface you might want to check out the other video that we published that gets more into the real basics of building an interface that might help answer some more your questions so we're going to move a little bit faster in this video from here we're going to start with this blank interface and what we want to do is start adding the fields that we need but the most important thing that we need to do is to choose our record picker here because this is what's going to show the appropriate invoice on the page so I'm going to click on this we've got invoices it's automatically showing our invoices table this is perfect now we don't really need a lot of information here but I'm going to move this over a little bit and then we're also going to add the account and we're just going to have this as a field and I'm going to drag it up here so that we can have our account information I don't really like this card view for what we're doing so I'm going to change this to a pill so that you can see here is our linked account now this is kind of optional because as you saw when we created that invoice we're choosing the account on that initial creation step so whether you want to have it show here is kind of up to you but it's handy to have that link sometimes so if you need to go see additional account information you've got a quick path to be able to get there on our invoices if I click this again it's fine that we are looking at all records here but I want users to be able to create new invoices so I'm going to toggle that on here let me close out of the adding the element so here is essentially the form the little model that pops up to create a new invoice record and this is the kind of information that I don't love this is kind of like the basics of ear table forms which isn't necessarily the best user experience so we're going to strip some of this information off the page we're going to take off the status and the invoice lines honestly we're turning off everything except for the account all we're saying is we're creating an invoice we're attaching it to an account and that is all that we need here so that will work for our purposes we'll close out of there next let's add our bill to information so I'm going to search for Bill two which is a Formula field that I created and we'll drop this on the page here I'm going to change this to one that has some filled out data here you go so you can see in the bill to we've got the name of the account and then we have the address that's showing here and because we have this as a default address I mean it's certainly possible if you just want to have Fields here and you want to type out the address each time if you want to override the address you can certainly do that I thought it would be easier if we just store the account address on fil so that we don't have to key it in each time so that's what I've done but you've got lots of flexibility when when it comes to tweaking this how you see fit so let me show you how we did this how we got to this formula field back in our data here on our account record I actually had all of the separate Fields I had a street a City a state postal code and what I did is I concatenated this information for city state and zip into this address line to and then over on my invoice record if I scroll over I have a lookup field and that lookup field is pulling from the account the linked account so we can see here's our account and here's our street and then I also had a look up and I said here's our address line too and then here I'm concatenating it all together now I could have done this back on the other record no Rhyme or Reason to this if I edit my formula here essentially all I'm saying is take the name of the account and then here's kind of the cool part if you put this back sln this is giving us the ability to have line breaks in our information so you can't really tell as you're looking at it right here but if we expand the record open and we scroll down you can see now that formula is actually rendering the name of the account and then the first address line and the second address line so this is really helpful especially in situations like this invoice where we want to see a nicely formatted address field next up we want to add our note to client this is a long text field and I'm just going to drag this next to the build to let's go ahead and change this because we want to have three Fields here so I'm going to drag that over make it a little bit smaller and then we'll have this note to client and this note to client again is something that we can manually enter information into let me click again make that that 1005 here's our note to our client here we've got our bill two and then we want to have our invoice dates so let me search for our date fields we have both our invoice date and this is an actual date field where we can manually key in that information and then we also have have our due date field of when they have to pay us by and this is a formula and we're choosing to render it like a date but we're not manually entering in the due date in this case now the next thing I want to do is I just want to add a little bit of stylistic difference so if I click here I can choose either the color from our invoice generator kind of this light purple or we'll just choose gray in this case and I think that makes it look a little bit nicer as we start to format that okay so let's talk about the invoice dates themselves we've got that one that we key in and we've got the due date now if I come into our Automation and we take a look at this I've got an automation that runs whenever we create that new invoice record so we're simply saying anytime we create that invoice we're going to update that invoice record itself we're plugging in the air table record ID and then we're saying hey thanks for your business and this we have as a series of lookups to pull the contact name from the linked account from the invoice so we've got a couple lookups away to be able to pull that information and that's populating that note record and then the invoice date we're just populating with the created date of when the record was created and that invoice date can be changed so maybe we're working on today but we're not sending the invoice out until Friday so we could let the user manually update that if we wanted to now comes the fun part this is where we get to add the invoice lines so we can actually start adding product so I'm going to choose from our invoice lines here and we're going to display this as a list on the page now right off the bat we don't see a whole lot of information here let me close out of here and I'm going to drag this up because that takes up a lot of room okay no products yet so we need to make a few adjustments here on the sides we are in fact going to let this be editable and we are going to allow users to add or delete records in line we don't want the users to open the record detail details I just think that adds a layer of complexity that we don't need if you have a lot of details you might want to be able to do that but we're just going to have everybody do this directly in line here so let's go ahead and try adding our product just to see what this looks like yeah and we can see that there's a bunch of extra fields that we don't need and in my opinion anytime we have extra Fields it just adds complexity and we want to keep this simple so let's go down to our fields we don't need our name to show I'm really glad air table is not forcing you to have your our primary field showing okay so we don't need our invoice as well we do want the product we do want the price we also want to have the quantity and we want to show the amount so I think it makes sense probably if we put the quantity before the price we have the amount at the end here and let's just check to see if this is working we've got our blue widget we'll key in this quantity here 50 of that and that should multiply that amount across and what's great is that this is happening in that near real time you're seeing that update very quickly and this is just using our formula field so if we go back into our data and if I go into my invoice lines here we can see we've got our quantity which is that number that we keyed in but our amount is just a very simple formula we're just taking quantity times price and that price is coming from the product table where we're actually doing a lookup we've got that price coming from there now we've designed much more complex systems where we've said oh hey we have a default price but then we want to be able to override that price or we want to add line item discounts or things of that nature but I'd say by and large if we can just keep it to this simple price times quantity that works out well for a lot of people both for products as well as service-based businesses too now we're ready to Total this up so down here we're going to add another field here is our total amount and we're going to have it below our list here so if I close out of here here is our total amount now unfortunately air table doesn't have a lot of really good formatting options yet so I don't think there's a way where I can right align this field which is what we would come to expect for an invoice so we did a little workaround here this is pretty goofy I'll be honest if I go into my data and I look at the invoices I have created a field called spacer and in spacer I'm just returning a space uh in quotes here because I wanted to have some space on the page without it actually displaying lines and and extra information like that so that's what we're doing there the total field that we have here is simply rolling up it's summing the values of our invoice line so again that's a calculation that happens dynamically so down at the bottom I can go ahead and search for that spacer field and we'll put it right here and when we do that it still shows our label but we can actually turn off the label in the appearance and that's how we get that little space on the side to align it that way so if someone has a better way of doing that let me know that's been my workaround as I've been creating interfaces let's go ahead and test it out we'll publish our interface and then let's go ahead and we aren't going to share that let's now create a brand new invoice so we'll click to add an invoice and we'll choose our account that we know has some information and we'll create it and that's pulling in our information our automation just ran like we planned to we've got it linked to our account now let's add our products here let's choose the green widget and we'll add a quantity and we can see that that sums up and if we add another one just to check our total red widget and we choose 10 here then we should be good to go and you can see that's how we can create our invoice generator the next question that usually comes up is well Dan this looks great to enter that information but how do I get this information to our clients and I think there's three main ways that we typically handle it I'll say right off the bat that I think air table should be a little bit better equipped to handle this inhouse they don't have great printing functionality to just say hey generate a PDF and it looks perfect and send it to the client and do it in an automated way so we're pretty much left to use external systems like make or like zapier to send our information so the three ways that I've handled it one is we send it to a system like QuickBooks where you're doing your invoicing QuickBooks zero there's a number of different platforms out there and they handle the actual invoicing and the payment collection side of it that's one way that we do it we're sending the data rather than just an image or PDF of the invoice that we've generated here but we have that underlying data that we just created another option would be that we send it to create a PDF and we can do that by using a number of different document Generations that are out there and then a third option would be if you're using a tool like softer as a client portal which automatically syncs data with air table so that you could show the invoice records directly to clients as they're logged in that would be another way to handle it if you have any questions on how to get your business up and running inside a air table don't hesitate to reach out to automation helpers . where we're offering free 30-minute consultations