Automation Lookup all the child items values and then update the Parent

[Sam Lin]

I need help with automating the update of a parent Epic's status color based on the status colors of its child work items. Here's the logic I want to implement:

• If all child work items have a status color of "TBD," then set the Epic's status color to "TBD."

• If any child work item has a status color of "Red," then set the Epic's status color to "Red."

• If any child work item has a status color of "Yellow," then set the Epic's status color to "Yellow."

• If any child work item has a status color of "Green," then set the Epic's status color to "Green."

Could someone guide me on how to achieve this via automation?

Trying to explore more reliable methods to handle this to ensure it doesn't fail.

[Trudy Claspill · Accepted solution]

Hello @Sam Lin 

Are the above conditions in priority order? If once child issue is Red and another is Yellow, should the parent be set to Red?

When do you want this rule triggered? Should it be triggered when the color changes on a child issue? Do you need this to run periodically against a set of parent issues?

What is the subscription level for your Jira subscription; free, standard, premium, or enterprise? I ask because there are limitations on how many rule executions you can have in a month based on your plan. That may impact the recommendation about the type of rule to be developed.

https://support.atlassian.com/cloud-automation/docs/how-is-my-usage-calculated/

[Sam Lin]

Hi @Trudy Claspill , thanks for your response.

1. The conditions should follow a priority order—if any child issue is marked Red and others are Yellow, the parent should be set to Red.

2. Currently, I’m able to run and configure this rule based on changes to the status color field for issues under the Epic.

3. Our subscription level is Enterprise. 

Thanks again!

[Trudy Claspill]

I think I have this worked out, but it is late and I need to test it before saying it is a working solution.

The basic idea is to run a series of Lookup Issues actions to look for sibling issues matching each "Color" value and set a variable to the number found.

Then run an If-else block where you first check the value of the variable counting the TBD siblings found and set a Color variable to TBD if that is greater than 0. Set of the Else-if blocks to check each of the other variables created after each of the other Lookup Issues action, and set a shared Color variable to their color if the counting variable is greater than 0.

The idea is at the conclusion of the if-else block you have a "Color" variable that is set to the color you want to use in the parent issue.

I need to test if this actually works. After testing tomorrow I'll post another reply.

[Sam Lin]

@Trudy Claspill  Thank you looking forward for your suggestion.

[Trudy Claspill]

First I'm going to paste the complete rule. You'll see numbers in it for which I will provide details below.

There are lots of "Add value to the audit log" (Log actions) in it that I added to help with debugging. Ultimately those are not needed.

[Trudy Claspill]

1. Create a variable that will store the Color you will eventually set in the parent. Set the variable to one of the colors.

2. Use Lookup Issues to look for any siblings (of the trigger issue) that match one of the color values. In my first action I am looking for siblings that are set to "Red"

3. Create another variable to record the count of issues found by the Lookup.

Repeat 2 and 3 for each color value.

At 4 we start and IF / ELSE-IF block to evaluate each of the colorCount variables we created, and set the original Color variable accordingly. With the If/else block you order it based on the priorities of the color decision: TBD, Red, Yellow, Green.

First check if there were any TBD issues found, If so, set the Color variable to TBD.

There is no "Set Variable" action, so you use the Create Variable action again and specify the same variable name we used initially.

Make sure that you choose the IF/Else condition, not the IF condition.

4. Check if any TBD issues were found

5. Set the Color variable to TBD if TBD issues were found.

6. Ignore 6. That is just a log action. I labeled that for call out by mistake.

7. As you are constructing the rule, here you would see an option for Add Else. You will want to select that to put the next colorCount check into the rule.

Click the Add Else button and repeat steps 4 and 5 for each colorCount variable. The logic proceeds to evaluating the Else-if blocks only if the preceding IF / Else-If condition was not met. So, if one issue was found with TBD, the Color variable will be set to TBD and none of the Else-if blocks will be evaluated. If there were no TBD issues, it will next check if there were any Red issues. If there were Red issues then the Color variable will be set to Red and none of the remaining Else-if blocks will be evaluated.

After all the IF/Else blocks are done you will then use the For Each branch to shift focus to the Parent of the trigger issue.

8. And here you set the color field in the parent issue to the value you have stored in the Color variable.

You may want to include a Condition right after the trigger to check the Issue Type of the trigger issue. You may want to only run this rule with the trigger issue is of one of the types that could be a child of an Epic.

[Sam Lin]

@Trudy Claspill  Thanks for the suggestions—I’ve configured it. I see that it’s checking all the status colors, but it’s not updating correctly. For example, if the child items has 6 tickets in Yellow, the parent should also update to Yellow, but instead it’s being set to TBD.

[Sam Lin]

@Trudy Claspill  the automation rule

[Trudy Claspill]

Excellent progress!

The IF/Else conditions are evaluated in order from top to bottom. The evaluations stop with the first one where the condition is met.

You have ordered the IF/Else evaluations like I did:
TBD
Red
Yellow
Green

So if there are any issues with TBD, then TBD is the color set in the parent. And there were issues set to TBD for the run of the rule you showed, so the parent color was set to TBD.

If that is not the correct, where should TBD be in the prioritized list of colors? It needs to be in the same position in the If/Else structure. If you want the color set to TBD only when there are no other actual colors in the child issues, then TBD needs to be in the last If/Else position.

[Sam Lin]

@Trudy ClaspillThank you so much this is working.

[Trudy Claspill]

You're welcome!

[Bill Sheboy]

Hi @Trudy Claspill 

I note your example rule does not have a condition to check the trigger issue's type (assuming it is not an Epic), while the one from @Sam Lin has a condition check to confirm the trigger is an Epic.  But both rules use the same JQL for the Lookup Issues to test parent = {{issue.parent.key}}...

I believe this means they are accumulating results from different issue sets, correct?

And as a suggestion, the example rule you show could be shortened using one Lookup Issues action (without the color filtering) and a Lookup Table to hold the counts for each one.  For example (assuming the above trigger issue type is correct):

• action: lookup issues, with JQL of
  • parent = {{triggerIssue.parent.key}}
• action: lookup table
  • name: varColorCounts
    • row
      • key: Red
      • value: {{lookupIssues.customfield_12345.value.match("(Red)").size}}
    • row
      • key: Yellow
      • value: {{lookupIssues.customfield_12345.value.match("(Yellow)").size}}
    • row...repeat for other colors
• and then use the table lookups in the if / else block, such as:
  • test if {{varColorCounts.get("Red")}} greater than 0

The custom field ID would likely need to be used in the expression, or the exact smart value for the field.  This works for single-selection fields, and the flatten function would be added for multiple-selection.

Kind regards,
Bill

[Trudy Claspill]

@Bill Sheboy 

You are correct about the condition. 

@Sam Lin if you want the rule to update Epics based on changes to the child issues you need to change that condition to check for the issue types that could be children of Epics.

Bill, you always have a more elegant method that takes fewer steps! I love that you take the time to provide those refined solutions!

[Bill Sheboy]

Thanks, Trudy!  Also, I updated the above suggestion to use a simpler way to count the values.

[K Vivek Rao]

Hi @Sam Lin ,

Just to confirm before we proceed — when you mention 'status color' (Red, Yellow, Green, TBD), could you specify where this value is stored on the issue? For example, is it: (1) the issue Status itself, (2) a custom field, or (3) another attribute like a label or component? This will help ensure the automation is set up correctly.

Also, is it on cloud or DC?

[Sam Lin]

@K Vivek Rao 

The data is stored in custom field and currently on the Cloud.

Thanks!