Power Automate: Compare two lists and find what's missing

what's up everyone April here in this video we're going to talk about power automates and we'll look at how we can use power automate to send reminders if someone hasn't added something to a list for a given time period this actually came about from a project I've been working on recently and as I was building the flow I realized this might help a lot of people who need to do something similar it shows some more intermediate to Advanced power automate Concepts like using arrays and comparing arrays but this is applicable for so many different use cases really any case where you want to compare one list with another and then perform some kind of action so let's take a look at the use case and how it works thank you so in the scenario that I'm going to show today I'm tracking items on a SharePoint list every week everyone in my team has to put in an item that they're going to post to tick tock for the week so by the end of every Friday they should have an item in the list with a draft due date for the next week reason the content scheduler list template and you can see that several people on my team have different items in this list but sometimes we forget to put things in a list and that leaves us scrambling at the last minute to think about what we want to put in so you want to avoid that with some proactive notifications and that's where power automate comes in I created this flow to send out an automatic notification every Friday morning if someone doesn't have an item in the list with a due date for the following week so let's break down every piece of this flow now since I want this to run weekly I've did what's called a recurring flow so if we go to make.powerautomates.com and click on the my flows tab on the left hand side and select new flow you'll choose this scheduled cloudflow option which allows us to run flows on a schedule when we do that we can configure this trigger to specify the time zone start time and what days we want this to run so I have mine set to run every Friday at nine o'clock so I know I'm going to want to get the information for my SharePoint list but I want to filter it so it only Returns the items for the following week so to do that I'll need to do some date time comparisons and so make this daytime comparison easy I've added in the daytime connector and use the get future time action so in my case I want to get seven days from the time the flow executes which will be on Friday so I put in seven for the interval and I chose day for the time unit so at the point in time in which this flow runs this will get the value that's seven days from that runtime then I can use this in my call to SharePoint to get the items and filter it that leads to the next step I added a get items action from the SharePoint connector I pointed it to my SharePoint site and the content scheduler list and here is where I had to do that query now since I only want to return items for the next week in the filter query I'm going to put in my draft do buy column which is the column that I want to trigger off of I'm going to say Le for less than or equal to and now we're going to use our Dynamic content in the get future time action that we just added the important thing here is you want to wrap this in single quotes as you see that I did here otherwise you'll get an error so what this will do for us is look at the SharePoint list take a look at that draft do by date and if that isn't less than or equal to seven days from the current date then it won't get the items so we did this future time action so we can do this calculation to where it is only going to get items up until a week from the date that it runs now that we have our data we need to do some manipulation of that data and that's why you see I have three initialize variable actions added here the first one is an array variable and I'm calling this VAR all Advocates so for my scenario I want to give everyone that reports to me and put it in this array then I'm going to compare that to the values that we have in the SharePoint list and if someone on the team doesn't have an item in that SharePoint list for next week then we know we need to send them a notification so that's what this array will be storing the list of all people on my team the next array will store the outputs of that get item so the people that do have items in that list and finally we need an array to put the list of people that don't have an item in the list for the next week now that we have that it's time to start populating those arrays this piece may vary based on your use case but for me I want to get my direct reports and that's what I'll put in that all Advocates array and to do that there's an action in The Office 365 users connector called get direct reports you simply put in someone's email address and it will get all their direct reports for you now that returns a list of objects so what we need to do next is go to add action and add and apply to each and this apply to each we'll go to our Dynamic content and we'll point it to the value from that officer 65 users get direct reports now we need to take all these values that it finds for my direct reports and do what's called appending that to our array so to do that within this apply to each we can add an action search for variable and you'll see an option to append to array variable we'll get a drop down list and we'll point that to the all Advocates variable that stores everyone on my team and then I'm going to store their email address so just use our Dynamic content again and in that get direct reports action we have a mail field this is another optional step for these notifications I also want to check if I have an item in there so get direct reports isn't going to return myself as the manager so I'm just simply appending another item with my email in there now it's time to move on to the contents of our SharePoint list so we have another apply to each here and for the output of this one we're going to point it to the value of our get list items action and you guessed it inside of this apply to each We'll add another append to array action but this time we'll point it to The Advocates with tick tocks because this one is for everyone that has an item in the list for next week and for the value we'll point that to the Creator email so this is directly from our SharePoint list we're going to get their email address and put that in that array so so far we've gotten all of my direct reports that we want to check to see if they have items we connected to our SharePoint list filtered it so it only shows values up until the following week and we've looped through all those objects and added everyone's email that has an item in that list now we need to compare the two to do that we'll do another apply to each for the output of this one this is where we're going to start using those arrays we set up will point it to the array for all Advocates and now inside of this array we're going to do a condition this is where the comparison happens so on the condition on the value on the left hand side we'll point that to the array of Advocates with tick tocks so people that had items in the list we're going to check if that array contains the current item so you're going to look at our Loop through all Advocates array and our Dynamic content and use that current item property so it'll go through all of the people on my team and check to see if each person has an item in that SharePoint list if they're not in there then we know we need to send them a reminder so in this no condition We'll add another append to variable pointed to The Advocates with no Tick Tock variable in the current item now this step here in this whole array for the people with no Tick Tock is optional depending on your use case I'm not actually using it in my scenario but I wanted to show it because there might be circumstances where you'd want to have those values in an array namely if you want to send a mass email to everyone at the same time reminding them to submit their items so if you have that information all in an array it's easily accessible and you can just concatenate all that in your to Field of your email or your team's action and send that all at once I'm not doing that in this particular case but that's the reason why I show creating this other variable what I'm doing instead is I want to send individual notifications so I've added a post a message in a chat or Channel action for teams this doesn't have to be teams it could be an email or however you want to notify people I'm posting this one as the flow bot in a chat and I'm pointing it to the current item of this particular Loop which is the email address of the person that doesn't have an item in the list and I'm just simply telling them that there's some action required they need to go put in an item in the list with a link to my content scheduler list so now we can do a manual test just to see how the process would work so we'll run this flow so I'll imagine it's Friday and we're getting that automatic notification so we see it's running getting my direct reports looping through the content items and now it's just doing that comparison on this last step here so you didn't take that long at all it ran successfully so if we look I don't have an item for myself for next week so I should have gotten notification in teams and if I go over to teams there it is so there's my notification letting me know that I need to put in an item I click on a link and it takes me to my SharePoint list and that's really all there is to it there's a few steps involved but overall hopefully it's pretty straightforward do you have any scenarios for how you might use this in your workflows if so drop a note in the comments and let me know how you think you might use it so lots of little interesting tidbits and things that we can learn about power automate from this especially if you're just getting started hopefully you'll find a good use case for it if you find my content helpful do me a favor and support the channel by clicking that subscribe button thanks so much for watching and I'll see you in the next video before you go I have lots of power automate content check out some of these other videos [Music]

Channel: April Dunnam

Views: 10,829

Keywords: Power Automate Compare Lists, Power Automate Compare, Power Automate Array, Power Automate, Power Automate SharePoint, Power Automate Compare Array, April Dunnam, April Dunnam Power Automate, compare lists, create variable, power platform, microsoft flow, microsoft power automate, power automate tutorial, power automate tutorial for beginners, Power Automate Reminder, Power Automate Notification

Id: G0GEdtn83pU

Length: 9min 28sec (568 seconds)

Published: Thu Aug 17 2023