Power Automate: Reminder Emails By Date [Power Platform Series - Ep. 16]

[Music] hello folks my name is matthew peterson trainer here at pragmatic works and as you saw in our intro video we do everything from an on-demand learning system that has over 70 different courses covering power automate excel azure sql we also do private training um hackathons virtual mentoring and we have this youtube channel where we post about two to three videos every week so if you're new to the channel make sure you subscribe to stay up to date on all free learning opportunities that we have here and in this video i'm bringing power automate to you for an issue that i actually did for a customer that i did a hackathon for so for a dental company they said hey we want to send out an email to our patients to just remind them your appointment is coming up in seven days and then a separate email to remind them three days out and give them extra information of hey here is the covid form that you have to fill out uh make sure you bring in your insurance card etc etc so how can we do this with one flow that sends different emails based on dates well let's take a look at how we can do it i'm not going to use their data i'm going to do a data set that you can easily create on your own if you want to follow along with me so with that being said let's take a look so i have a sharepoint list here which is the issue tracker sharepoint list this is a template list that you can make whenever you go to make a new list and what we have in the list is what the issue is a description of it who it's been assigned to to fix the date it was reported who logged the report as well as when do we feel like this should be done and this is going to be the big column we're going to use to send out these dynamic emails so once i have that kind of you know we look at what we've got let's go and make the flow so i'm over here at power automate i'm gonna do a scheduled cloudflow and i'm just gonna call this something like uh my youtube reminder email dynamic and i'm just going to put it you can put whatever schedule you want here and i'm just going to hit create so the first thing i need to do is capture the date three days from now so to do that i'm going to hit a new step and i'm going to use the action called initialize variable so as i search for initialize variable here once it comes up if i can spell it correctly apologies there so we want to do initialize variable and i always like to rename my steps so i'm going to hit the ellipses here initialize variable capture three days out date and then i'm gonna give my variable name var date three days out what's our data type for this variable it's going to be a string since we're using dates now for my value i'm going to write an expression to capture today's date but three days from today's date so the expression here to add days to a date is just simply called add dates you can see you've got hours minutes etc but we want add days then i use my open parentheses to start to make this active and the first thing it says is give me a timestamp alright my timestamp is today's state which is another function called utc now so this will capture today's date the next thing it says after you give it the timestamp put in a comma and now tell it how many days that you want to go forward which would be positive or backwards which would be a negative well i want to go three days forward then i have to choose the format and the format needs to match the format of my data type which is a two digit month a two digit day in a four digit year so to get that i do a single tick i do two digit month so two uppercase ends two digit day two lowercase d's and then four digit year and we've got four lowercase ys once i have that done i'm going to copy this out as well you'll see why in a second so i'm going to copy i'll hit ok so it ok puts the expression in the action but the reason i copied is on anytime i write expressions i like to put those notes that expression right here within my notes so i'm gonna just paste that in so it's a nice little reference here so i now have three days out now i need to do the same thing but for seven days and to make the process quicker instead of just redoing the whole thing i can hit the ellipses here and i can copy this to my clipboard it is a preview function so sometimes it doesn't work but it should here today so i'm now going to add an action but not underneath this is going to be a parallel branch which means it will run in tandem off to the side here so i'm going to hit add parallel branch i'm going to go to my clipboard and i'm going to copy what i had a second ago now notice it just puts the number two after the pasting well let's rename this so this is no longer my three days out this is going to be my seven days out and then all i have to do once i've done that is just modify my expression so my expression i'm also going to change the number up here so this is going to be my seven days out i'll click my expression and then right over here just change it from a three to a seven and then i'm just going to hit update and so now that updates my code and as you hover over we now see the 7 is there so that part's good and i'm also going to edit my note to make that modification that i also used 7 and not the 3. so let me zoom on in here okay so we're keeping track of everything so now that i have that done i'm going to come down and hit save and i want to make sure that my dates are being captured correctly before i move any further with the process so i like to always test my flows and baby steps here so i'm gonna do a quick test and make sure the correct dates are being returned so we can see three days out september six which is good today is the third that i'm recording and september 10th alright i now have the dates picture perfect now we need to use that date to do filtering of my records because i only want to return specific records so let's do our seven days out first so underneath seven days out i'm going to add in a new step another action and what i want to do is return my values or my records from that sharepoint list so i'm going to go get items i'm going to rename this so get items returned seven days uh estimated completion date record something like that i hook it up to my sharepoint list or sorry first do my site so i'm gonna do my power platform and then my list name which is my issue tracker and now i go into the advanced options where i go into the filter query so i can put in an odata filter now to get this filter to work correctly i have to give it the name of my column what condition i'm doing the filtering on but here the column name is really important with sharepoint so let me let me really explain this if i come on over to sharepoint list and take a look at what i have we all can see that it's called estimated completion date and there's spaces there but if i try to write my filter with spaces it's not going to work i have to get the exact um logical name as how sharepoint refers to it so just a little trick for you to know in the future on your list if you hit settings and you go to list settings and then you find your sharepoint column list that you want to do filtering on click the name of your column and then when you go up to the address bar at the top this is where you can see how it's being referred to so no spaces and this is the casing i need to use so i'm just going to copy this out that is the correct column name so now when i go back into the flow in my filter query i'm going to paste that in so estimated completion i didn't get the e so let me get that e there then eq for equals but there's more than just equals uh if you just type in odata filters you'll find tons of blogs on all the common ones that we use so i'm going to say equals and what do i need it to equal i need it to equal my variable bingo just like that so that's going to return my item seven days from now now i do the same exact process but i do it over here on my three days out now i'm not going to do the copy and paste because this is where sometimes it gives me issues so i am going to rewrite the whole thing so i'm going to do another add in action we're gonna do another get items and i'm not gonna do the full name here but i'm just gonna do seven days out or this one's gonna be three days out for my site address gonna hook it up to the exact same one same thing i did just a few moments ago so issue tracker gonna go into my filter query and this is going to be estimated completion date eq and this time it needs to equal three days from now so just like so so now i have hopefully my actions that are going to filter out my records so now on to the last step and this last step is saying well what do you want to do with these records that are returned in this case i'm just going to send out an email all right but we need to do it for both of them so to send out this email after my records have been filtered down i just go to whichever branch i want to do the email on first so i'm already on three days out so let me start it over here so i'm going to put in a new step here and oh not a new step because notice what it did it put the step inside of that parallel branch so i need to come over here and when i go insert new step i need to add an action right underneath of it and so i'm going to go with send an email to send an email and we'll go with our send an email v2 now for today i'm just sending it to myself so i can uh show you but i will show you how we can make this dynamic here in a second so technically i'm going to send it to myself but normally we would have sent that to if i come back to my sharepoint list we would technically send this email over to the person that this is assigned to so when i click on home here and i go back to my issue tracker again i would technically send this to the assigned to person but i'm just going to validate here with you all today sending it to myself so the subject something three days before completion and then for the body i'm gonna do something like deer let's put in to show you that you could have um access this assigned to person up here so my dynamic content i'm going to go to my assigned to column and i want to bring in their display name so dear alice and dear jonathan whoever now notice when i did that it now puts in apply to each because multiple records can be returned from this so each record is going to get its own individual email so what i code here is going to run through and do it to every single record that gets returned that's what an apply to each loop is going to do for us so now i can come on down into this email and just add more to it so i can go something like dear whatever reminder issue was assigned to you and the estimated completion date is again you can make this as fancy as you want i'm just going to do some quick stuff so you don't to watch too much of the video so i'll say the estimated completion date is our variable which is three days out and something like i'll put in the issue so the issue here so i'll go to my dynamic content my issue is being recorded in my title column i'll bring in something else like our issue details so i'm just coming on down here going to bring this one in which is my issue description column and now i'm going to do one more thing here i'm going to bring it in extra action if you believe you can't complete please contact your manager well we don't have the manager listed in that sharepoint list but what we can do is take advantage of another action to pull the person's manager in through our azure active directory so what i'm going to do before this email gets sent out i'm going to add in another action here so i'm going to click add an action and this action is going to be using our office 365 users which is basically saying let's get our manager so if i just type in git manager you can see that this is an office 365 users connection that allows me to pull in manager information so i'm going to go get manager now i have to tell it whose manager do i want well for the user principal name what i can now do is because in my sharepoint list we have the actual request or actually not requester this one is our assigned to person so i'm going to search for my assigned to and i want their email that's what's going to bring back manager so i need the assigned to person's email and this will return that person's manager all right but this needs to be done before i send the email because i want to bring that manager information into that email step so i'm just going to drag and drop the email step to be after manager now notice if i come into here i can now come back over please contact your manager comma and now in my dynamic content i have all the manager information so i want to get the manager's display name just like so and that sets up the first branch of our flow now i'm going to do the exact same thing over on the other side alright so i would come down here we've got the filter action going i'm not going to make the email as dynamic here so i'm just going to say let's add a new step so i'm going to add an action the first thing i want to do is get the manager so we're going to get our manager and then for our user principal name this again here is going to be our assigned to person so we need the assigned to email so that's going to get the person's manager notice it puts that apply to each in i'm just going to hit add an action we're going to send out this email so send an email v2 again i'm going to send it to myself but now you can see you could send it to the manager's email address you could send it to the assigned to etc i'm just going to call this this is my bomb seven days out so seven days out i'll go here again dear we'll put in that uh assigned to person again so the assigned to display name and again i'll be very generic here issue is seven days out and i'll bring in our variable and i'll just go issue [Music] bring this over here this is just for validation purposes so i want to bring in the title column first so title and then i want the description [Music] and then i'll do the manager as well and then we're gonna run this thing so issue description and then just so we know that the manager is being returned to come on over here and i want the display name from git manager just like so so now this is what we've got we now have two parallel branches so i should get multiple emails because we know that it's going to be september 3rd or sorry september 6th as well as our september uh 10th date and as you can see in my sharepoint list i've got two or actually one for september 6th and 2 for september 10th so when i come over here after it's done saving and i then run my flow i should get my emails that will then tell me if i have them done and if they're being returned correctly so i'm gonna hit test here i'm gonna go manually again this would be run on a schedule so i'm going to run the flow the flow run failed let's take a look it says the estimated completion date equals is not valid so why is this not working here so when we come on over a thing to note here i'm going to go into edit when i run this filter down here the reason it's not returning is due to this variable itself it needs to be treated within the filter like a string so it's missing a single tick before and a single tick after and that's also going to give me an issue over here as well now if this column was just a value column and not a string column that we're doing the filter on those single tick marks would not have needed to be there but for the filter to work i needed those single ticks so something i wanted to show because it typically will happen when you're running filters like why isn't it working you got to remember what your data types are so now once i save and we test it again now we're going to get the correct access coming through because we've now changed the way these data types are being filtered down all right let's test it so we're going to come up here hit test we're going to go manually again we're gonna run this flow and this time we should not have that error because we changed those single tick marks as we can see the flow is running so if we do our apply to each under get three items out notice there's only one email being sent because we only had one that was set for september 6. if i go to my applied to each for seven days out notice there are two emails that got sent because i have two now what do those emails look like well here we go let me bring it on over so this was my seven days out dear nate hallowell issue is seven days out issues leaky faucet description and boom there's his manager so this could have gone to the manager for that dynamic email rather than sending it just to myself or i could have put nate's email address in here so here's my other one just to always like to validate dear matt peterson because that was the other one for the september 10th i actually was the person who it was assigned to so just to show here september 10th there was the other one that i was assigned to and then finally did the three day out one get sent in three days out should be sent to allison here and yes it is dear allison three days out and so now what you can see is how you can use date filtering parallel branches one flow kicks off every day and it returns the correct filters down from those items so hopefully this is something helpful that will do something great in your business process save you time and with that i hope you enjoyed make sure you like subscribe and hopefully i'll see you around thanks so much [Music] [Music] you

Channel: Pragmatic Works

Views: 52,919

Keywords: pragmatic works, Matt Peterson, power automate expression, adddays, formatdatetime, utcnow, power automate filters, power automate managers, dynamic content, sharepoint, power automate data operations, power automate style email body, power automate, power automate how to, microsoft flow email, power automate emails, microsoft power automate, odata filter, Power Automate Emails, Power Automate How To, Reminder Emails By Date

Id: g_SqkW9ZCR0

Length: 19min 38sec (1178 seconds)

Published: Thu Sep 29 2022