Create Airtable Invoices for Management Effortlessly

Airtable invoice for Management

[Music] in this video we're going to format our invoices table to make sure we're pulling in all the data that we need to generate our invoices so to start we generated this just blank invoices table in a previous video we're going to delete a few of these fields and kind of start from scratch with our own fields and in this first field we're going to customize this field type to be invoice id and we'll just leave it as a single line text for now we're going to change it to a formula field later but we need to add in our other subsequent fields before we do that so this is going to be our invoice id and next we're going to link our clients table so we're going to link to our clients table and this will be clients client and we'll create that field here and we're also going to need to link to our design projects table and our task table so let's go with design projects and our tasks great so we'll be able to pull in data into our invoice from any of the other tables in our base now let's add a lookup field for contact name so this is going to be our lookup and this is going to come from our client table and this will be our billing contact and we're also going to pull in our clients logo in another lookup field and next we're going to want to add a rollup field for our invoice total so we want to use a rollup field because one client could have multiple tasks associated with their project and the roll-up field will allow us to automatically add all of those together as separate billing items into a single invoice so we'll call this invoice total and this is going to be a roll-up field and we're going to get this from our tasks and we're going to roll up the invoice amounts and we're just going to do a sum of those values so sum together the values of those invoice amounts great so now we have our invoice total added as well and next we're going to need to add a field for invoice number we'll just add that invoice number and we want this to be a auto number field because we want it to increment automatically with each new invoice so our auto number field is down here at the bottom air tables made it pretty convenient for us so these are automatically going to increment as a unique counter for each record and just a note here if you do delete a record that will still be counted in the auto count so for instance this generic table came with three rows or three records if i then now delete this row just by selecting it or right clicking and delete this record right so now we can see we saw invoices number one and two but if i now go to add a new invoice it's going to be invoice number four so it takes into account any rows that you delete so just keep that in mind as an auto increment field and last we just need to add a couple of date fields so one for our invoice date and one for our invoice due date so just scroll over to the right here so we'll have our invoice date as our date field get past it and that format is fine and then our invoice due date is actually going to be a rollup field as well because we want to take into account the billing terms that we set up in a previous video for the client so let's do our roll up here and we are going to reference our client table and we're going to reference our billing terms in days i'm just going to copy in our formula here which is just our invoice date that max value adding the billing terms to that value so we'll create that field it's going to give us an error for now because we don't have an invoice due date nor do we have any clients associated with any of these records yet to start so before we add in some of that data into our table so that we can see everything in action let's just scroll back over here to our invoice id field that we set up previously earlier in this video and now we're going to make this a formula field now that we have the other fields in our table we'll be able to reference them so this is going to be a formula field what we want the invoice id to pull in is the auto invoice number the auto incremented invoice number as well as the client and the task so i'm just going to copy in that formula and we'll just paste that in and i believe we have client instead of clients and we'll save here and then we have our tasks so as you can see we have the one for the invoice number the auto increment now we have our four down here because we deleted that one record and then added it back in then we have a dash and dash and so we'll add in some data now so we can see everything in action just scroll over yes so this was client instead of clients so this formula is accurate so let's head on over to our design projects table and the project that we'll work with for now is going to be our engineer you project that was already built into the project tracker when we downloaded it as a template or import as a template so the engineer you brand identity that's the demo kind of data we're going to work with for now so we have engineer you we've given it a few tasks so the market research initial proposal those have all been done and we have 150 as our project hourly rate and let's just clean this up a little bit so invoices was an old field so we'll delete that and then we're just going to rename this to invoices perfect so now this is connected to our invoices table here and that's what we're going to work with so if now we go back to our task we just want to make sure that our engineer you so again we're just going to clean this up a little bit i had to remake the invoices table and so it's pulling in our old table and we just want to make sure that's referencing the correct invoices table perfect so that's good to go and our design project so we have the market research initial proposal and prototypes for engineer u we have our billable hours and our hourly rate and these would be the invoice amount so let's just add in those invoices so we're going to go with invoice number one for each of these so we're going to essentially group these tasks into a single invoice that we're charging them for the market research initial proposal and prototypes all in one invoice great and if we head on over to clients here we can see that in engineer u we have added some demo data in here already so we have our bill to our bill from our payment information a billing contact and our billing email and all these are just click in to edit those details for each contact as you add a new contact and we'll just again clean up our invoices table here so we'll delete that field and again this is going to be invoices so we'll save that and we're going to add that invoice to this client so it's going to be invoice number one so now we've added that invoice to our client here and if we head over into our invoices table we should be able to now see that invoice one is for engineer u we have our engineer u client our design project we can double click in here and it's our engineer you brand identity we have our tasks our billing info our logo our invoice total is adding all those tasks together which is great we have our invoice number and then we still have an error for this due date because we haven't given an invoice date so let's just select today for now and now we can see our invoice due date is in 30 days great so that is looking great and that is how the invoice table gets auto populated with those details once they're added to each table so just as a quick kind of reference let's add a completely new client and project and you can see how this will all work together as well so let's head on over to our design projects we'll add that our agency is going to develop a no code app and [Music] we should be able to do most of what we need to do just from this single design projects screen because all of our tables are linked here so we'll give it a category and we're going to add a new client and this client is going to be fit with friends this is going to be a no code fitness app where friends and family can work out together virtually so that's what that's about we can add our build to here so let's add and our agency payment information will be the same as what we had in the kind of demo data so we'll just copy that in here and our billing contact will be chained quick and let's set our billing terms to 60 days this time and we're going to link this to invoice number two because we already have that line item there so you might as well use it perfect so we have our fit with friends client and now we can just assign a few tasks let's just scroll on over here into our well first we'll give a project hourly rate of 150 to our fifth friend's client and then we can assign a couple tasks so maybe we've just done the market research for this client we haven't done anything else yet and again we'll assign this to invoice number two and if we head on over to our tasks tab just to make sure oh i actually don't want to assign the market research we want to give it a new task so we're going to delete that we want to make it a specific task to the project so we're actually going to give it a new task and this will be it will still be market research but it's specifically for fit with friends great and we don't have any subtasks but our design project is at the bottom here our no code app and we could put our time estimate of one day and let's say we did seven hours as our billable hours here for this task and we have our hourly rate we can see our invoice amount is automatically calculated on that seven times the 150 dollars and we'll just add this to invoice number two great so if we head back over to our invoices table now we can see that we have that invoice number two our client design projects and tasks are all there we don't have a logo yet for this client but our invoice total is also there we have auto-incremented our invoice number and if we select day as our invoice date then we can see that our due date will be instead of 30 days it will be 60 days away great so now we can see how straightforward it would be to add new clients and projects to your tracker in the next video we'll look at using the designer block to create our auto-generated invoice pdfs [Music]