Creating an automated flow within AzureDevOps

[ntailor]

Hi there, 

I have been browsing the forums and trying to find threads and conversations that might assist me in building my flow, but I haven't been able to find the information to help me specifically.

Goal: I want to build a flow that automatically sends an email to users when their work items in AzureDevOps becomes due.

The flow (in my mind):

(1) Get Query Results from ADO
(2) List all the work items from the Query Results that are due before a specific date
(3) Retrieve all the unique users (no repetitions) that are listed on this result from the "Assigned To" column. 
(4) Send an email to each user from the "Assigned To" column that lists all the work items assigned to them that are coming due. 

I don't know if this is possible, but if someone can assist in guiding me in the right direction. I have been having trouble to only sending the work items to a specific user from a query. 

[Expiscornovus]

Hi @ntailor,

For items due before a specific date you can add a clause for a target date field in your query and compare it with the '@today' macro and add for example 14 days to that macro.

1. For the Unique users you can use a Select action.

a. Use the value field in the From.

b. Switch the Map field to text mode with the button on the right side

c. Add the Assigned to field to the Map field.

2. After that you can get the unique users by using an expression with the union function. In an apply to each you can loop through those unique users and filter for their due items via an filter array action.

a. Use the below expression

union(body('Select'), body('Select'))

b. Add a Filter Array action. 

c. Use the value in the From

d. Switch the where to advanced mode and add the below expression

@equals(item()?['System.AssignedTo'], items('Apply_to_each'))

e. Add a Create HTML table, use the outputs of the Filter Array

f. Add a Send an Email (v2), use the below expression to get the email address from the assigned To field:

first(split(last(split(items('Apply_to_each'), '<')), '>'))

[ntailor]

@Expiscornovus  Thanks so much for your reply. I do have a few questions as I was able to follow your instructions, but when I tested it out, it didn't seem to send the email as intended.

The first part of the instructions were simple and is as below:

However, the second part, under the Create HTML action, I was unable to select the "Output" from the Filter Array as it only gave me the "item" and "body" options, so I chose the "Output" from the Select action.

and then the final part of the flow didn't seem to have any issues

But for some reason, when I run the flow, it runs successfully but no emails are being sent. I have a feeling there is an issue here

Because it runs through this and then skips the Apply to each loop altogether

Let me know if you have a reason why the Select action may not be functioning properly.

[ntailor]

@Expiscornovus Update:

I ran it again and this error popped up. I have a feel it is occurring cause the output is coming from Select and not the Filter Array. 

[ntailor]

@Expiscornovus 

Sorry for the multiple tags, I was able to fix the error, I used the "Body" from the Filter Array action instead and the flow ended up working as intended. Thanks a lot!

The only thing I wonder if you know how to do, is to include the links in the HTML table? When the email comes through it doesn't have the embedded links to take the recipients directly to the Action Item in ADO. When i searched how to include the links, they all involve creating a HTML table using the custom method but i don't think that works with the flow we have here? Same with the headers? I don't believe I can create custom headers instead of the automatically generated ones?