Comparing a SharePoint List Task ID column to a Planner Task ID

[roxy1]

Hi,

I am trying to create a flow that runs monthly that compares a SharePoint List Task ID with a Planner Task ID. Any that do not have a match I would like an email to be sent with the task ID and title of task.

The reason for this is that we want to do a check to make sure that any list item that has been run to automatically create a task, has not had the task accidentally deleted from the planner.  Or a task wasn't created directly onto the Planner - as all tasks come directly from new list items via an automated flow. Unfortunately even with staff training this occasionally happens!

My flow so far which does not work....!

(Please also note that the SharePoint list I mentioned does get updated automatically with the TaskID in a column when the task is created)

[grantjenkins · Accepted solution]

You'd need to check both ways. Planner Tasks that aren't in SharePoint, and SharePoint Tasks that aren't in Planner.

Below is how I'd do it where you get an HTML table for each scenario that you can email out. Note that I've just got a Manual trigger, but you would have your Schedule. I'll go through each of the actions.

List tasks and Get items return all the Planner tasks and SharePoint (SPO) tasks respectively.

Select Planner Tasks and Select SPO Tasks just get the properties you want. Note that they both need to have the same Properties including names so we can do our comparison across both later. You can add more properties, but you just need to ensure they are the same across both, including the order.

The Filter array actions are where we filter to show only the ones that are not included.

Filter array Planner tasks not in SPO uses Output from the Select Planner Tasks action for the From, and Output from the Select SPO Tasks in the comparison.

Filter array SPO tasks not in Planner users Output from the Select SPO Tasks action for the From, and Output from the Select Planner Tasks in the comparison.

They both use item() for the does not contain value.

Create HTML actions build up our tables with the relevant data so we can send it in our email.

Create HTML table Planner tasks not in SPO uses Filter array Planner tasks not in SPO.

Create HTML table SPO tasks not in Planner uses Filter array SPO tasks not in Planner.

Compose Table Style is just some CSS that we can use to style our Tables a bit nicer. You can adjust this to your liking. It's not required but does make the tables look nicer than the standard style.

Finally, we can use Send an email to send both the Tables with the Styling applied. I have the Output from the Compose Table Style first, then the appropriate HTML table Outputs.

The email output would look something like that below:

[roxy1]

 you so much @grantjenkins I really appreciate your time in helping with this, and your screenshots. I am having issues now filtering the tasks by created date but I will do a new post for this query!