Collaborate on Overdue Payment Reminder for R&D with Ease Using airSlate SignNow

Overdue payment reminder for R&D

so in this video we're going to be building rent reminder agent for real set businesses so what this assistant is going to do uh just before five days the deadline is H is going to call the tenant for reminder of the payment it's going to call three different times depending on what happens so um before so once is going to call five days before the deadline and then it's going to call right on the day when it's the deadline and thirdly if they have not paid the rent then and thirdly if they have not paid the rent then after the deadline the assist the assistant is going to call them and remind them that their deadline has been hit and they can create more problems for themselves if they not pay the rent so this is the kind of assistant that we building and this is the sheet that we are going to use for calling this assistant so these are the random people and they rent this is their deadline and the remaining days the due days call status means if the call if we have called them it's going to be true if not it's going to be false and for the payment uh as as we hit the deadline is going to say unpaid so right now I should receive uh four calls from this uh assistant uh I'm going to show you assistant later but I'm going just showing you the database as to how it's going to call me so it should call me like uh one two three and four times it should call me four times because as I told you if the if we have anything like five days remaining it should call us so it's five days 3 days four days if you're in the scenario it's going to call and there's also an overdue payment that's remaining so this is and there's also one overdue payment uh so it means a total of four people need to receive reminder calls so because I don't have uh four numbers I only have my own phone number I'm going to use my phone to receive four calls so I'm going to show you one call but technically I'm going to be receiving four calls so this is the scenario this is the scenario that makes the no this one not sorry it's this scenario that's going to make call and the funny thing is this one is um making it happen so when you hit this one is going to receive the call now we're going to receive four calls and we're going to see so we will receive four calls now and I answer one to show you that it's calling the whole sheet yeah so I'm receiving the call and I'm going to decline this one and I'm going to wait for another call there you go I got another call so think of it as a second call and can decline this one as well so I should make four calls because four the four people did not pay their rent three was like before it and one was overdue and this is our third call so yeah we should receive one more call yeah so we have received our final call let's pick it up hey Beni this is AA from Toronto real estate you have payment remaining for this month do you want me to give you the details yeah give me the details hello B and johy this is a friendly reminder that your rent payment of $1,200 is due on Monday September 30th 2024 please ensure your payment is made on time okay yeah so that's it it's just a reminder for the tenant to pay the rent and we have two types of messages let's just go and build this one if you need to build any assistant for for yourself you can reach out to me on my website which is z. and the templates will be available in my resources section which is also in my website let's enjoy the video so first of all we're going to build our assistant and I'm calling it run collection agent so the specification does not really matter we're only going to go on the prompt so uh this is a dynamic variable if you're not familiar it I share it in my channel often so Dynamic variable is something that you can uh use inside W dashboard and you can create it outside of the wpp so we created this one in the make.com that I'm going to show you later here and it's these variables that I'm referencing them uh I'm creating them uh in a me the account scenario but uh I'll show you better how to use them for now just remember these are Dynamic variables that contains values so we're going to say hey uh whatever the name is hey Alex this is a from Toronto real estate and you have ping for the role it's a rent collection agent and for the context um nothing special you're just talking about the payment to use and upcoming payments uh there are two type of tasks that that we have for this agent one is for upcoming notification one is for overdue payments if it's upcoming notification this is the script if it's an overdue payment this is the script and so how does it know which one to play well if we have overdue number uh if we have overdue days number and it's going to play this one and if we have remaining time uh number number then it's going to play this one so this is a variable over du days if the when we when we run our mom scenario if a customer has an overdue payment this is going to contain value or if the customer had remaining time this variable is going to contain value both of them at the same time cannot contain value this is how I have created make.com and uh Google sheet in a way that one of them can have value so if then I'm telling if you have the overdue days number then it means the deadline is passed which means this script the remaining if you have remaining time it means it's just a reminder so you play the script so uh it's going to be easy for Wy to understand which script to play because we're going to pass has either of the values and that's how it's going to play the script and these and the rest of the uh things they're just uh um like an example for hey you have an upcoming rent for this much payment again this is a variable which is due on this date this is again variable please ensure your payment is made on time and if it's an overdue then name your payment of this much payment was due on this date and is now over du days oh this is again number so these are variables that make sense when we create them right now you might you might get confused but um forget it for the timing we're going to build it and now we need to go to make.com not to make.com first of all we need to create our uh sheet so for the sheet it's a Google sheet that contains the name payment and it remaining date overdue days call status number payment status so uh for the payment uh not for the payment for the remaining date and overdue days we're going to be calculating them automatically every time so if this date passes um then we are going to say one day is overdue and if there's like five days remaining um you're going to say and if there's like seven days remaining then we're going to say it's seven days remaining so we're going to make we're going to create make.com scenarios to calculate the remaining days and the overdue days and then for the call status uh we can set it to true or false so if we have called the customer it's going to turn on to true so for the payment status uh if I hit paid and the scenario is running first let me run the scenario it's this scenario let me run it and if I hit paid it's going to change this date so it was one uh January now it turned into February so this is how it turns um it changes the deadline as well and um the deadline and once we have uh hit the deadline then it means it's uh an overdue day then the payment status is going to be uh like it's going to turn down to unpaid so this is the structure uh now we first need to go and U making out one call it's going to be a dynamic call so we're going to go in build this call first so first of all we need to make a search it's going to be Google sheet we need to search through this Google sheet and find out all those entries which has a call status or false so we're not going to be dealing with the true ones because we're not calling them we're only calling those uh entries that has call it as as false and then once we know so to do this we're going to work with search rows uh to create a search hereit this one search search row this guy uh when you open this connect your account and then you will see this filter in this filter we're going to select call status if the code status is false if the code status is false uh then we going to we're going to aggregate this search row function uh we're going to aggregate this search rows with the array aggregator and that is goes to then we are going to use array aggregator um to create an aggregator click on flow control and this is your array aggregator and once you have your array aggregator you want to uh select the fields that you need we need the total number of bonders row number if you hit select all it doesn't really matter it's fine you can go with select all as well module so it's going to just like aggregate them together into one thing and then we can and then we can use iterator to like uh loop through all of these entries one by one uh where this iterator is going to Loop through this array which is coming from this aggregator and it's going to iterate one by one and it's going to make these bundles now before making any call uh we need to make sure that we are not calling if it's not less than 5 days and we should only let the call happen if uh the the remaining time is either of 5 days or less we're not going to make a call if 6 days is remaining to deadline so it's 5 days or less and we're calculating the remaining time in other me scenarios for the time being just know that we already have a variable that tells us what's the remaining time and for like what's the remaining time for the deadline and in this case it should be either five or less than five and then we're going to process the request and at the same times either either it could have a remaining time or it could have a deadline so by deadline I mean uh the overdue days so these are overdue this if there is anything in these entries then it means we can make a call if there is um so if there's a deadline it means the number is in negative numbers will be in negative when when we have dead lines so it's going to call when so it's going to call when we have a deadline and and again we're just rechecking the call status must be false and we're getting all these values through the iterator module so the remaining time as you can see it's here is 35 days isue and for this one uh overdue this we getting it through this one so yeah we are getting our values through iterator so once uh this filter is passed then we're going to have this value so in our case it was only one entry that made it to the call and it was this uh for it and so it was only one entry that made it through the filter and then what we do is we get range of values so if you if I want to call this um number four calling number four calling this entry in the Google sheet um before calling this entry like this has let's say it's a false value when we were calling your false so before calling this uh entry we first need to get the whole row so that we have the information about benjam about his payment about the ending date about the remaining days so we need to have all these values before we make a call that's how we make it Dynamic to do it you need to get range of values and the range of values are going to be it starts from so in my case it starts from C up until I if I can show you it start from C up until I these are the range of values that I can get so to know which exact row I'm calling it's very simple uh I'm already getting the information from the uh iterator module so the iterator is going to return a row number which I can actually use and this is the row number that I can um get the range of values for so if uh if I hit C if I say C and then the row number and then the column I row number is going to return these values for this C10 I 10 it's C10 and I 10 yeah it's C10 and I 10 if you go back to your sheet it's C10 C and then 10 and then I and then 10 is going to give you these values so that's what we want we're getting this value next we need to go to create a Jon body to create a dynamic assistant and we're going to create some variables it name reming time payment ending de and overdue days we're getting all through this Google sheet and then so I'm assuming you understand all this so you can just C and paste into map into this then we add a sleep so let's say if we are calling 20 different people you want to delay each call by 10 seconds so after each 10 seconds the next call is going to initiate and now comes the real part we actually need to make a call and to make a call you first go to VY when you go to Wy and then you go to docs here you need to create a call and to create a call you need to authorize your connection put your Bearer key here your Bearer key is under your profile API keys this is your Bearer key and then uh we need to create uh we don't need to create body for this one um you can just copy and paste this one so in make you to create another module for HTTP make request and then U copy this API reference this one copy this URL and paste it here in the URL section and the method has to be post type and then in the first header you want to write authorization here you want to add your private Keys your beer keys and then content type application just on and now in the body you want to add this much code which is your assistant ID and then um this is the Json that's going to make it Dynamic so um before going over this uh you also need your phone number ID and then you can add it here you already have this as a template if you want to import it so if you want to import this as a template just go import and to the file and that's it you will get this in a template uh so you need phone number ID you need assistant ID and you need customer number so we all have these things uh the assistant overrides object is going to add these variables into our uh assistant so this uh thison string which is this one not this one this one uh has all these values it's going to go to um these are going to go to the VY so if you go to VY these type of dynamic variables are going to be replaced with these values so they're replaced with uh variable values and then whatever values you have so in my case it's a whole string that contains the whole information about the current caller customer and then in the customer um we are adding numbers so for each customer we have a different number and that can be found through the Google Sheets so every Google Sheets is going to return number um in my case I have the same number for everyone you could have different numbers just hit okay and now it's going to call with Dynamic values and just ined when we made a call we just want to update the row in the row number you can get it from the previous module and then you need to update it with the call status to true when you made a call it means you need to turn the call status to true so that you don't again call the same person so we have created this one and now we are able to call but the thing is we need to calculate a few things we need to calculate the remaining time over du days and we also need to deal with uh the payment status when we hit paid uh it should uh change one month to like it should increase add one month to ending date and then um zero things yeah so let's just first go and estimate these remaining times uh for that we can create another um make.com scenario and we first get the range of values again we first get uh we need to first get this sheet so that we understand we have the values then we can calculate how many days for each deadline is remaining and what are and if we have any overdue days uh we can do it by uh first we need to uh get the sheet and we do it through get range of values um I'm doing it from uh E2 to E10 so what it means is uh E2 this is E2 up to E10 uh so I should have written E12 but I have written E10 let's correct it so it's E2 up to E10 12 these are the range of values that I'll get when I run it let me run it when I run it I got these dates I'm only looking for dates because I'm calculating the remaining dates the remaining days and the overdue days I'm not dealing with the rest of the things in this module so um for the first um for this is going to set uh few variables so the variables are going to be current time new time and then there is a divider uh I'm going to show you why divider is needed so for the current time uh you can get current time with Now function and then you have to format it into this specific type of functionality format and then you need to parse it into date uh to treat it like a date in make.com you need to parse it to date so if you don't pass it to date the make come will not understand if it's a date or what do we explicitly tell me.com it is a date that's we do it to par date um for the uh second variable it's a new time which means uh it should not be called new time uh it should be called deadline so this is the time that's coming through um the Google Sheets and it is this one ending dat time which is the red line so we need to convert it into a format that we can uh compare it with the current time we need to compare it with the current time so that we know how many uh days are remaining for the deadline and uh if there is any due day so we again we need to do the same thing format it and um par it to a date so that make make the Comm understand as a date and um we have this divide because uh when you in make comment you're um creating the difference of two dates uh you need to it gives you the difference of milliseconds and to convert milliseconds into days this is the formula so this why we need uh a third variable and that's why I call it divide so click okay and give me let me show you an operation the first date is September 23 the date which we have um now it's two years over um okay so not two years one year because and that is because I added a few months when I was doing the testing so it added months in the test test um so when uh so when during comparing uh it's going to look at okay this is current date which is September 23 and these are not comparable so let's not do it okay these are the dates that we can actually compare so it's September 23 current date and the um dat that's and the deadline is October 28th so it means we have more than a month remaining and it's going to see the difference of these two to calculate the remaining days and overdue days to do that we create another variable with the name compare and then this is the first value which is new date um it is not new date it is um deadline so deadline minus current time is going to give us remaining time for this customer and if you see the bundle the time that it gives us is in milliseconds as I told you it gives us the difference in milliseconds we want it in days how many days are remaining and to convert it in two days we will have to divide it by the divider and the divider contains and this is the value of divider th000 * 60 * 60 * 24 is going to convert milliseconds into SEC into days and so compare is the value that we are getting through our previous module which is in milliseconds we're going to need to divide it to uh 1,000 * 60 * 60 * 24 and it's going to give us days so this is how I can show you uh this is 158 days and this is minus 2 days this is good operation five so so the operation five give us the value of minus two which is interesting because anything minus means a deadline and if you go to our fifth operation this is current date which is September 23 and this is the deadline which is September 21 so it means we are two days over the over the due date and it's a big problem that means it's in overdue day so we are two days overdue in this um the operation five which is okay so now so now we're going to go and um decide if how to fill this sheet we're going to go and decide how to fill this sheet with the remaining days and overdue days uh as you can see it shows 15867 we can actually round it to get U you know not get these pointing values I did not round it if you want you can do it and you should do it round these values when you round these values when you're uh updating the sheet so now when we updating the sheet what we need to do is we need to create a router because it could be a remaining day or it could be a overdue so in the first we we're going to say if it is overdue day then we're going to say the difference of this if it's less than zero if it's less than zero if and minus then it's an overdue day so uh if it's less then what we're going to do we are going to update a row and uh the row should be updated in here so when you update a row you're going to get this RW number through your previous Google sheet module select this one and then this is very important in the remaining time we want to give empty string if you remember the assistant it could have either remaining time or overdue days it cannot have both of them because we want it to play different script based on what data it has so here we are uh here this is what we are doing we are this uh part leads to overdue days that means we have an overdue day not a remaining day and uh and we can add an empty string in the remaining time and uh this variable in the overdue days this variable contains the difference of the days which are either due days or they are that is their remaining two the deadline so you can hit okay just removed one the we didn't need that and now we need to go and explore our second route which is the remaining days if the difference of the two dates is actually greater than or equal to zero that means we have we are remaining with due date it's not overd yet that means we can update that row and um we're going to have multiple rows to update again the row number comes through the uh Google sheet and then uh because it's uh this F leads to filling remaining dates uh we can add the difference variable here which will be the days that are remaining and then overdue this should be empty in this case and when we run it is going to update the rows update Row Two update Row three and five and then this is very important if it's the zero if the difference of the two days is exact zero then it means it is the last day it is the due day we need to call this day again so we're calling once it's five days to the deadline and once it's exactly zero and we are doing it here and now in the end we can when we're done with all this thing we can actually update the row two fals now if you're getting confused um to make this call happen we need to make this false remember if we have call status false then we can call them and if I want to call if I want to call when I have the deadline for someone I need to first turn that to false I need to turn the cus to false and then it's going to go and be able to call now this HTTP make request contains this web Hook when we go back to our make call we have this VB hook we can actually copy the address and go back to this scenario and paste it in here the method is post up when I hit okay when we in this scenario this HTTP make request is going to uh run this scenario whenever this scenario runs and we run this scenario once in once every day so let's see once every day this scenario is going to run and then it's going to trigger make call scenario and that's how it works so yeah we're updating the draw and we're updating it to fult so that we can call it again and now this actually estimates the time this gives us the difference of the two times and that's how we are able to tell if it's an overdue date or if it's uh or if there is time remaining so let me show you how it works um these dates are really messy looks normal what I have to do is to run this scenario and see if it can give me the exact remaining time and overdue days and let me hit this function the scenario ran and we should get okay here 6 days overdue 5 days 4 days 3 days 2 days one day here is zero days and it turn this into false so because it's the last day it should turn it into false so that it can call and the rest one two three four is the remaining days and yeah that's actually true so now we can go and make scenario for this one for the payment status when we hit paid it should change this month to 10 so we're appending a month on the deadline when we hit paid and when we say unpaid we're not going to say unpaid um if there is an overdue is going to make it unpad itself and to do that uh we have to go the uh the thing is um Google Sheets have its own add don't if you go to the extensions and then make for Google Sheets um I think you can find it in addons and then here you can find meg.com and then once you uh have it then you come and click on this make for Google Sheets and go into settings you can actually set up a web hook URL uh and then write the sheet name and then what range values do you want so by this I mean um whenever we have uh interaction with these range values the scenario is going to get triggered so if I do p unpaid uh and I select them as my range in this one I'm going to be able to trigger the scenario so this is J column and it goes from J2 upon J12 so I'm telling this one if from J one if up up until J 100 which goes down the way if there is any activity in this column trigger this scenario and the scenario is this one so we have copy you have to copy this web hook URL and paste it in here and that that way now we're connected with the Google Sheets so whenever there is an activity in that column this is going to get triggered and it's going to give us few things that we need this is the row that it has returned uh that was uh you know uh triggered and and so uh in the r this is the column this is the row and and uh the value has changed from unpaid to paid uh this is the information that we are getting so we can create a variable the purpose of this scenario is when they hit paid and the scenario gets run we will change this date to uh we will append one month on this date so if it's uh U 09 is going to become 10 if it's 10 is going to get 11 so we're changing the months we're adding 30 days to end it every time uh it's SPID so when we go back to our scenario uh we will first get uh get theur you know whatever current time that we have for so we will first get uh the current time that we have in this Google Sheets and we can do it through uh uh you know this web Hook is sending us that time which is this one January 30 2025 and the thing is that uh we want it to be to look like uh type of date that me.com can understand and and operate on it then actually me.com can add a month on it we need to par it when when we do par St uh and try to format it in this way then make.com will understand that it's a date then it can append 30 days or a month or one to three days however you want so in the next module uh I'm calling this end dat it means the new deadline I should call it the new deadline I have very bad naming practice so it should be called the new deadline um the new deadline is um the current time which is coming through this variable which is this one so the new time is going to be the current time added by one month so how we do it we do it through add months when you say when you say uh when you add a true format that uh function you can actually add months to your current time so when you say add months and then you add your uh date and then you write one it's going to write one it's going to add one month to it if you say add days it's going to add one day to it this is how it works and this is the type of format that I want and this is the output so it was um before it was January 30 now it is um so before it was January 30 now it's February 28 because February um the last date for February is 28 so it added to 28 and now uh we only want uh to update the database this we only want to update this column if it's hit FID let's say if someone has unid we're not going to change this to make it happen we will create a filter in the filter we're going to say if if this value if this value is equal to paid if this value equals to paid then can only update the row otherwise don't do it and we update the row by saying this is the row number row and row start you can use any one of them this is the row number and we want to add the new date that we just created and it's this one and now when I hit this when I run this scenario and I hit paid is going to change it to 10 there you go and yeah that's it pretty much I think we are done Rec created so do we have something else the last thing that we need to know how we are running this scenario this scenario only runs when we run this one which is just this just initiates the call so it again has the web hook for this one this this one and it just initiates the call and if you set it to like once a day uh every day and then it's going to run every day one time and it's going to trigger this call and then um it's going to run uh and then in and then uh it's going to run depending on how many uh rows we get so so this is the one that initiates the call and it has this URL from this web hook so what happens is we can set it to one run once a day so every day once it's going to run and then it's going to trigger this call and then if it's going to check if it needs to call somebody if there is if it meets all these conditions and it's going to make the call and uh this one also is going to run once a day not this one it's actually trigger from uh the PED so when we say ped it's going to trigger this one um and one thing more this one which is going to calculate the difference um between uh the timing so it's going to say if this guy has a remaining time of let's say this one has remaining time of zero days so tomorrow it should have remaining time of uh nothing is going to be an overdue for that purpose it needs to run every day and also so it's we can set it to run once every 24 hours and then it's going to calculate all the time differences and then um if there is like final day is going to call again and Set uh call status to zero but that's it that's how it works uh I hope you got some value from this one uh if you like watching this kind of videos you can subscribe to my channel I buil voice assistant