Scheduled Flow and SharePoint oData query - Using Recurrence to check for overdue approvals

in today's show we're going to do a scheduled flow to find our past due approvals so in a previous video we built an approval app not using flow to do the approvals but handling it all inside of powerapps and so now what we want to do is create a scheduled flow that runs once a day and checks to see do we have any approvals that have been outstanding for too long so it's going to require us to build our own data query and we're going to talk about some of the mechanics and introduce you guys to a bunch of little things that we haven't covered before should be fun but first here's our intro hi my name is shane young with powerapps911 those guys in today's show we're going to talk about scheduled flows right that's kind of what sent me down this path but what i realized was that in our approval app that we built back in june i think when we built that we kind of set up the approval process so in powerapps it generated those deep links sent people emails so they could then do the approvals but what i figured out was you know we've never went back and said hey how do i remind people if they don't do their approvals right you can come into the app they can see those but instead what i want to do is have a reminder that goes out hey if you have any approvals that are more than seven days old then we want to be able to notify you and you know nudge you along a little bit and these types of scheduled flows are very handy because a lot of times in flow you find yourself saying hey i want to have something happen every day at 8 am or have something happen every hour or on the first of every month right these different types of scenarios come up all the time so what i want to do is introduce you to that trigger and then once you get to see how easy that trigger is to use then we're going to go in we're going to be much more complex flow because what we're gonna do is we're gonna talk about how to use an odata query to only get the approvals back that we need to and then we're gonna send off those deep links so a lot of little things here but really the idea was to kind of expose you guys to that so let's switch over to my desktop and let's take a look over here on my desktop what we're going to do is run through the app real quick and then show you the pieces we're going to build right you always enjoy seeing what we're going to build and then we'll build it so over here this is the expense reapproval app we've built this over a course about three or four videos if you remind me i will send a link somewhere up here on screen here somewhere and so what we do is we go in and create a new expense approval right we're like hey flow demo when's the date what type of expense is it well clearly this is wasteful no it's not and then what we were doing in the last video is we automatically figured out who the approver was based on the amount of the expense and so like if i say hey i want to buy a new tesla plaid i think it's like 134 thousand dollars if we say save you're going to see that the approver changes to me haha so i can improve my own car i like this so we also have the ability to attach files we're not going to worry about that because none of that's the point but what's going to happen is we say save and so then now it creates the master items it creates the children items all in our different data sources and because i was the approver if we say my approvals you can see i've got two different approvals here the screen doesn't look very pretty as you can see i that's okay mechanically we went through all the process of it so you could see what it would go into and approve and reject we also when we said save back over here it sent off an email with a deep link which is a link to um this specific item so if someone gets that email which would be the approver then when they click on the link in the email here let me open the email one sec so right here you can see there's the link and if i click on the link what will happen is it'll open up the power app oh it'll ask me for permissions then it'll open up the power app right into the actual item that we just got created so we can approve it okay so it's called a deep link all that's covered in the other video and remember if you sign up at training.powerapps911.com the curated library you even download that fully working solution you don't have to build any of it so what we want to do today though is we send out these approval emails but one of the things that powerapps can't do is it can't just automatically remind people hey you have old things to do you know if the user comes in here to the app and says my approvals they can see them but you know before i think back to my last corporate job you know my boss he hated approving expense reports i don't know why i don't know if he got bonuses based on how long he strung out my money i don't know what it was he always had an excuse so what i would do today is i'm going to build a flow that reminds him anytime he has one that's more than seven days old and so the way we do that we go here to flow and we're going to use the reoccurrence trigger and so the reoccurrence trigger in our case we're going to build it that run once a day so just every day i can even say the time so i believe this is like at 10 a.m right i know my boss didn't ever came in very early so i want to remind him after he's in the office but so every morning at 10 a.m we're going to trigger this flow in the flow we're going to get all the things that are more than a week old and so what we're going to do is i did this compose step we're going to do it that way so that way we can see kind of how i do it we don't have to break it out this much we will and then i'm going to get all the items from sharepoint and we're going to use an odata query to do that and so we're going to say where all the approval sent is less than the output of this which was today minus 7 days then we're going to use this compose step here to do the length so that way we know how many records we got back and for every record we get back we're going to send an email off with that and so then that way when they do this you will get an email that has the thing says hey you need to update the status and so i've used this for customers quite a bit in the past and you know sometimes it's like all right we just remind them every day um you know from seven days on sometimes we'll be like hey remind them it's seven days 10 days 11 days 13 days and then every day you know only remind them if their title is not ceo because we can't nag the ceo so we have these different types of controls but uh you know we're going to not get into that level of granularity but you can if you need to so how do we build this thing well what we're going to do is i'm going to say create and we're going to say i want to create a scheduled flow and so we're going to click on schedule flow now keep in mind that this is showing you how to do it from uh flow studio or power audit studio whatever we call this thing but if you were over in project oakdale what would that look like well here let's switch over look at what that would look like real quick to get us both the same screen so no matter how you need to do it you can see it one sec okay so over in project oakdale and teams right i went over i went to the powerapps tab because you're going to build your flows from there you'd click on build you would then figure out which environment you needed to tie this to so where was your approvals coming from and so then what i'm going to do is we're going to find that environment so i'll just do my playing one i'll say see all and so then now that you're here you would do new flow and scheduled flow and you get the same editor right so so whether whichever way you're coming in either one of us we can do the same exact thing so i'm gonna make that very clear but we'll get rid of that one we'll do it over here and so we'll give this a flow we'll just call this the nag flow all right and so then you can see they give us a cute little wizard so when you want to run this flow i'm we'll start it tomorrow morning so that'll be the fifth and i'm going to start it and i did the last one at 10 a.m so i'll make this one at 9am so it's different and so then now it's like hey how often do you want me to repeat and once a minute once an hour once a day once a week once a month one second so you can do these i'm going to just do once a day um now keep in mind that you know you do have a flow run limit so if you do like have this email go out once every five minutes which would make no sense in the scenario but you could you're going to use a lot more flow capacity and microsoft's getting better at uh you know stopping you so if you run and use too much capacity you know yelling at you so just so you guys are kind of heads up like one of my customers ran out of capacity from writing to me flows so we had to buy a license no big deal all right and that's what it's meant to do but things like having a reoccurrence flow run every five minutes it's going to run you out of capacity a lot faster than having this flow run once a day so we'll say create and so there you go it's built on our flow we can expand it if we wanted runs every day and we do edit and so they do a pretty good job here but you can come in here and you can mess with this thing all you want um so if you really want to get down and tune into those different values specific times i've never found like an actual business case for that i'm sure you guys have got them so i wanted to make sure you at least knew they were there because remember we also we just always click on show advanced options see what happens all right so then now i need to get all of the items that match the criteria so the first thing i do is i'm going to say new step and so in this case sharepoint is my data source but remember nothing about this is sharepoint specific necessarily what it's really about is just finding a way to get your data set so i'm going to drill into my sharepoint list so it's on powerapps videos and my list name is called expense master there it is and so then now there's a limited entries nested items i don't need any of that but what i am going to need is i'm going to need to do an odata filter okay because one of the most common mistakes i see i see thousands of these flows built and one of the most common things i see happen here is that people say hey i'm just going to get all the items and then i'll write an easy flow condition to check for those well what happens you know in the case of this list i think it has 88 89 100 ish items in it so i would have to go fetch that entire payload right 100 items that's a bigger payload and then i would come over here and i do a loop and loop through all 100 items and so i might make my flow take 10 minutes just to find the ones that match my qu my thing in the case of like um sharepoint or cds or you know sql right there's different query uh ways that we can you know send over the query we want to do and only get the records back we want so that way we're processing less info right making the flow as lightweight as possible which means as fast as possible so to that end in the sharepoint world i'm gonna have to use what's called an odata query and so odata if you haven't seen it before this is this terrible language i've been doing it for 15 years and i still hate it and i still have to look up how to do it all the time but the idea here is that you can query for a column and so if i look over my over here maybe one of the columns i want is you know expense status of submit submitted ugh so hard to say i think the other video i messed up saying submitted to apparently i don't like that word so what you can do though is you can be like all right i want expense status like this now you're not gonna do an equal symbol don't do that you have to use the word so or the you know like letters i don't know eq for equals so you'd say expense status equals and then it goes in a single quote and in our case we want to do submitted now and then close my single quote keep in mind that these things are very case sensitive and they are very obnoxious they like what they like and they don't like anything else so just you're gonna have to take your time with this if you haven't done odata queries before but i think that one will look good now the other thing i would warn you is i'm gonna go ahead and say get items um i'm gonna just test this thing as simply as possible i'm not going to try and write the whole flow i never do i am going to add another compose step down here though and so the idea of this compose step is it's going to give us we're going to use the expression called length which counts the number of items that get returned so length like that and then once it gives me the little thing we can click on dynamic content and say value and then say okay and so that's going to just count the number of records we get back kind of count rows over in powerapps but now we know how many we get back so that looks good we'll say save and now it's saved we'll do a test and then we'll perform the trigger action ourselves we'll say test again and then run the flow done and so now it ran very quickly right which is what you're after you want this to run fast and so we got items it looks like that was happy and we do compose and we got 10 and if i go over here to this thing and say hey show me all the things and maybe i'll just say you know filter by and submit it apply one two three four five six seven eight nine ten so we know we got the right data set back this is super important right take your time because especially if you're new to odata queries you're going to want to make sure that you you know have a little bit of you know a little bit of practice there and so now that we've done that now we say okay well i want expense status equal submitted because that's what we do when they are not sent and so then i cheated and i added a column earlier called approval sent so when did i send this approval and i've only updated it here in one field quick edit the other record we just made so i need to go change my power app to update this field automatically but we'll just change this one this one was sent on 10 4. oh i guess i just click on huh that'd be a lot easier there you go quick edit mode exit okay so we got two items with an approval sent so if we ran this tomorrow monday morning well this one will not be so too old so i'm not too worried about that one but i want this one to trigger so back to my flow what we're going to do is we'll say hey i want expense status equals submitted and now once again this is a common mistake i make because in powerapps and is spelled with a capital a and o data it is lowercase a so expenses submitted and and so then now it's like all right and what so i look at the column name approval sent so approval sent remember getting all the capitalization and all that and then we're gonna do equal sign or not equal sign equal the word and so then i know that the format it wants is uh 220 09 i don't want to just do oh let's do 10. so 10 01. now so that would only get the ones that were equal to o10 so what i really want is if they are less than so i would just do lt so anything less than 1001. all right i don't know if that's going to work i probably got a typo but whatever was i test we'll just use our last run save and test hey it didn't fail that's kind of exciting go compose it got one yeah now if you want to make sure you got the right one you could click get items and then click the download i'm not going to do that because i know it got the right item but very cool right we wrote a complex odata query now you're saying well shane that's hard coded to october 1st that's fair so now we need to make this portion dynamic so to do that i'm going to step back up here and this is where i'm going to add a compose now you do not have to do this once you understand how it works but this makes my life easier troubleshooting and so i actually found a new expression today i had never used google expressions i don't know if it's brand new or if it's just i'm the first time i found it but under the date and time functions we said see more and there is something called get past time because what i was trying to do was subtract from time but that was pain in the butt get past time this says returns a time stamp that is the current time minus the specified interval oh so what if i said get past time and so in my case i just want to do one comma week and then i also know that i need it back in a very specific format and so i'm going to put that format right here and we go 2020 oh that's not what you want i want yyy for year dash and then weirdly enough it is capital m capital m right lowercase m's are minutes uppercase m's are month and then lowercase lowercase d and so we'll do that now i'm not even going to bother changing this right because i don't want to mess with that yet it it's good so we're just going to say test test again and so look it's done all right so let's go and expand it and look at that it returns 9 27 which if you get out of calendar is seven days ago from 10-4 right sunday afternoon my bengals just won i think i don't know i didn't watch football i watch my kids play sports whatever anyway i'm making this on sunday night for you guys i like you a lot i know but so you can see that gets us exactly what we want so now what i'm going to do is edit again go down here to my get items and so right here we're going to replace this and this is why i mean this is literally the way i built it this afternoon trying to figure out how to show you guys this right i i do it with hard-coded data first to make sure i understand mechanically that it does what i want and now that i know and i know this spits out the right thing we're just going to replace all that make sure you see the little single quote is still right there and that single quote is there that sometimes those get deleted and if they do it won't work but that looks good so then now we can say test again and in theory we should get all the records that match which in our case would just be the one hopefully so fingers crossed it is successful compose says we got one record what look at that folks i i did that without screwing up without a single typo i'm kind of shocked i made a lot of typos when i was practicing earlier um but yeah so expense status equals submitted and approval less than um the date that just got calculated now if you wanted there's nothing stopping you from coming up here and just grabbing this ctrl c and so then you could come down here and you could say expression you could paste this in so now the expression's there i make sure that my single quotes are there we're gonna be brave we're gonna be real brave we're gonna delete this because this is what you would do in a production scenario and so now we can say test and one more time we will send this off to the tester okay and it looks like it was happy and we got one remember you can also go here to get items and you if you say show more you can see that that is the formula that we wanted it to be sorry i apologize for the happy dance okay so we've got a lot of the hard stuff done now the simple thing so what i'm going to do is i'm going to hear i want to email these people so if i go here and i say new step i'm going to type in outlook and then we'll just do like send look at that send an email v2 who do i want to send it to well i don't want to send it to a hard-coded person i want to send it to the approver that needs to do his or her job so add dynamic content scroll over here so you can see it because flow's annoying and so there's approver email right there so that's the email column over here right chewie shane daniel ombuss oh not there over here so approve your email all right now when i did that notice that it automatically created this apply to each loop right and this is because flow is smart flow said hey when you didn't get items that's going to give us back a table of data it might only give us one record back but if it only gives us one it's still a table so i have to loop through that table because some days you might have one approver this past due so maybe we have 27 this is going to be resilient to that process and so what we're going to do now is we're going to send an email to that person and so for the subject we might say you forgot to do your approval and so we could come over here grab some dynamic data you guys have seen that before we're not too worried about it but that is possible and so then now the last little tricky part so in the body i don't want to just send them a nudge i want to send them a nudge with a link to get them back in there and so to do that we're gonna go back to our power app for a second because over here in our power app in the last video we figured out how to create this deep link right this is the link back to the app that we want to send them to and then we put in that query string parameter right called mid equals and then the id of the record that we want to attach them to so i'm just going to copy up through the url so ctrl c and we'll go over here to our flow and we're going to say here is the link open this here is the i'm going to click on the little winky symbol or go away dynamic content oh well dynamic contents will be a pain in the butt and so for the link title i'm just going to type in link i'm lazy and link target we're going to paste in the whole thing and we're going to say add now if we hover oh it's not going to let us okay so now we're going to do is we're going to take advantage of the little show code here and so that shows us the entire code now remember this link is not the same for you this link is for my specific app yours will be different but in the video we talked about making mid for uh i don't know why i called it mid quite frankly but whatever i called it mid for equals and we need to add right here the id of the record that we want to do and so what we need to do now is we need a dynamic content we're going to type in id that's going to get the sharepoint list item id the same way it got the approver email but so then now this should be a here is the word link but it should be a link to this app with a parameter of mid equals that all right that seems pretty cool so let's just test it so hit save oh one that was saving chewie was barking hopefully make it through the video if it did i apologize but hopefully edited it out anyway so then now that this is saved now we can do our test and we'll test again and so now the flow should be off and running but what should happen this time is it should get the item we expect we should just get that one and it should send an email all right to me because i was the approver right and it's not it's because i am literally the approver if chewie had been the approver he would have gotten it but that looks good so now i'm gonna open my email one sec and look you forgot to do your approval and so if we hover here's the link oh i see that mid equals 102. so let's click it we click the link and so remember that's called a deep link in powerapps terms we say hello and so then this is going to drop us into the powerapp boom it is ready to do the item that is past due so then now i can approve reject i can add my comments and write all the powerapps functionality takes over but flow this fun little flow that we just wrote does all the work for us so every day we're going to check we're only going to get the items that are needed to be uh notified right do not get all the items or your flow will be slow and you will be angry just get the ones that need to be checked and so like with sql we can just do a sql query i believe you use odata filters with uh the common data service or oakdale so you know do the right thing to get your just the items you want this compost step completely unnecessary but helped me troubleshoot so i kept it and then down here we're just going to loop through and for every record we get back then we're going to send an email so there you go guys girls ladies gents kids of all ages you have got a flow so hopefully that helps you right that gets you thinking about how to do not only this scenario but all the different scenarios you have um you know as a reminder you can download the app that i just showed you you can download this flow that we just built all of that is all available if you sign up at training.powerapps911.com for the curated library you know you get a personal email from me right everybody's doing an email here an hour like hey here's the link to the new video why i made it and some details they can download and watch it ad free all that fun stuff have any thoughts comments leave me notes below i'm actually all caught up on comments yay um which is unusual i'm usually about a week behind but i do reply to all of them just about i miss some here and there um if you want to work together hit me up at powerapps911 blah blah blah blah blah blah all right it has been a long day of kids sporting events and professional sporting events and dogs barking who's now laying there quietly of course um so that i'm just gonna say thanks and have a great day before you go be sure to click on the subscribe button over here so that way you'll be notified when new videos come out if you need any help or you want to work together whether your problem is big or small check us out at powerapps911 we do it all i rhymed or if you're looking for more formal training offerings we have those linked up here somewhere so check them out thanks and have a great day

Channel: Shane Young

Views: 22,149

Keywords: Shane Young, powerapps911, PowerApps, Power Apps, power automate tutorial, scheduled flow in power automate, flow recurrence, flow recurrence trigger, recurrence microsoft flow, power automate recurrence, power automate recurrence trigger, jon levesque, flow, sharepoint odata, sharepoint get items filter query microsoft flow, sharepoint get items flow, power automate get items from sharepoint list, microsoft flow get items sharepoint list, flow past due, flow approvals

Id: qGqqQjGMun8

Length: 25min 4sec (1504 seconds)

Published: Mon Oct 05 2020