cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sanal5677
Microsoft
Microsoft

Building flow using multi-dimensional Azure Devops Query output

Hi All,

 

 I am building a flow to populate a word document by using the Azure Devops - Get Query Results action output. 

 

The output contains the hierarchy of Azure Devops Items per below

    

IDStateWorkItemTypeParentTitle
1ActiveObjective OBJ1
2ActiveObjective OBJ2
3ActiveKey Result1KR1
4ActiveKey Result1KR2
5ActiveKey Result2KR3
6ActiveFeature3FTR1
7ActiveFeature3FTR2
8ActiveFeature4FTR3
9ActiveFeature4FTR4
10ActiveFeature5FTR5

 

I need to fill this in a word template which has the below format :

 

 ObjectiveID :     ObjectiveName: 

KeyResultID:      KeyResultName: 

Feature Details:

IDStatusTitleNotes
    
    

 

The above content has the following characteristics as you can see from the query output above :

1. 1 Objective can have multiple Key Result's

2. 1 Key Result can have multiple Feature's

3. Notes - Top 1 comment under Description, corresponding to each Feature work item.

4. Have created the whole objective content as a repeating control in word template.

5. Each inner control is a rich text control.

 

This will become a multi-dimensional array when we use in the flow as much as i can think of based on  my little knowledge of flow per below:

 
{
  "Objective""Obj1",
  "ObjectiveID"1,
  "Key Result": [
    {
      "KeyResultID"3,
      "KeyResultName""KR1",
      "Feature": [
        {
          "ID"6,
          "State""Active",
          "Title""FTR1"
        },
        {
          "ID"7,
          "State""Active",
          "Title""FTR2"
        }
      ]
    }
  ]
}
 
I have created a flow to just check for the workitemtype - "Objective" and then populate the objective content for now. I want to filter the output for every objective in the output (whichever key result has the same parent) and then filter the feature which has the same key result as its parent. Finally update these contents to the array. 

 

After this is done I will need to update the notes section in the template (for every Feature WorkItemID) by calling the get comment API for the corresponding feature WorkItemID.

 

The above flow will happen for every objective workitemtype. In this case 2 Objectives and then finally write it to the word document.

ObjectiveID: 1 Objective Name : OBJ1

 

Key Result ID3  Key Result Title: KR1

 

ID  

Title  

Status 

Notes

6

FTR1

Active

Comments 1

7

FTR2

Active

Comments 2

 

Below is the current flow which i have , which i need to modify based on the suggested approach for appending to a multi-dimensional array 

 
 

Flow.jpg

 

Please do let me know how I can do the below said :

- Append to the multi-dimensional array in the above specified table format, so that the repeating control accepts it.

- Since the query results are already available, how can i filter and get the children for every objective & key result, till we reach to the feature level records. Instead of calling Get Work Item Children for every objective 

- Once i get the content for the comments for a feature work item, how can I update this objective array, as the Get comments is a separate action.

- How to apply conditional formatting for one field based on a value. Say , if we get state as Active , then the table cell in the word will have green background else its yellow.

 

 

 

 

 

2 REPLIES 2
sanal5677
Microsoft
Microsoft

sanal5677
Microsoft
Microsoft

Also the Get Query Results action does not result System.Parent when there is no parent for a workitem. Please do let me know how to return consider the output to return null for those work items which does not have a parent.

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power Automate User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (51,111)