Create an Airtable Invoice for Public Relations Effortlessly

Airtable invoice for Public Relations

one of the great things about working in my business is that I work on a lot of interesting projects with a lot of interesting clients one of the recent projects I've worked on allowed us to create a custom payment system with xero air table and make.com let's have a look how that sort of system can be built let's get started [Music] so at the heart of my solution I'll be using make.com it used to be called integromat it's a very nice visual tool for creating complex automation simple and complex automations it's quite similar to zapier in its output in the sense that it's achieving a multi-step automations but it's much more Visual and extremely powerful and there are some things that you can do in in make that you cannot do in zapier and vice versa so it's a great tool for this particular integration one of the main reasons that I'm using make here is that within make the xero integration allows us to extract easily the URL of the invoice from xero which is not so easy to do in zapier so let's um have a look at how to build this automation starting with our table so here we are in make.com we've just created a new scenario and the first thing we'll see when we arrive in the scenario is this big black cross in the middle of the on the side of the screen here and this is where we're going to create our air table and module and then we're going to have that module listened to changes or new records in our ad table base in the paid meetings table so let's get started on this automation so click on the plus sign here and then I've already pre-selected the airtable module but you can search for the module here so select air table then click on watch records and then we need to choose a connection so we click add and we actually we've got a couple of different options airtable oauth has been a bit problematic I find it hard to work with I haven't actually successfully managed to get that working so far so I'm going to use the air table or key option now airtable Keys the API keys are only going to be useful for the next you know 10 months this is now um March 2023 so I'm going to use API keys for now but in future you'll need to create a personal access so where do we get this API key from well we'll need to go to the air table developers section of your account so you go into airtable and then you'll find here on the side in developers you'll find create you'll find the API key option here you'll see there's two options use the API incad instead or create so what we're going to do in this case we're going to use the API key so it's going to select that API key and the API key has been generated for you already so we'll just copy that key and we're going to go and paste that key into mag.com and then click save and shortly you'll have your connection to airtable now we need to select the base so in this case it's the business development base and then we need to select the particular table within the base so it's paid meetings and so what we're going to be listening for is basically we're going to have to have a last modified field and we don't have one in there at the moment so let's go have a look at the paid meetings table and we'll add a last modified or created Time Field in there so what I'll do I think because we're going to be making updates to it so when you first enter it into the end of the field the question is do you want to generate the payment when you've first created or when you make an update so I'm going to suggest that we add a field here a single select option single select and this field will be called okay to pay okay to invoice and then we'll have two options one will be yes and one will be no so obviously when we select yes it will then generate the invoice so at the moment that's blank and just put it as no if you want or leave it as default and then on the end here we're going to add the last modified field so we're going to get our last modified time in here and create that field there so obviously when we make a change to this this field here okay to invoice we'll then update the last modified so that will trigger the automation there in make.com so I'm going to go back into make now obviously we have to re-synchronize I'll just pick up those new Fields so we've got last modified in here the label field which is the initial one at the start here the name of the person is the default level field we're only interested in returning one record at a time and the rest can be left as they are and the view itself sorry is the grid view but select the grid view default view and click just a moment here yeah I think that's fine and click ok and then we'll choose where to start so we're going to start from this time onwards there's no no records anyway from previous prior to today so we're just going to go okay so now we're watching for records now what we're going to do is just test to see if this works so just click run once and what will happen is that you see there won't be any records so that operation will have occurred but there's nothing there so that's basically nothing that can't really give you any information at all at this point so what we're going to do is add in a value so I'm going to add in so Fred Smith here okay to invoice and we'll select um so select yes and then I'm going to kick off the automation again and see what we've got here and here we have Fred Smith so you can see by modifying that record we've now triggered this particular module so now we've got the module triggering whenever you make a change to when you make a change to in-air table and add in you know modify the record and we can then add the next module now what we want to do is we want to check to see if that specific related contact is already in xero if not create the contact in xero so we're going to go and use the xero module and what we want to do is check to see if a contact already exists we're gonna have to search for contacts by name or email in this case I think we will use email we can actually use a combination of the two which I've used in the past so we search for them via the name and then we confirm whether it's the same person or not so in xero they shouldn't be duplicate names you should have more than each name should be unique so we can then search for a contact and we're going to select add connection my zero connection now we're going to select the scope here which is going to be accounting I think we actually are pretty good with the defaults I think we should be okay or we can always adjust that later on and I'll try these ones I think that's the one click save and then you have to log into xero now in this particular case I'm already already set up the connection to the demo company so I'm just going to select continue with one organization it's a little bit confusing but it does um connect us directly to the cut the organization that we've already logged into in the other tab here you can see I'm logged into demo company so continue with one organization that then redirects us back to zero sorrytemake.com and now this particular what we're doing is we're searching for a contact so it's searching by a particular field in this case the field is the email address fact yep we'll just search by the email address we can also search by a name I think that's probably the wise thing to do so we're going to search my email address and the email address equals the same email address that we received from the first step so let's just grab that from the first step and the name is also I'm going to use contains because that's actually case insensitive so we can do that name contains and then we can go for take the full name as well so let's see if that works we can always fine tune it if we need to so click click ok now we're watching the records search for contacts what we need to do in here is add a filter so we don't obviously want to create a search for contacts and do all the rest you know creating invoices Etc unless we've actually said that we should create an invoice so we could do that so we just select here set up a filter and the filter will be um okay to invoice and in this case the condition is that okay to invoice is yes so if okay to invoice is yes then we go ahead and do them do the rest search for contact if we have found a contact then we don't need to create it again but if we haven't found a contract contact then we do need to create it so what I'm going to do is add another zero module here actually I'm going to add a what's called a router which will allow us to decide if we've created if we've got a contact then we'll create it we won't create it if we don't have a contact we will create it so what I'll do here I'll just go to router click let's grab a router from there pop that in there and then I'm going to from the router I'm going to add some more modules we've got two modules one of them is going to be the xero module for creating a contact and the other one is going to be uh the data store module actually so now the other one is I'm going to go directly to create the invoice so what I do is I go set up the filter if contact access so the contact exists here and then I'll just say contact ID exists so contact exists now what do we do if the contacts is this we can send them an invoice so that's where we do zero and we're going to create an invoice from scratch so create an invoice and we're going to select the account into a connection continue we need to add extended permissions so go back in again so we've extended permissions here and select the organization demo company and the type here is the sales invoice and the contact ID is the contact ID back here which is on the search step so just bring that one across and then we can add our line items so in this particular case I'm just going to add a very simple line item which is going to be a meeting fee and the quantity will be one the units amount now we're going to get the unit amount directly from the the type of from our table so we need to go back and have a look just down here the first record here the fee is here 50. so that's there and the item and the calculator we can leave that there tax code we could say for example bass excluded um account code it would be sales sorry that's going to go into our sales Account Tax them out we can say zero we're not going to charge tax on this one um okay and that's all good and that should be it so and then we click ok so that should now generate if you've got an existing contact should then generate an invoice now I know that this contact only exists in the in the database so we should be fine now what I'm going to do here is I'm going to set up a filter that the contact does not exist so if the contact does not exist we will then create a contact in xero I can't contact does not exist so okay and then what we're going to do is create the contact in zero again select the demo organization and the name of the contact is exactly the name that came across in airtable which is Fred Smith we can show some more advanced settings here now what we would probably need to do would just bring out his his full name now in our air table record we haven't we should have a formula in there for splitting first name and last name so we're not going to put that in right now the email here [Music] um Peter at process culture in that one and the rest of it we can just leave it as it is so you're going to get a very basic contact record just with the name and the email and then of course now it's not possible here to just bring that right back into the other one so we're going to have to do to create a new um invoice again so kind of duplicate it what we can do to speed up the process is actually take the existing module that we've set up right click on it and we can actually clone the module to make like a direct copy of it or clone of it sorry and then we'll have to go in there and configure it because in case any values have come from a previous step I think we're actually pretty safe oh hang on so yeah so the contact ID does not come from Step number four because there was no contact if you remember it actually comes from Step number six so that's where we take the contact ID from Step number six and again everything else is pretty well the same and just click ok now at the end of this process what we want to do is indicate that any invoice has been issued to this client so let's go back to airtable and in our table we need to create firstly we need to um probably select no because you've already already invoiced them but we could actually you know make it a little bit more interesting if I just go in here where it says okay to invoice We'll add another option and this one will be invoiced and we can just mark it as give it a color of reddish or pinky color click save and now what we do is we go back into make.com and then after you've created the invoice we can actually go back into airtable and just update that value so go into air table update a record and we're going to pick the record that from the first step there so we go back into business development select the paid meetings table and then the record ID comes from the very first step so we're just going to grab that very first step which is this record ID here so that's the record ID that we updated and then we're going to select for ok to invoice we'll just put in invoiced so you have to type it in physically type it in the value there and then click ok and then using the little trick that we used before clone that one and then pull that across connect that to this particular step and pretty well I don't think you need to change anything because it's going to be retrieving values from before the the router before the split so um yeah it is a little bit annoying that you have to sort of you know obviously repeat the same thing again but you can see here we have our basic automation setup so what I'm going to do I'm just going to run this through now you you obviously you can select scheduling and that will then run automatically all the time and that's that's the way to do it in the long term you know once you've developed the solution you can then run it and keep running it whenever there's a change you'll then get the invoice generated and you'll then get the updates into our table but for now we're just going to do a trial run so I'm going to go into air table I'm going to just flick this off for a second now we're just going to prove that when it says no to invoice it will block it so let's go and take test that so we're just going to hang on there you go just go into here select run once and it should stop at the filter which is exactly where it's done so you can see if we click on the filter it's it says okay to invoice is not yes if that's what it means there's the condition has not been met so now we go back in there again and this time we'll select yes and then we'll actually click on the Run once button and it should run right the way through because the contact doesn't exist and you see that's a beautiful thing about make.com you get a visual representation as you go through the system here and we can see that the search for contacts returned there was no it's a number of bundles zero there was no result there so when we got there I've got to the router and the filter is a contact does not exist we then create the contact create the invoice and then update the record in air table so if we go back into air table right now we can see it's marked as invoice so it has worked successfully now there's one other little piece that would be really useful if the customer comes back to us and if they want us to send them an invoice or we can create a step where we'll actually email out the invoice it would be nice to have the invoice URL so I'll add another field here called invoice URL and this will actually be a URL type in our table so create the field there I'll just push that along to there next to the um the drop down and if we go back into make.com after we've actually created the invoice I'm just going to go and add in here another module which is to download the invoice they get sorry get the invoice URL so select here get invoice URL and then your organization and then the invoice ID which comes from the previous step so step number eight invoice ID is here we select that and click ok and again we can do we could duplicate that step or clean it but it's pretty easy so I'm just going to add a module zero get invoice URL and then select the invoice ID from the previous step and then click ok and then the last piece is to update the record now we've got the invoice URL so what we'll do is as well as updating the value to invoice we can also pass on the invoice URL which is here okay and we'll do it down here as well in this lower branch and select the invoice URL and click ok so now when we run it we should get the actual xero invoice URL so let's let's try that one more time again we'll flick that to no and just go and have a little look down here and just run it again and it will stop again at the filter so let's stop to the filter then we'll go back in here and select yes as soon as we've done that then we can run it manually again and you'll see this time it will run right through and generate the invoice URL and update the record you notice how it went through the lower Branch because we'd already created that contact in xero so if we go back into air table you'll see its invoice but you've also got an invoice URL so we can click on this invoice URL and open up the invoice and you can see what we've created so as you can see we had an error there which is um was a bit unexpected but actually once I've dug a little bit it's a fairly fairly obvious firstly there's a couple of things I missed out so let's go back through and fix them so firstly in here we've actually not specified the due date for the invoice and also another thing you can't get the invoice URL unless the invoice is actually approved so let's go in here and click on create an invoice and at the bottom here you'll see this toggle here show advanced settings and in advanced settings we can actually go down and select the status as authorized there's number of statuses drafts submitted and authorized so selected as authorized then there's one other thing that we need to do we need to set the actual uh the due date for the invoice because if you don't do that you can't create the invoice so I know if you can't create an approved invoice authorized so what we do is we're going to use one of these date functions in here so we're going to use the add days date function so add days and then what you do is just click in between the two parentheses and then put use now which is basically today's date and then add however many days is the default for you so now we can of course in more complex systems retrieve that information from somewhere else but I'm going to put in this invoices due in seven days time or it could be in one daytime whatever if you're just about to book could be um that that day so we could just say zero one let's let's put seven for the example in any case and then click ok and then we'll do the same below here and I've actually done this already so if we go sorry create an invoice scroll down here you can see add Days Seven and then status authorized so now when we run that hopefully this time you'll get a valid invoice URL so go back into the business development base and make that change so let's go into the development base and just switch that on to no I'll just run it for a second here and stop it at the filter and then go back into here again and select uh yes rotate invoice and then run it again this time should run right the way through with no errors fingers crossed and there you go it's gone right through and updated the record in our table and you can see here it says invoiced invoice URL we just click on that link with with good luck we've got there you go we've got an invoice so 50 Australian dollars no GST quantity of 1 and meeting fee so we've successfully created an invoice in xero directly from Air Sable so that's fairly successful if you've got any questions about this integration add a comment below all right thanks very much [Music] all right [Music]