Collaborate on Stripe Invoice Example for it with Ease Using airSlate SignNow

Stripe invoice example for IT

what's going on everybody so today in this video what we're going to be going through is building out the invoices and payments Integrations into our airtable CRM so we're going to have invoices some of those are going to have payments attached to them others are not going to have payments attached to them we will also be going in rolling up invoice information C total number of hours paid for by any customer a total number of invoices sent to them their most recent pay date uh yeah that's what we'll be doing if you haven't met me before my name is Ben green I'm the owner of optimize is and we basically help businesses build out crms and different customer management systems just like this in airtable so if you're interested in that and you want to book a call to get some help if you're tired of doing this alone you can go check out the link in the description and book a call with me or someone on my team without further Ado we're going to get into this we are going to be doing some pretty advanced concepts here in airtable so I'm really excited to show you how to do some of this stuff and we're also beginning going to be connecting this with make.com so to pull connect straight to air table will be used to make so let's jump right into it so I have done a little bit of pre-work in here basically what I did was I exported a CSV from my old CRM and imported it into this CRM but I'm going to show you exactly everything that I did so everything in here I added a I've started the series a while ago but we've added a company's table and an invoices table each company table is going to have a company name it'll have some contacts linked to it currently I this is one of the reasons I'm building the CRM is I used to use I didn't have a company stable So currently everything's just contacts which is why you see an email here in the primary field but every record here in this invoices table is an invoice that was at least created in stripe and some of them have payments some of them do not so how it previously worked was just every record every time I create an invoice zapier would watch invoices in stripe and it would add it to my old Air table base so you can see some of these like the status on these are drafts it says I sent them I don't think I actually sent them actually I don't think I had something to update them as sent but this is everything uh these are all of the invoices and yeah so how it would also work is based on the customer ID so every customer and stripe has a customer ID it would link it to the right contact or company in my old CRM so we're going to do the same thing here and later on I'll be getting into an onboarding automation video not in this video in a different video where when I onboard a new client it adds them into stripe actually it's from the article when it on the audit call they get added to stripe and I pull their customer ID back into airtable so the customer ID is really what runs the whole system and it's what links all this stuff so if you don't have this set up already what I would suggest setting up is having an invoice ID column which is what this one is stripe and voice ID the next thing that I would really suggest having some of this doesn't as I found out doesn't really matter like this field we don't really need the invoice number is fine to have it's good for reference pay date I would highly suggest having this one I'm going to add some of these dates next to each other so the email the person that I just sent to you will use that one this contact field will get deleted because these are the same thing right now this is a linked record to the company's table definitely we'll need this one so all these fields or Fields you can add you just need to have these set up so that we can do this effectively the status I added a few statuses and you have paid draft open and voided and that's it uh origin we're going to default this if it's coming from stripe to created in stripe I have this in here because some clients I don't go through stripe I for example request payment of ISL or yeah it just changes for some clients so not everything is through Straight then we have a sent date so that's the date that the invoice was sent we have a due date that comes from the invoice and then we have a pay date so the pay date is once the payment is received we have in my old CRM I had zapier come in and update the date that it was paid as well as update the fees that were paid and the I think the invoice total didn't get updated but that's pretty much all you need things I also have in here are quantities so when I write an invoice how I do it is I write an invoice say for 10 hours for a certain hourly rate and it adds it back in you these ones are for like just test data in here so like some of them are one some of them are 111. uh but it's a number of hours for a certain price per hour then we also have the invoice total and then fees like I was saying the rest of these fields are specific for my CRM because I do hourly calculations I already imported all this data from the old CRM there's certain invoices that are exclude excluded from our calculations and then we have the old created date from the old CRM so those are all the fields that are suggest adding in here I added a field for profit as well and how we can edit this and redo it so this profit formula says if status equals paid so the status field is in purple right here if this field equals paid in quotes then what I have it do is I have it sum the invoice total and the fees so the invoice total down here this example is 2500 bucks and the fees taken out for processing this transaction were 72.80 so that puts The Profit at the invis invoice total minus the fees and then if the status so if we had this formatted well it would say like this so it says if the status is paid then sum those two things together if the status is not paid then return zero and how you would read this for for this if statement is if this logical statement is true then return the value of true if it's not then return the value to false so this is what we're starting out with this is the company or this is the invoices table we also have a company's table so here in the company table this is just some test data in you these are the different companies and really the only important thing here is it's linked it just has a name which we just have as this email address and this email address is linked to all those invoices so that is currently how that is set up we also have a deal stage and one thing we need to add in here is that stripe customer ID for every customer that's added in stripe I'm going to say stripe for every customer that's added in stripe they will have been added from this air table so this should always be filled in just not because we're just starting this out so that's what we're starting with we're starting with the customer ID on the excuse me on the company's table and then we have all the invoice data here so now we want to add an automation which we'll first go map out in Miro that will add invoices in here well another automation that updates them as sent and then another automation that updates them as paid and that's what we'll be doing each invoice that's created will also be linked to a company based on the stripe ID but let's go map those out real quick all right so let's see okay so here are some of our automations let me zoom out just a little bit okay so the ones we're going to be going through is this one right here as well as this one right here so our first automation is gonna this one in zapyrate checked every 15 minutes this is how the automation is set up though so it watches for new invoices then it goes and finds or creates a company and then it creates an invoices it creates an invoice and adds a quantity fills in the like invoice total the other one watches for payments which I believe in striper called charges then it sends a slack message to say new payment received and then I had it waiting 15 minutes or 16 minutes in case the invoice wasn't created yet like if they pay instantly or they charge their card instantly it would wait for the invoice to be created which would could at least be 15 minutes so starting over it goes new payment then sends a slack message and then it waits for a certain amount of time to make sure the invoice is created then it finds the right invoice and then it updates that invoice as paid so we're going to set up the same thing in our system but through make not a zapier so jumping into scenarios and make we're gonna create a new one this new scenario is going to say this is going to start at the beginning so invoice created create in optimized CRM so we're going to add a module here it is going to be stripe if you haven't added stripe yet you'll need to do that and then you can do this part so you'll just need to go get your API key and then you can create it and make so this is going to be a new scenario it's going to watch events so it's going to ask for a web hook and we need to add a new web hook this is where you'll need to add your stripe connection if you haven't already and now we're going to pick a group so this is going to be invoice Creed now there's a lot of different web hook like options in this so we want to be very specific because we're going to have a few different instances of this and we'll be testing this out to make sure it works as well all right so let's say group is going to be so we have the option for charge we don't care about charge yet we have the option for invoice as well as invoice item so I believe we will want to choose new invoice now we have all of these options so one option is we could create one that what watches all of these things all of these different events and does different things based on what event it is and I think that's actually a fair thing to do so we're going to say invoice created we'll choose that one we didn't yet map out something for if the invoice is deleted but we could invoice finalized marked as uncollectible marked as paid payment action required payment failed payment successful invoice sent so we're going to choose invoice paid invoice sent we will say payment failed we will say all of these invoice upcoming and voice updated invoice voided I don't necessarily care about all those and then invoice finalized as well so let's go and see let's try this out so we're going to create this web hook and these are the ones we're going to go with we're going to click save and we'll do a different we'll map out different things to do based on what it triggered off of I didn't name it very great I'll probably have to go back and rename that but we'll use that okay now I'm going to go into straight and I'm going to start creating an invoice so one second all right so now in stripe I got to myself as a customer I'm gonna shrink myself over here so this is the customer ID that I always use and the reason why I use this customer ID is because you can write a URL right here that will take you right there I'll do that in another video but I'm going to use this customer ID and I'm going to add it to airtable and I'll be importing all of my actual customer IDs in but for this it's this customer right here that's going to be the customer ID okay so back here in stripe now what I'm going to start doing is I'm going to say action I'm going to create an invoice this is the actual process that I do I've now technically created it so I want to see is this web hook gonna catch anything so let me X out of that and we will delete that and now that this scenario is oh does it say something okay so this actually did say invoice created that's awesome opening this up it has the right customer ID it has the right footer has everything everything looks correct okay so let's redo that we already deleted that one so we're gonna have to run this and here we will actions create invoice okay so that created one at for 210 which is the right time for what what it is here and this all should look right okay so that's perfect so we want to add one thing for if the action was invoice created which is the event type down here okay so let's add another module and my good friend Scott Rose taught me most of this so we're gonna try to do this our best we're going to add our router now this what this router should do from what he taught me and sorry Scott if I'm not saying all of this exactly right but basically it should go from top to bottom and it should route this automation to the right place how you make sure it goes to the right places is using filters so if I click on this I can add more but basically each one right here will be filtered so this is going to be invoice created so when you click on the little filter or the little gear icon you can put a label on there so this one is going to be invoice created condition will be that that one status down here this type is equal to invoice created so if the invoice is created what I want it to go do is I want it to go into air table and I want it to get a record it's going to be in the optimize CRM foreign so what it's going to do here is it's going to go into the companies table and actually I chose the wrong one instead of get record it should be search so we'll have it display the name but it's going to say search based on the let's see okay uh I don't think I'm gonna do get a record I think that is the wrong one so I'm going to delete that and instead I'm going to add an airtable search records and so so instead of get recorded I should have read this it should be search records and now we're just going to have to add this back in here so we're going to say the type the event type is what we just pasted okay so we should search records in the optimize CRM it's going to search the companies table we'll have it output the ID the name and the stripe customer ID we're going to have it search using quotes it's going to search based on this customer right here we're going to say equals and then in these curly braces we're going to type stripe customer ID and limit on this this should just pull one there should only be one matching all right so now let's see can I run that on the one that was created probably not any so that's what it's going to do and it should find a record and then what we're going to do is we're going to go create the invoice record so this is again being going to be in the optimize CRM and it's going to be in the invoices table so the stripe invoice ID we will pull that from this invoice ID object right there the invoice number this is usually I don't know if there's a way to really see what this says right there but it would look something like it would not look very super pretty basically so maybe it'll just be that for now the email will pull in the customer email I usually do this just in case it didn't happen to find a search record which I know you could add more advanced logic in here but we're just going to pull in the customer email the status is going to be the same thing every time in this time it's going to be draft which if I go back into my Air table base I want to make sure it has the same language as everywhere else so the status should say lowercase draft if I open this back up here I'm just going to copy that and paste it in there companies that should be mapped in here from my search records right here and this should be pulling from I think this ID we're going to test that out now some of these things will be pulled straight from Stripes so this origin or it'll be pulled straight from this right here so origin will always be created in stripe and now let's open this back up the rest of this at this time I will not be pulling the rest of this in let's click OK there and let's see if we can test this out so we're going to say create a new invoice and we will run this okay so let's see it followed this it said it found the correct customer so it found that record that we added the customer ID on and then it should have created a new invoice so let's jump back into air table and let's see if I created it or not okay so I just had to update the filters real quick and now we can see right here if I pop this one open it is going to say I created this y API 46 seconds ago that looks awesome okay so now I want to go back here so one of our other actions was the invoice was finalized so we're going to add in here some items on the invoice and then I'm going to send it to Ben green okay so I've added some items to the invoice I've added uh I only ever do one line item on the invoice unless it's like reconciling a receipt which doesn't impact hours so it should only have this one line it's going to say hourly Consulting there's a quantity of 10. ten dollars each and soon I'm going to send this so I saved this and what I want to know now is what does stripe consider that as do they consider that as a finalized invoice if not what I would go doing straight is I'll review this invoice then I will send it to the to me okay so there we add invoice finalized so now I want to say here set up a filter for invoice finalized where something equals this so that type equals that so for invoice finalized what I want to do is I want to go into air table I want to search records this time if the invoice is finalized that should mean it was already created so in the optimized I should be able to search in the invoices table return all of these and use a formula so our formula here is going to say that the stripe invoice ID equals this ID right here from the start so now I'm going to try to use this invoice ID right here we're going to run that and it didn't find anything okay so what I did wrong was I did not include quotes around this and so we're going to click OK there and we're going to say run that again okay so that found our invoice I think no it did not find our invoice because that invoice might be a new one I suppose so the invoice ID we're looking for is that one okay so the next thing I would go see is maybe when I was pasting that it may have when I click run this module only at the beginning here when I pasted it I had a tab in there so we're going to retry that and see if now okay now it has an output bundle which is what we want that means it found the record okay so now we're gonna say update the record so this is going to be the optimize CRM it's going to update the invoices table the record it's going to use is going to be from 6 the search record and what it's going to do is it's going to update the line item names to pull from here so let's see lines okay so we may have to come back in here and do some work with text aggregators but for now we're just going to see if we can probably it's not right but we're going to see if we can just pull on the description right there from that same area we're going to pull from stripe from the lines it's going to pull the quantity it's also going to pull the item prices so the total amount was a hundred dollars each price was ten dollars so I can't tell if that's supposed to be four or five so the unit amount here should be that amount invoice total we'll see if we can pull the total amount due in here that's worth the shot and we will see yeah we also pull in fees which we don't have at this point we have to wait until it's paid based on the payment collection method it may be something else subtotal the rest of this we won't work won't worry about okay now we can click ok let's try this again so let's go create a new invoice all right so now I've created it and it's been okay so what happened this time so in that okay so this is pulling the invoice that was sent from a while ago so I guess while we're here let's take this and say invoice sent so it's no longer a draft at this point let's say set up a filter label as that this will be the last thing in here says invoice sent in this case it's going to do this like same thing as this one right here and it's gonna do this right here as well so it's going to search for that invoice and then it's going to update it as sent except here it's going to be from eight so the status is going to say sent and we will not include any of this okay so let's get a little bit messy so let's Auto align this okay so which one here so this invoice finalized I believe this one is for the scent so that appears to be in here wrong so this should actually be voice sent instead all right bottom line that again we'll click save now let's try this again okay so let's run this again use the existing data and see what happened okay so the in this case the invoice was created so we can see here it searched and then it created the record and that all looks to be right is there another one I can use okay so that was this one so now let's review the invoice click Send okay so in this case the invoice was finalized it went and found the right invoice and then it updated it so let's go into air table and see what that looks like okay so this says five thousand dollar draft so this may just come in a little bit different than it did prior so the quantity was five the item prices 1000 so it says invoice total is five thousand okay so this may just be something like after a certain date I write a formula that says okay take the invoice total and divide it by 100 because it's bringing you then as since it doesn't have any fees it's not excluded so all of this appears to be accurate now let's go back into Chrome and let's see if there's anything else we can run okay so that one was for in invoice sent except it says invoice permissions allowed to create the new select options sent so that sometimes can happen what you have to do is status you have to make sure all of your options are in here so in this case I want to say sent and we'll make that yellow okay I'll drag it right next to the open okay so let's go back to Chrome let's see if I can use the existing data okay that time I had no problem updating it as sent sweet now is there anything else nope not right now okay so now our last one is when it gets paid so I think if I click this it will say invoice paid so we'll set up a filter that says invoice dot paid we'll say based on a condition that the is that the event type we'll say that type is invoice Dot paid click ok now we're going to clone this I think I need to re-addit all that now we'll delete this we also clone this okay so this one is going to be we're going to read that filter invoice dot paid there and there we're going to say that this type right here is that so this one we shouldn't have to edit anything oh wait no this is this one is the great record okay so let's see I may have missed that one up a little bit we're going to delete these so invoice sent and finalized those ones are where it updates so we're going to clone these instead this one we're going to set up that filter that says invoice Dot paid say okay now we'll Auto align these and we just need to update this right here to say the status should be paid so this also needs to be updated to reference instead of eight because it will not come before that guarantee it will come from 12. they record only so that's why that little yellow thing was right there we'll click ok and we'll save this okay so now it appears as if everything is set up right and we have our filters set up correctly so now I'm going to go ahead and pay that invoice uh yeah actually before I do that the other thing I want to update here is when it is paid um I suppose we're just gonna have to see what the information is first okay so I just paid it and we're going to click run use the existing data okay so now it ran it updated everything is paid so the last thing for us to do in here is to come down here to fees and we will pull in the fees so let's just try typing a new search for fees nope all right let's see if we can dig into this and find out what the fees were so if we scroll down and I'm not seeing it in this output okay well I guess we just won't pull it in on this one I would like to pull it in just a little bit frustrating can't find it we may have to like update the invoice or wait for a different thing to come in here and say it uh it may just not come out of this but yeah I do not see anywhere in here to indicate the fees and maybe pulled from a payment or a charge but maybe just not this okay so let's see if there's anything else that came from that so that one ran through none of them invoice payment succeeded okay let's see what that one says so that one in the object we have five thousand it's really fifty dollars nowhere in here does it say anything about fees in this one I think discounts nothing about fees so it looks like we're kind of Sol on this one we'd have to pull in fees a little bit different over what this is fine though this should work so now we have invoice finalized sent created and paid and all of these should add to our air table so the last thing I'm going to do is turn this on and now this should run so now what I'm going to do is jump back into air table and we will go do this from start to finish without hopefully without doing anything here in um make so I'm going to pause it I'm going to write the invoice and pay it all right so I just saved the invoice I'm going to review it and send it now we're going to go into air table okay so we can see that updated everything pretty fast came in as a draft updated from draft descent now I'm going to go into my email and pay it real fast okay so I just paid it and now okay sweet I can see it just updated it as paid where you can see all of this happened super fast the invoice total is off by a magnitude of 100 like 100 so we'll just factor that in in our formulas but this should work great so it says sent update to paid this is awesome it was a ten dollar invoice this time so it says a thousand so this is perfect I think this is good to go I am going to update some formulas in here so how I would take this into account as I would say for profit instead of this saying one thousand five thousand we're going to include something in here that says check box actually no we can just use old this field so this field is filled in if it came from my old CRM if it's not from the old CRM then this is going to be else skewed so what I'm going to say is if status paid let's edit this one we're going to say if that field over there if that field is filled in that's going to return true so if that field is filled in I want to pull in that same formula and if it's not filled in then that's going to be the new way and it's going to say invoice total divided by 100 and the fees which currently are not anything so that fixes this now let's update this to be a draft I need to do the same thing here it's on this one this is our formula so I'm going to copy this in here and here it's going to be just like that replace all this if yeah so if that record ID field is filled in it's going to return the first way of doing it which we can see like this if it's not filled in then we'll divide it by 100 and we'll delete this old formula sweet so now let's click save there and that appears to work correctly so the only things that won't look right are these things down here at the end which is totally fine with me all right so that's everything on how to integrate this with the CRM the next thing that I'm going to do is do a video on comparing the total amount of hours paid versus the total amount of hours used and how you can come up with that calculation for every company that you work with based on invoices that look just like these so click here in the end screen right here in the middle of the screen if you want to go watch that video on how to build out that reporting system