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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,251)