cancel
Showing results for 
Search instead for 
Did you mean: 
yashag2255

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. 

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

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?

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/