How to create reminder notifications using Power Automate

hey everyone today i'll be showing you how to send notifications based off dates of things this could be items that need approval and have yet to be approved so you'd like to send an early notification to them to the approver that this should be approved this could be something like an event that is happening and you want to send notification on the event date or finally it could be a deadline that has been broken and the item has passed due and you want to send people notifications to complete that item so this is sort of the output of what we're going to get i have an order list and basically i want to send people notifications two days before the order date to approve of the order so that they may be shipped out this is my order list you can see here and today is october 24th and i would like all the items on october 22nd to be sent a notification to the approver which is myself so let's start making that flow so i go to power automate and here in the my flow section i will go and start a new flow from instant cloud i'm going to name this send approver notification email and we're going to manually trigger this flow great so the first step we're going to say is we're going to pull the relevant items from this order list that are two days before today because that's when i want to send it so let's use the get items action to get the items from sharepoint we're going to use to get items with an s instead of get item because we want all the items that are relevant and here type the sharepoint list name you can search for it this site address has been filled out now we get the list name which is order list and then in the advanced options we want to provide a filter query because this get items will just give us all the items in the sharepoint list but we want only the ones two days prior to today so we need to find the column so this is the column order date but you can see there's a space and you can't really use spaces in the filter query here we need the exact column name so we to get the exact column name we're going to go to settings at the top this little cog list settings find order date right here and if you look above you'll see the url has the field equals order date without a space sometimes they might have a percent sign like this it might be weird but you want to use exactly as it's shown here which is for me just order date let's go back settings order list and in our flow we will say order date and here we're going to put our filter query which is going to be i want the 22nd so today's the 24th so i want all items in 20 second so i want the order date to be greater than is it 10 21 20 21 so it has to be greater than 21st but end order date has to be less than 10 23 2021. so this means that it'll be greater than the 21st but less than 23rd meaning they can only be on the 22nd and now just to test out how many items we got here i'm going to do i'm going to count how many items we got so you can do that by using compose function and in the inputs we'll put the expression length and in brackets if you just click over here it won't get rid of this and then go inside the brackets with the value and hit ok this will give us the number of items this get item says pulled let's save let's test it out manually ok and you see here this error query failed because i did not put it in single quotes so let's go back put this in single quotes i believe you have to put it in single quotes the only thing it accepts is numbers like if it's if this was a number field i would say uh my price would be greater than a hundred and a half of quotes let's test this out again with the single quotes and there we go so the get items worked with this query and we got three items meaning it must be working because only three items here are on the 22nd so now let's make this dynamic because let's say i i run this tomorrow it's still going to use the 21st 23rd it won't use 22nd and 24th right so we want to make it dynamic based on today's date how do we do that is a very weird formula which i will put in the description of this video but basically we want to use greater than and the expression in the single quotes will be add dates utc now today's date and the day we want to add is you remember this was the 21st and today is the 24th so to get that i have to add negative three days so three days before so negative three and then the format will be capital mm lowercase dd slash yyyy 4ys you have to use capital mms because the lowercase mm stands for minutes there we go let me copy this formula and here is the 23rd and in here i will put the same formula but negative 1 because to get 23 just minus one day from 24th there we go and let's test this out again and remember to put single quotes between these functions if you don't put some quotes it'll break and you can very easily do that by going to the end putting a single quote and then doing another like that so let's test it out again and let's see if it works okay perfect we got same number of items and you can see here the filter query is the same so now we've made it dynamically based off today's date so now if i run this tomorrow this will be 22nd this will be 25th and this will pull me not items on the 22nd but on the 23rd okay and you can customize and play around with this add use different sort of dates to come up with whatever you want maybe three days from now maybe five days from now maybe you don't even need ad days maybe you just put zero days and you just want today's date meaning equals so then you would say order date if you just wanted today's date you would get rid of all this and put order date equals and then just put today's add day 0 and then it will put this and it would only get you all items on today but i want all items before two days from now this is what i'll run next we're going to send an email to the approver myself on all the items that need to be approved so we're going to use a send email action right here send email v2 send it to myself subject of the email will be the body will be and we need these items in a certain format to let's show over here so what we're going to do is called another action create html table from the value above get items and we can use the automatic columns however this will bring in all the columns such as id approval date approval quantity and even hidden columns if you go over here all these it is not so useful i prefer using custom columns and let's say i just want the name order price and the order date so i can do name of order price and date now you can see name order doesn't have spaces because it won't let me put spaces how you fix that is you click on header you go to expression and in single quotes put your title name order and there we go and the value we want if i go to dynamic contents will be by the search name order right there the price and the order date and there's my table and now if i go to send email i'm going to use is the i could just put the html table output this will give me the html table that i want and let's test this out first and there we go you can see there is my order list my order table however i want it sort of in this format as you can see so i'm going to paste the exact syntax or expression in the description for you guys but right now i'll just show you how to use it so you guys will go into the body and go to the expression and paste whatever i put in the chat and hit ok and as long as your title for the html table is called create html table this will work if not you can go inside of it and then sort of change the um over here where it says create html table to whatever it says over here for you guys and now let's test it again and there we go we got our new prettier looking table that the approver can review and final step because we don't want to run this manually every day ourselves we actually want it to run every day by itself and send an email on a certain time so i'm going to delete the trigger flow at the top and we're going to use something called schedule and this we can set up however many times it runs but usually we're going to run it every day because that's how this works so every day is a frequency and show advanced you can sort of show at these hours so depending on your time zone change the time zone up here so i'm going to say negative 5 eastern now this should be 9 am exactly so every 9 am let's say tomorrow it runs it's going to check is anything within past two days from the 25th which is 23rd and it will get nothing so it won't it will still send an email that's another problem we have so now we need to make sure that it only sends an email if we have uh items over here we have anything because if we don't return anything from the get items it shouldn't send the email because there will be no outstanding orders so let's add before this a condition and this will be is our compose the length get items is greater than zero if yes we drag this into the yes that's it now i'll test this out [Music] as you can see it ran it got three items it created the table it was greater than zero so it sent an email right up here so tomorrow it'll run and there's no items on the 24 23rd so it won't send any email thanks for watching i hope this video helped you understand the power of flow and how you can leverage it to automate your tasks get people notified on things work well with powerapps and sharepoint and all the other power platform tools and also i hope this video helps you understand more about the filter query and more technical side of power automate and how to leverage it in the future much better

Channel: Arslan Muhammad

Views: 26,715

Keywords:

Id: VMDRwhEiho4

Length: 13min 19sec (799 seconds)

Published: Sun Oct 24 2021