Skip to main content

Notifications

Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

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 stepazblogflow1.pngAction: “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'] 

azblogflow2.png

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.azblogflow4.png

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)azblogflow3.png

//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.azblogflow5.png

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!

Comments

*This post is locked for comments

  • sd1991 Profile Picture sd1991
    Posted at
    Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

    Hi, I have a flow which updates a SharePoint list with the status of work items (features) in DevOps. One of the columns I need to include for each list item is which epic (parent) the feature is linked to. Am I able to use this method to simply get the name of the parent work item so I can then update the SharePoint list item?

  • PowerPat Profile Picture PowerPat 96
    Posted at
    Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

    hi, this is a query across projects ... what is the syntax for only THE Project ?

  • yashag2255 Profile Picture yashag2255 24,454
    Posted at
    Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

    Hey @FlowJoe ! Glad that you found this interesting. Note: While using Azure DevOps, you can create the query by populating the fields (as usual) and simply get the 'wiql' query and paste it query part of the HTTP action (with any dynamic values that you want to pass). I missed mentioning that part in the post. 

  • FlowJoe Profile Picture FlowJoe 27
    Posted at
    Create/ Update Parameterized Queries in Azure DevOps with Microsoft Flow

    This is amazing, I was looking at ways to create a work around to pass queries! I'm looking forward to trying this, thanks for the post @yashag2255 !