Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

[yashag2255]

Currently, we do not have an action to create/ update or pass parameters to a query in Microsoft Flow. A work around for such a situation can be achieved using the “Send an HTTP Request to Azure DevOps” action from MS Flow. Also, there is no staright off way to get the work items related to another work item.

A work around for such a situation can be achieved using the “Send an HTTP Request to Azure DevOps” action from MS Flow.

Let us take a scenario where a user (Team Lead) has been assigned with a work item in Azure DevOps. The user wants to:

1. Assign the work items based on the type:
   • If it is of type Task, assign it to user1
   • If it is of type Bug`, assign it to developer1
2. If there are no related work items/ child items, create a new child work item “Review Task” of type “Review” and assign it to tester1
3. Get a brief detail on the assigned work item and the child / related work items of the currently assigned work item.

Have a look at the screenshots and create the flow step by step to effectively automate the above scenario.

Trigger: “When a Work Item is Assigned in Azure DevOps”

Action: “Send an HTTP Request to Azure DevOps”

Appropriately populate all the fields. Refer to the code below: choose “ID” from the dynamic content tab in place of the highlighted area. (Refer to the screenshot of the flow)

Body: {  
"name" : "TestNew",
"queryType" : "tree",
"wiql" : "SELECT [System.Id],[System.Title],[System.State],[System.WorkItemType],[System.AssignedTo] FROM workitemLinks WHERE ( [Source].[System.Id] = @{triggerBody()?['id']}) AND ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') AND ( [Target].[System.WorkItemType] <> '') MODE (Recursive)"
}

Action: “Initialize a String Variable”

Value: body('Send_an_HTTP_request_to_Azure_DevOps')['id']

Action: “Get Query Results from Azure DevOps”, Query Id: variable created in the previous stepAction: “Apply to each control” Select the list of values from the query results of the previous step''

//Apply to each loop Starts

Within the loop, add a condition:

Action: Condition: to check if there are any related/ child items.

Expression: length(body('Get_query_results')?['value']) 

1.a. If yes,

Action: Condition: to check if the work item type is a “Task”

Expression: @items('Apply_to_each_2')?['System.WorkItemType'] 

1.b. If Yes,

Action: “Update Work Item in Azure DevOps” -> Select the current work item ID and in the Other Fields input, Add-> System.AssignedTo as the key and the user (user1) as value.

1.b. If No,

Action: Condition This time check if the value for work item type is “Bug”

1.c. If Yes,

Action: “Update Work Item in Azure DevOps” -> Select the current work item ID and in the OtherFields input, Add-> System.AssignedTo as the key and the user (developer1) as value.

1.a. If No,

Action: “Create a Work Item in Azure DevOps” -> Select all the relevant values and in the Assigned to field, add the user (tester 1)

//Apply to Each loop ends

Action: “Get Query Results from Azure DevOps” Select the variable created above for the ID

Action: “Create a HTML table” Select the value of “Get Query Results from Azure DevOps” and set the “Include Headers” option in advanced settings to Yes.

Action: “Send an Outlook Email” Enter the email address and modify the subject line. In the body, add the “Output” of the “Create an HTML table” action and select the option “IsHTML” as true.

Note 1: “yashTest” is the name of the project that I have created and this need to be replaced with your project name.

Note 2: The tricky part here is to get the related/ child work items of the assigned work item. I created a new query using the “Send an HTTP Request to Azure DevOps” with input parameters from the assigned work item and executed that query using the “Get Query Results” action.

This query has a dynamic parameter that can be updated (the entire query can be modified to include dynamic filter criteria based on any preceding actions defined)

Note 3: I used the HTTP POST type request to create this query in the “My Queries” folder of Azure DevOps. I have used a static name to create the query and you can parameterise that by initialising a variable and populating that with dynamic values. A work around to this could be that you first create an empty query in Azure DevOps and then use the HTTP PATCH type request to update the already existing query. Using the HTTP POST request is going to create a new query every time on Azure DevOps and updating an already created one will be a much better option of the two.

I hope you found this interesting and it helped you. Thank you for reading!