Going to create a payment link then customers are going to click on that payment link pay that bill and that payment is going to track automatically back into excel all in just a few clicks so it's going to be a really incredible training like nothing i've ever taught before this one will be a little bit different you won't want to miss it i hope you do like these trainings if you do then i would just ask a few things from if you could just click that subscription down below and i really appreciate that that was going to help us don't forget to click the notification icon bell that'll ensure that you get these training each and every week if you do like this application or applications like these i've got 200 of my best applications now on sale available for download.
In a single zip file it's only 77 and i've got a little bonus on top of that so it's more than 200 right now if you act now so that would really help us out that keeps these trainings free each and every week i've got so much to show you in this training so i really appreciate it also thank you for your views likes and shares the one we created just less than two weeks ago the automated purchase order with inventory that was an incredible application now over 100 000 views in just 12 or 13 days an incredible feat so thank you very much i really appreciate that let's get.
Started on this training i cannot wait this one's going to be a little bit different because this is a large application there's a lot of components if we were to create this from scratch it would be four to six hour trading i don't want to put you through that but what i want to do is i really want to focus on the automation aspect of this application how do we automate things in excel how do we use third-party tools such as toggle which i'm going to show you such as introgermat that's integromat such as integer map that's going to actually bring everything together and how do we use stripe an incredible payment processing application that's global that can actually create invoices create payment links and actually take in credit cards and build those customers and how we can integrate really tightly excel and how do we do that automatically with.
Just a few clicks of a button so we're going to go through all that let me show you what i mean first we'll go through our scenario and how that would work so let's go into toggle real quick and i'll show you what kind of scenario we would be looking at and then we'll get and then we'll go into more of a detail so toggle's a great time tracking i'm going to walk you through every step but basically let's say we have a project so let's just say website update right and you want to put in this track time and you want to select a specific project you got a website update project you'll start your time or you can do this from any device right this particular third-party application works on mobile phones websites browsers whatever so you've tracked your time you stop your time.
Have we've just created one invoice let's take a look at what that's going to look like inside stripe and we're going to go directly inside the stripe account we've got a test mode here if we go in to our customers here we're going to see that invoices under the invoices i'm going to walk you through every step of this that invoice i just created is here we can send the custom we can add a note if we want to view the invoice we can download it we can see what it looks like we can also pay for it so let's go ahead and click the pdf we're going to download that and take a look at it here's the invoice that got created inside that so that's that website update here it got created in the customer could get a copy as well we can also view it inside there we.
Can send the invoice charge a customer directly in stripe they're going to get a payment link they're going to be able to click on this payment link you also have this same payment link inside excel when they click to payment they can click right here so we can send them this direct link i've already downloaded this link and i'm going to show you every step we're just giving you a rundown they're going to click pay that payment's going to process we're in test mode so we can do everything i'm going to walk you through this too so they can download the receipt they can pay they put in their credit card information back in excel we can now process those payments let me take a look at this and all we have to do is click process the payments and all the payments one payment's been processed and now all we need to do is just enter this invoice again we see that that payment was.
Paid on and all the information was here so it's we've got a lot to cover in this training that's how it's going to work i still got to get this paid amount to go payment here we've got that all right so we've got so much to cover here let's get started on this basically what i want to do is i want to walk you through every step of the process we're not going to be writing really much code at all in this training because there's so much to cover i want to take my time and go really slowly and walk you through the entire process from start to finish i just went over a really quick idea but basically if you want to learn how to create this invoice i've already got that for you i've done.
Something called invoice from scratch i'm gonna put the link down below invoice from scratch that was a video we did a little while ago and i'll make sure that you get a copy of that video link so you can go ahead and watch that so we're not gonna create this from scratch i'm going to show you how we can create this automated time and billing and payment tracker so the first thing we're going to do is we're going to start out with tracking our time and billing so we're going to go into that toggle and that's a really really great application so we're going to look at this so this is what i want i want to be able to create time tracking and i want to have that information but not just time tracking i want to be able to create customers create users create projects so.
We can have all of that sent into excel excel is going to be able to handle all of that for us and track it all so how do we do that well toggle's going to we're going to start out with that that's our starting block right so we want to track all that so toggle is a great free application there's some additional features if you want to do charges and things like that then you can pay for it but for now we're just going to use the free app so inside this we'll start out with teams right so you can have a team you can create additional users in this case team members here i've got two of them set up here those would be likewise in our application we call them users so.
Posts Related:
Inside our application here we've got a list of users so i'm going to go over that so we've got
Users here where i've got two users now when we add a user inside or a team member inside toggle it's going to automatically add that here so we've got an id that was generated by toggle we've got a name and an email we can then add cost and billing rates if we want so just some ideas so that's automatically going to be here i just changed the name to lisa just so we had a different name here and there but basically we have a user id here that gets generated from toggle and i'll show you how to bring all that information in as you can see it's a large project.If we were going to write the code it'd take three days so we have to you know sometimes we can write the code on similar ones this one i want to be able to take you through it okay all right so we've got users and we'll create them from toggle we can also create them draw we can just add them here as well so there's nothing preventing us from doing that we also have clients clients are our customers so when we add a client here so if i add a client here let's just say thomas freed okay so if we click on that create it what i want is i want to automatically send that client information into excel so we can do that update and so how do we do that we how do we send it.
Will we use of course integromat to do just that integromat's going to help us do that it's going to do a lot of our automation for it if we do if you don't know what integromat is we're going to walk through that so we're going to go through that in a moment so we have teams we have clients and then we have projects projects are assigned to individual customers so if we want to create a new project what we can do is just create a we can call this let's say app testing we can create a brand new project creating and we can assign a customer right so we want to basically assign a specific customer or we can do a create a new project inside a specific workspace we have different workspace so we can do that create a project and we can do that once we create a project like ap testing we can also assign a customer right so we can do that very easily.
If we want to so projects app testing and we maybe want to do we can add different members to that if we want as well so lots of things we can do with that we can then edit the project so what i want to do is we can also select a client if we have a specific client we want we can save that and it's going to assign that project to that client i want all that information to come into excel too any changes or any new projects i want that to come in so we've got projects we've got clients we've got team members all the and lastly we have the timer right our time tracking all those instances.
Of time when we start a time and when we stop at time i want that time information also to make it into excel so that way we have a fully automated time tracking and billing and information okay so that's going to come into excel now once it gets into excel what we're going to be able to do is track everything so let's go over inside excel just what we have the foundation i've got an admin screen now we need to store that information when our customers come in directly from toggle we need.
To store it somewhere when our projects come in we have a new project we have an updated project we need to store that when we have a time tracking right those time entries that we just spoke of we need to track that as well and also payments and users so we've got to track those five different information we're going to use dropbox to do all that dropbox is a great file so i've got it all in here inside a single folder inside my dropbox i've mapped everything basically you can see the mapping here to each folder we've got customers the customers i've just added that's going to be here i've got payments if there's any payments that can be added projects changes in the product will be saved here as you notice this project just got created and also time.
Tracking any time tracking we're going to become here and also users any changes or new users would come here so basically once it ends up in dropbox it's going to allow us to then check that folder and if there's any changes or any updates we can take that information extract the information from those txt files and then we're going to bring that into excel and then that's going to come inside our data so it could go inside our invoice list here this is a list of all of our invoices.
They could go into an invoice item list these are all the items that make up an invoice what's an invoice item so for example if we enter an invoice here these are individual invoice items so they get brought back all the way in there of course we can add our own items too if we want right that's no problem but we do want to automate as much as we can if we're going to be tracking projects and time for multiple people we can invoice our customers almost effortlessly and accurately with this system and the power of excel is what i want to show you that can excel can tie it all together when we use these really great third-party tools so that's what we're going to do so we can bring.
In these invoices and we can bring in this data so we have these individual items that's called invoice items and they're stored here they're all based on the unique invoice id so notice that all three of these belong to invoice number one these two belong to invoice number four and so on and so forth we also have a list of customers just a basic list we have a customer id this customer id was generated by toggle we have a customer name a date that they were added on and a stripe id when it gets sent over to stripe stripe also creates a brand new customer and then an id is signed there as well so we want a stripe id as well we want to put in their address and their email and then just.
Some empty fields if we need to add that later on again projects also when we add projects inside
Toggle i want that information to come directly in so we can keep track of our individual projects and i also want to know what client was assigned to that there's a client id notice there's a client id here notice we have individual customer ids here so 3 one this one ending one three three one if we see here this project here application development is assigned to that particular client so we have a client id so we know exactly which projects were assigned this is a fully automated.System and then lastly we've got payments right payments we don't necessarily i'm going to probably get rid of this because why because i can track i'll just remove this sheet we don't need it because i can actually track the payments directly inside the invoice list right so inside this invoice list i can put a payment right here a pay date and a payment amount so we don't need that payment it was something that i had an idea and i'm just going to delete that sheet we don't need that because i can bring the payments in because payments are going to be applied to a specific invoice so when we have that all we need to do is apply that payment and apply the pay date so we.
Know and we have an invoice id this is a invoice id that's assigned by stripe so you can imagine we have so much to go over even without us doing any coding today so we're going to have so we have also emails this is something that i kind of wanted to add in i kind of ran out of time basically i would like automated emails but i can only squeeze so much in a few days of work here so you can imagine here's a great here's a challenge for you when we create an invoice automate those emails we've created emails in the past before automate you can create different email templates based on payment information so it's a great opening for you to challenge yourself to create some automated emails and i've got a sheet here something i just kind of run out of time something.
I would really like to do billing items are something that we can add remember billing items are items that you may want to add inside your invoice notice i've got two billing items if i enter a billing item in the invoice it is a drop down list of billing items and we can inside that it's going to automatically insert that information the rate and the total we also have cost costing can help us it's something i put in here but didn't fully complete because once we have the cost right once we have the rate we know the profit from a specific invest you could put a i was thinking about putting like a little profit and loss a pie chart here or something so that we know the profit and loss on every single job all we have is a total cost and we have but you know like i said i'm limited with the amount of time that i have a few days to work on these so.
You can understand that and i really don't want to create too many large multi parts just because the views are generally not there on the larger so i try to pack all the value into one week if i can okay so we understand that we understand how we're bringing this we've got a lot of different data and i'm going to go over slowly with you every step of the way so we'll start out with toggle how do we get that information in how do we bring all that information inside there well we're going to use integromat so what we want to do is we want to wait for changes is there a change to this timer is there a change to a project or a new project is there a change to a client or is there a change to a team member if there's any changes we want to send that into our.
Excel application but how do we do that how do we trigger that well that is where integromat comes in tiggermas is an amazing tool and basically it's integromat.com and it's free for up to about 1 000 different automations per month so you get a lot of free space if you want to pay for it it's certainly worthwhile i'll include the link down below i think i'm working with them i'm going to try to get you a special promotion i've got a link down below for you so basically you've got all these possible integrations i think there's almost a thousand types of integrations now with almost every well you don't need to search for these but what you might want to do is search for something specific so what we want to do is we want to work toggle so.
If we click on toggle we're going to find that and we also want to work with dropbox so what we're going to do is we're also going to click on dropbox and so once we find dropbox then we have those two that we're going to work if we want to send information from toggle we're going to send it directly to our dropbox so i'm going to click continue and what that's going to do is going to create a trigger now what's the trigger now the first trigger is going to be something that happens in toggle so i'm going to click toggle now what do i want to look for now there's different things i've created them already but what do we want to look for or when workspaces change we're not working with workspaces too much projects change right noticeably or when tasks change when users change when clients or with time injury so let's just.
Say we're going to do time injury now i'm going to show you what i've done already but i want to take you through that so the first thing you'll do is click add you want to basically look for the api token inside toggle api token so where can you find that you go inside toggle here and you look inside your your information here your settings here inside your account your profile settings here and then what you'll do is you'll scroll down to the bottom and it's okay if you see my my you can click to reveal you can see my api token because after this training all i need to do is just reset it so it's no problem it's not for for me purposely i can just reset it and i'll.
Get a brand new one so here's the api token what you want to do is you want to copy that and you'll want to paste that directly inside this api token and that's going to automatically and then we're going to click continue and what that's going to do is and also with maximum number returns we're just focused on one transaction at a time so i'm just going to click one right i only want to return one because we're set up for that then what i want to do is i want to do from now on i don't want to i don't want any the past history of transactions i only want it from this point moving forward so i'm going to click ok now what i want to do is i want to you can do two things you can add a dropbox but what we want to do is we want to what do we want to do i really want to.
Create a overwrite a text file create over a text file so that's what i'm going to do if you do create i want to overwrite because i want to use txt and then we have to map the file we can map it or we can select a file if i were to select a file i'd search dropbox and also if you don't have a dropbox connection you just click add and what that's going to do is you'll put a name any name and it's going to automatically sync to your dropbox you do need to have a dropbox account it's free up to 2 gigabytes www.dropbox.com if you don't have it it's an amazing tool.
For file sharing that's going to help us bring that information inside so dropbox is what we want to use so connect your dropbox account once it's connected this popup will come of course i already have it so we can cancel that but what we want to do is once it's connected you'll use overwrite then we want to select a file and we're going to click select a file and we're going to browse for the specific folder where we want that to come in and where we want you've got all kinds of folder and i've got one thing called time and billing here and then i want to select a subfolder inside time building i want to look for time tracking that's the one i want i'll select.
It that's the folder i want then what we want to do is we want to assign a name to it what is a unique name but to do that what i'm going to do is i'm going to change this to map the only reason we did that is just to get this file path here that to make it accurate it makes it easier then i'm going to change it over that got erased not helpful for us okay so mapping a file path all we really need to do if you want to go into your file path just go into your dropbox here and we'll focus on time and billing and we want time tracking and what we're going to do is just all i'm going to do is just copy this right here making sure that we have that path i'm going to paste it in it's easier but basically you get the idea here so then once we have that i'm going to use this forward slash again then i want to assign a unique name doctor and what is that unique name.
Well i'm going to use this type this id here and i'm going to use dot txt that particular id is for a specific time entry okay then that's going to give us the name of our file you just want to make sure that the file name is unique for this time entry then what i want to do is i want to know what is the contents of that file well i need several information what do i need inside that well let's take a look inside our table if we have invoice items we're going to start out i need the time entry id i need to know what user i need to know the project and i need to know the description and the start and the end so i want to know all that information and i need to bring.
It all inside that txt file so how do we get that information in there well the best way to do that is just to bring it in from here so all we need to do is click on that so the first thing again what i want is that id we can do that here or we can use that then what i want to do is i want to use a separator some kind of a delimiter i'm going to use asterisk for that then what i want is as we mentioned here inside our table we want to go in order we want the user the project project then the description so that's just what i'm going to do the user here then i'm going to separate this by another asterisk then i want the project id click on that and then i'm going to separate.
That by an asterisk then i want the description then i'm going to click here description then i want the start time and i want the stop time so if i click start time and then asterisk and then stop time now that's great but what i also want is i want this these times i want them formatted they're going to come in an odd format it's not going to look this pretty but i want it in a very nice format so how do we set a specific format all we need to do is use some of the calendar so if we go before that put the cursor right before that if you'll notice the cursor right before that.
Then what do we do we just enter something oh let's go back in here put the cursor back there then i'm going to enter the calendar and i'm going to look for something called format date or format date that's what i want here format date so the best way to do that let's undo that so and then if you take a look in here i actually i'm going to show you what some of the formats are so basically it is the date and then we have a semicolon and then whatever format you want so that's exactly what we're going to do so how do we do that so let's let's clear out what we have here put it put it back to where it was and we can type it in manually now let me know format.
And then date then parentheses and then we're going to have the date then i want the semicolon and then i want to put in the for the date of the format so we'll use mm slash dd slash yyyy for watch that's it i'm going to do the same thing with the end time right i want the same thing i want the date and the time but i also want the time right so how do we get the time if we take a look in this format date we really want the times 2. so how do we get that let's take a look at that we have here is this one's good so all i need to do is just h m m and there.
Let's take another look at that because it was a little bit quick here inside that format date here we can see here because that looks just the right format h colon m slash a so that's what i want right there this is it all i need to do is just copy and before it disappears copy that and then put it directly inside here so paste that in here okay now i'm going to do the same thing with the stop time so i'm going to drag that stop time right inside here right inside here and then.
I'm going to bring that in all right so that's basically all i had to do here let's clear that out and go back to where we have it right here so now we're not going to use this one we'll use a sample so before the format here i'm going to put that asterisk and make sure that exists here format the stop time semicolon again here and then all i need to do is just copy and paste this here and then don't forget the end parenthesis here we need that too okay so that's it copy and paste that and bring it in here now that's going to format it properly and then of course the end parenthesis there we go so now that's all we have to do if we take a look at that here we see everything is properly formatted we have the id then we have the asterisk that.
Separates each one of them and we have the format this one how it's going to be formatted again the asterisk another format date and that's it okay so then what we want to do is then i want to bring the information here what i'm going to do now is i'm going to get rid of this one here the one and i'm going to go into the one that i've created just so you can see so now what we have is inside scenarios and i'm going to show you i'm going to leave this one we don't need to save the changes on this one and i'm going to go back into our folder here now you can create multiple folders if you have a lot of scenarios all you need to do is create a new folder here and you can separate all your scenarios you just need to drag and drop them inside.
So here's what i have so now the one we were just working is called test time entry and so what i want to happen is here's here's what we just created and here's what i've created basically we have the id the text and the formatted everything that you just saw there's one differentiation on every single change this is gonna from toggle every time we make a change to the timer every time it's gonna send information here every change right right every single when time interests change but what i don't want as i really only want to send the information when the task is completed.
For example i only want to send the information when we have a stop time if there's no stop time i don't want to send the information to dropbox only when we have a completed task so we can set up a filter inside this all we need to do is just click here and then we give it a label then what we do is we give it a condition in this condition i want to make sure that the stop time exists we can create all kinds of different conditions so that means if this if we get a trigger where the start time is there but there's no stop time it's not going to pass through we've created an effect a filter to do just that so now we have it so now only those.
Times only those entries where there's a start time and a stop time that is exactly how we do that so now if we create it created let's just call this site fixes and we assign it a project here we can assign it any project that we have let's just say this website update and we click start it's going to start it's not going to send anything now how do you know that it's sending we can look in the history we can see a history of everything that's going on inside intrica map so i'm going to stop that right here and now what it's going to do in this case what we're going to do is we're going to set it up on a timer see this little clock here when we click that it's going to say how often do we want to check for that how often do we want to check toggle to see.
If this changes well i'm going to check it every single one minute okay it's got to be equal higher than equal to one so every minute we're going to check on those and we're going to click ok now what i want to do is i want to see the history it's already on any changes you make you'll need to click save if you want to check just once you just click just run once and make sure when you're ready to go you turn it on then it's going to check every minute for time tracking changes when it's found a change and it's going to go through here if that change has a completed text it's going to go into your dropbox it's then going to create a specific file inside your dropbox under time tracking if i refresh this and we'll let's go in here give it a little bit of time go into the history here we also have a history so we know went on oh yeah we have to stop the time here.
Let's stop the time okay we've got it for two minutes and 45 seconds sight fix is there okay let's go into the dropbox folder there it is it's just coming let's take a look at this text file that got created and see exactly what it looks like it looks like here okay we have our entry our time id entry we have and let's take a look at the table so we can see it up against there there now we'll put it up against each one of them so we can see the difference so we have our user id which is this one 208 369. then we have our project id we have our description separated by asterisks then we have our start time formatted just the way we wanted.
Then we have our end time also formatted just the way we wanted everything is in here it got automatically brought in to our so now once we have it here what i want to do is i want to take the first available row and i'm going to put all that information into right here that's what we're going to do in the next step because that is the macro that we're going to run when we do it when we click on the invoice and i click on sync timelines it is that macro that's going to take that information and bring it directly inside this invoice there it is right here that's the information just so how do we do that well let's take a look inside the vba if you go into developers visual basic you can also press alt f11 to get you there i've got several modules.
Here that we're going to go over okay so the first thing what i want to do is i want to focus on our time invoice items time invoice items so this is the macro that just got run it's called time tracking items check for new or updates right if we make it update it'll be in here as well so check so first of all i want to check for any new users i'm going to go there's three macros that we're going to check for i want to check for new users i want to check for new customers and i want to check for new projects those are three different macros they're all relatively the same we're basically just checking a folder checking one of these folders here checking whether it's a customer folder to see or checking the product project folder or basically.
Our user folder to checking if there's any entries if there are we're going to update those databases so the good thing is these macros are relatively the same it's the same process so once you learn one you can do them all so first of all i want to know how many entries how many time entries are we doing so i want to keep that as a long number so we're going to dimension that as long we're going to set the folder path right we need to make sure we have correct folder pass so we've set that inside the admin this time we're going to be looking for time tracking that's the folder path i want to make sure that's accurate that's where our information is going to go so i need to make sure that c6 actually has a proper directory so we're going to check that with the folder path.
We're going to assign it to c6 and i also want to make sure that there's a backslash also on the end of it because we're going to be adding a file to that so putting that backslash there then what we're going to do is we're going to check to make sure that folder path is accurate using directory folder path vb directory if it's empty or if the folder path itself is empty meaning it's not an accurate this would be it's not an accurate folder it's not an accurate path or it's it's not a path or it doesn't exist at all in that case we need to let the user know we can't check a folder if it doesn't exist so we need to let the user know for a proper dropbox time tracking folder so that's just going to make sure that the folder is correct and the path is correct once we know that what i want to do is i want to loop through all the txt files those are those little files.