cancel
Showing results for 
Search instead for 
Did you mean: 

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 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 !

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. 

Meet Our Blog Authors
  • Working daily with Microsoft Cloud to deliver the needs of my company, my customers and various Microsoft communities and forums. | Office 365 | Flow | PowerShell | PowerApps | SharePoint |
  • Co-founder of https://plumsail.com, Office 365 and SharePoint expert. Passionate about design and development of easy to use, convenient and flexible products.
  • Microsoft Business Apps MVP. Owner of ThriveFast, an Office 365 consulting company.
  • 7x Microsoft Business Solutions MVP (CRM)
  • I'm keen in MS technologies, SharePoint, Office 365 and development for them
  • Daniel is a Business Productivity Consultant & Microsoft Business Solutions MVP who is very enthusiastic about all things Office 365, Microsoft Flow, PowerApps, Azure & SharePoint (Online). Since the preview, Daniel has been working with Microsoft Flow and later on with Microsoft PowerApps. That led to him being awarded an MVP Award for Business Solutions. He loves to blog, present and evangelize about improving productivity in the modern workspace with these amazing tools!
  • Michelle is an Office 365 solution architect in Twin Cities, MN. She has been delivering business collaboration solutions for years with her focus on SharePoint and Office 365. Michelle is a recent board member of the Minnesota Office 365 User Group and has been a member of the SharePoint community since 2009. She is a frequent speaker at MNSPUG and SharePoint Saturday and co-chaired the Legal SharePoint User Group for 4 years. Her most frequent projects have involved rolling out a large deployment of Office 365, SharePoint Online intranet, build of a "CHAMPS" Office 365 user adoption program and most recently, SharePoint On-Premise to Online Migration. Michelle is very excited about cloud technology as it is shifting her IT Pro focus to collaboration strategy and technical adoption.
  • I'm a Microsoft Office Servers and Services MVP with a special interest in SharePoint, Office 365, Microsoft Flow, Microsoft Teams and PowerApps. I work at Triad Group Plc ( https://triad.co.uk)
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor