Display Name of users who are assigned to incomplete tasks

[roxy1]

Hi,

I've followed the solution in this post by @v-bacao-msft almost successfully but hoping for some help. I have set up a flow to filter tasks that had a start date of the previous month, that have not been completed and that are assigned to someone. It then emails a table to the manager of the Start Date, Task Name, Task URL and Assigned to,

Unfortunately due to the 'Apply to each loop' the 'Assigned to' variable ends up having every task having all the assigned users for the various tasks put on every task!

My flow filters through the various arrays so I am not sure how to avoid this.

As you can see the output of the variable si correct, however, when I use the variable output in the HTML table is has more than one name (it has used every users name assigned to all the tasks it filtered and added them all together):

[v-liwei-msft]

Hi @roxy1 

You overlooked a step of nulling the variable (Assigned to).
You can add a step to empty the variable at the end of Apply to each 2.

Best Regards,

Levi

[roxy1]

Hi Levi @v-liwei-msft ,

Thanks for your assistance!

I'm not quite understanding (also as I don't have a variable for the table), when I add a step to empty the variable in the 'Apply to each 2', it just clears the assigned to and therefore the output becomes blank for the HTML table:

Kind regards,

Roxy

[roxy1 · Accepted solution]

I've managed to get it working thanks to finally stumbling across this blog: https://tomriha.com/how-to-display-assigned-users-in-planner-task-with-power-automate/

It seems a bit of a longer way but it does work!

[v-liwei-msft]

Hi @roxy1 

Glad to see you solved the problem. 

You could mark your answer/solution as a 'Solution' to allow more people with the same problem to solve their own problem as quickly as possible. 

Thank you so much! 

Best Regards,

Levi

[grantjenkins]

This is probably how I would have built the flow.

See full flow below. I'll go into each of the actions.

List tasks retrieves my Planner tasks.

Filter array applies three conditions as specified. Active tasks only; Start date last month; and at least one assignee. See expression below. Note that this requires you edit in advanced mode.

@And(
  not(equals(item()?['percentComplete'], 100)),
  greater(length(item()?['_assignments']), 0),
  equals(formatDateTime(item()?['startDateTime'], 'yyyy-MM'), addToTime(utcNow(), -1, 'month', 'yyyy-MM'))
)

Initialize variable users creates an array variable called users that will be used to hold the Display Names.

Apply to each Task will iterate over each of the tasks from the Filter array.

Apply to each Assigned iterates over each of the assignments within the current task. For each assignment it gets the User Profile then appends the Display Name to the users array. The expression used to get the userId for each assignment is:

items('Apply_to_each_Assigned')?['userId']

Compose Task takes our data and builds up our objects. Below are the expressions used for each property. Note that for Task URL I've put an actual hyperlink, so we get a nice text link (Link to Task) and not the entire URL.

//Start Date
formatDateTime(items('Apply_to_each_Task')?['startDateTime'], 'dd MMM, yyyy')

//Task Name
items('Apply_to_each_Task')?['title']

//Task URL
concat('<a href="https://tasks.office.com/happywolf.onmicrosoft.com/home/task/', items('Apply_to_each_Task')?['id'], '?Type=TaskLink&Channel=Link">Link to Task</a>')

//Assigned To
join(variables('users'), ', ')

The full object, if you just wanted to paste it into the Compose Task, is:

{
"Start Date": @{formatDateTime(items('Apply_to_each_Task')?['startDateTime'], 'dd MMM, yyyy')},
"Task Name": @{items('Apply_to_each_Task')?['title']},
"Task URL": "@{concat('<a href="https://tasks.office.com/happywolf.onmicrosoft.com/home/task/', items('Apply_to_each_Task')?['id'], '?Type=TaskLink&Channel=Link">Link to Task</a>')}",
"Assigned To": @{join(variables('users'), ', ')}
}

Set variable users then clears out the Display Names ready for the next task. We set it to an empty array []

Create HTML table takes the output from Compose Task. The expression to get the output is:

outputs('Compose_Task')

To fix our hyperlink in our HTML table for the Task URL link to each task, we need to take the output from Create HTML table and replace a few elements. This is because of the way Power Automate treats some characters. The expression used in our Compose HTML table is:

replace(replace(replace(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&quot;','"')

Compose Style adds some styling (CSS) so our HTML table looks nice when we send the email. The CSS for this example is below:

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 5px 10px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Send an email uses the output from both the Compose Style and Compose HTML table.

The final output of the email in my example would look like that below. Note that the Task URL is a hyperlink that will take you to the actual task.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[roxy1]

Whoa thanks @grantjenkins ! The work you put into answering in such clear detail is incredible. Thank you!