Simplify Your Workflow with Airtable Invoice for Teams

Airtable invoice for teams

duplicate entry is a huge waste of money and resources don't enter order and invoice details in multiple places in this tutorial I'll show you how to automatically create invoices in Zero from Air table check it out welcome back to the channel for those of you that don't know me my name is Zach Stevenson I'm a business process and no code consultant if you need help streamlining or automating any of your business processes you can visit our website interdev solutions.com or you can click the link in the description Bel below to book a free console I have my order Management System created in air table you can do this process in a lot of different databased or no code based tools even if you have your information in Google sheet smart Suite or a lot of the other tools that are available out there the process will be very similar but I have M creating air table if you want to build one in air table and don't have an account already there is a link in the description below we will also be using mate.com to build out this integration if you do not have a make account already there's a link in the description below for that as well getting started we have our order management demo set up we have an orders table order items or line items that link back to the orders and we have customers and of course our products we're going to jump right into make and we're going to integrate with zero being our accounting and invoicing platform if you do not have a make account already you could go to make.com or there's a link in the description below once you have an account set up go ahead create a new scenario and we're going to have to authenticate and connect our accounts with air table and zero first thing to do that what we want to do is go ahead and click this add new module icon at the end we are going to create a web hook button to initiate the scenario but to get started we are just going to use the air table module and we're going to use a get a record and we're just going to pass in a static record for the time being if we click get a record there's a few connection accounts but I want to go in and add a new one we click add connection type we'll select the off option where you can label this whatever you want I'm just going to call this demo order management and then we can click save and it will bring up a new menu for us I'm already logged into air table I can go in here and click add a base and find the base we want to use which is order management demo and then in Brackets zero you can select it and then we can grant access and now that authentication is only going to link to this individual base rather than an entire workspace or all of my bases that I have access to we gr access and later on we will likely have to update the integration because this is going to be a read only cuz we're just getting a record once we go ahead and update the record we will have to allow more permissions now we can go into the base there's only one available and it will find the tables for us here in a moment go inside tables and we want to start with an order and as I mentioned we're just going to pass into static order to get started I'll go back to air table and before we continue and make I want to just go ahead and add in all of the additional fields that we will need here in air table that we can easily get the record ID of our orders we're just going to create a new formula field I'll label it record ID and we're going to pass in the function record ID and now it's displaying the record IDs for us I'm going to grab the first order copy that go back into make and pass that in and hit okay we'll go back into air table and add in our additional fields that we need this orders table is going to represent our invoices as well I'll go in Hit This plus icon and we're going to go zero invoice ID that once we create the invoice in zero automatically we can pass in the ID as a reference back into air table this can just be a single line text field I'll go in and add another one and this is going to be zero in invoice number the ID is going to be a long string of text that is unique within the zero system and then the invoice number is going to be something that's a little more legible and displays on the actual invoice this one we'll just also use a single line text field and that's all we need to add in the orders table we'll go into customers We'll add another field and we can call this zero customer ID or in zero they call it contact so you could also call it zero contact ID and we will just use a single line text field here as well now we can continue back and make and we can click this little add another module icon and from here we're going to click this air table button and we are going to get another record we want to select our order management demo base and this time we are going to get the customers record and it's going to be passed in from here and if I hover it you can see that it flashes there and background on the left and what I want to do here is go down to customers I can hit this little button and I can hit the one here and it's going to pass us the customer record ID and good practice if I right click I can rename this module this one's going to be get order and this one is going to be get customer the reason for this is you'll see in a moment when we go to create the actual invoice we have to pass it in the cont contact or the customer ID That's Unique within zero and that will be stored as we just created in the customer table now at the end of this video I will show you the full scenario that I have already built out and I will just quickly go over creating and searching for contacts or customers on the latter half of the router and I'll show you what that looks like at the end but from here the next thing that we want to do is add in an iterator and this right here which I just mentioned I'll show you we would add in a rotor here basically what's going to happen is if the customer the zero ID is found it's going to go the first route and create the invoice for us using the found zero customer ID but if it does not have that an air table it's going to go a different route and it's first going to search for the customer by email and customer name and if it's not found it will create the new contact and if it is found it will just create the invoice using that found customer ID but we're going to keep things a little bit more simple we'll go add in this iterator here and what we want to do is we will bring in the order items that is found here and it's going to iterate through each order item now we can click this add another module another air table one and it's going to be get a record what we want to do is is we're going to get the line item record or the order items record for each line item found within the given order and that's what this iterator is doing here again go find our base go find our table which is order items and then the record is going to be from the iterator and we can click okay go in rename it call us get order items and we'll hit okay and now we can add in an array and we're going to merge all of the order items that we have now found the source module will be back to the iterator the target structure I'm going to hit okay here because we need to find or create that Target structure which is going to be create an invoice in zero we'll search for zero go down here and we will use the create an invoice module I have already created connection but similar to what we did in air table if you do not have the connection created already go ahead click add it will bring up your zero account or you'll have to log into your zero account and even simply do the authentication I'm just using the demo company now type we actually creating an invoice not a bill and the contact ID as mentioned is coming from the get customer module we'll click contact ID find the get customer module and and we'll go down to the zero customer ID we'll click map because what we're doing here is we're getting a new line item or multiple line items and we're just going to pass in this array here and we will click okay I'll go back to the array aggregator the target structure is going to be line items and that create an invoice that we just found and here is the line items Fields we're going to keep it pretty simple I'm going to use the the product name which is a lookup field the product name here is a lookup field otherwise it would just pass in the record ID here but we want the actual text value we can pass that in the quantity and unit amount we'll just go in and select this item price here and everything else I believe is optional depending on your unique business there's going to be different information that you need to pass in last step I'm going to add another mod module it's going to be air table and we are going to update a record I'm going to choose my order management demo account that we just created and as I said we'll have to update connection permissions we'll hit continue select our base and we'll Grant access we'll select our base we'll select our table which is orders and the record ID if we hit collapse all goes back to the very first ID or get order module that we created and if we scroll down here are the two fields that we created within the orders table which are zero invoice ID and zero invoice number from the invoice module that we're creating we can pass in the invoice ID and if we click and drag this invoice number we can drop it into the invoice number field H save we can rename this to update order I go into my order management system and go into customers I do not have any customer IDs here this is what I was referencing earlier that we will set up or I will show you the separate route that I have created that will search and or create new contacts or customers within zero and then update the information here back in contacts but what I need to do because this is the customer that I'm creating the demo on I already have them in zero I just have to go will get the customer ID and you can do that by going into zero Go Contacts select all contacts the customer name is this Tech innovators I'll just search Tech and you can see they have been found I'll select them and then up in the URL there is a ID that can be found and I will just copy that and paste that into zero and you can see it looks like this long range ROM string of characters now if I go back into make I should be able to run this once and it should work properly for us we'll go ahead give it a test I'll click run once and you can see it went through got our order got our customer if I open this up we can see that it found the zero customer ID that we just pasted in there goes to this iterator here which is going to iterate through each order item that was found there should be four line items on our invoice and you can see that true here I've got four order items aggregated the array and then created the invoice here and updated it back in air table if I go into air table quickly go over to orders we can see the zero invoice ID and the zero invoice number here that was created and if I go back into zero go into our business tab down to invoices and we'll be able to see this invoice 035 if I select it we can see the four line items that were added it gave us a description quantity a unit price and it automatically calculates the amount for US based on how you have your zero settings set up the tax will be different that's the simple version of this is creating the invoice assuming that you already have the zero customer ID created and in just a moment I will show you what the other option looks like that has the multiple routes that shows you how to create the contact or customer but one last thing that I do want to do here as I mentioned at the start of the video I want to trigger this by a button there's a lot of different ways that we could initiate or create this invoice but I want to create a button if I go in here I'll search for the button field type I'll label to create invoice and I'll just pass that in here we can pick our color and and it will be open a URL and we're going to use a URL formula if we go back into make I'll right click add a new module we'll use a custom web hook we're going to add a new web hook and just call this demo create new invoce again you can label it whatever you want we'll hit save I'm going to copy the address to my clipboard which is also found here I'll hit okay and I will attach ATT the web hook to the get and order now what I want to do is go into this get order and I'll clear this out and eventually I will have the web hook once we give it a test run we will have a web hook value that we can get from the key value pair I'll show you what that looks like in just a moment back into a table we'll use a concatenate function and within the quiles we will paste in our web hook URL and then we're going to add a question mark so that we can add in a parameter we'll just call this record and we'll add it in equals and then on the outside of the quote because we're using a concatenate function we can pass in the record ID I hit save and we can see if I go select the first order here or the order four it creates and adds in our make URL or web hook URL it's added in a question mark with a record this is the key and then equals the record ID if I hit cancel we'll go into here we'll just run this module once if we click this button says accepted we'll go into make and we can see it's passed in a value that has a record as a key and the record ID for that order now if we click get orders we can clear this out and we'll pass in this variable of ref this time when I hit run once it will sit here and spin until we go in and click the button now when I click run once it's initiated and waiting for information to be passed I can go in create an invoice and go back into make and we can see that it's created another invoice for us using the variable and record that we pass has to now as I mentioned from the start we have a little more complex or Advanced scenario the difference is we have the get order get customer and then this top route here matches our scenario if and if I zoom in it's hard to see I'll click the filter if zero ID exists we're looking at the condition being if the zero customer ID is found then we're going to go this route that we'll have no problem creating a invoice and linking it to a contact or customer that already exists within zero but this is where the router comes in we'll split this rout if it is not found if zero ID does not exist on this customer ID then we need to go a different path and the first thing it's doing is it's searching for contacts it's using a module that's been pre-built and we can go ahead and search for a contact I'm just using a search term of looking for the company name or the email address if it exists within zero then we want to grab that zero customer ID and if the customer is found which we're using a filter here we're going to go through basically the same process as up here we're going to iterate through the line items we're going to get each of the line item details we're going to aggregate that an array and then we are going to create an invoice and we'll update the customer and order the update Customer because it has found the contact it's just going to add that uh zero customer ID in to air table and then the other route which is not found we need to create a contact will create a new contact in zero and then same thing as the first step there which is iterate the items get all the order items aggregate the array we'll create the invoice and then based off of the contact that we just created we will update that customer ID back in air table here and that's the slightly more advanced and complicated version of it but again it's pretty simple it's just duplicating this scenario here that we just created with a couple of extra modules in it being searched for contacts and created contact that is it for this video I hope you enjoyed it make sure you hit that subscribe button for more tutorials in the future