Status Rollup Jira Automation

[Travis Hill]

Im hoping to get some help. We have a custom field (customfield_12971) that is used at two different ticket hierarchies. Initiative and Feature Level. We leverage this field for statuses (in a string format). What I want to do is roll all the child inputs of customfield_12971 into a singular output in the parent customfield_12971.

So essentially if there were 3 child tickets with statuses like the below:

Child 1 (customfield_12971) is:

• Status is blah blah

Child 2 (customfield_12971) is: 

• Status is ABC

Child 3 (customfield_12971) is:

• Status is 123

Then the parent (customfield_12971) would be:

• Status is blah blah
• Status is ABC
• Status is 123

Note: this would be an automated push (happening weekly) so Im not too worried about syncing it up as a singular child ticket is updated (although that would be ideal)

I have a rule set up and it says it is successful but the info isnt populating in the parent at all. I suspect its because there are multiple children, any workarounds? Thanks in advance!

[Bill Sheboy · Accepted solution]

Hi @Travis Hill 

First thing, what version of Jira are you using: Cloud, Server, or Data Center?  That impacts how to solve this for the different versions of automation features.

For example, with Jira Cloud, the Lookup Issues action could be used to access the custom field and gather the results: https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Lookup-issues

• trigger: scheduled weekly, with JQL to find the parent issues
• action: lookup issues, with JQL to find the children of a specific parent from the trigger
• smart value condition: check if there were results found from the lookup
  • first value: {{lookupIssues.size}}
  • condition: greater than
  • second value: 0
• action: edit issues, to update the parent with the child information, adjusting this based upon the type of your custom fields

{{#lookupIssues}}
* {{customfield_12971}}
{{/}}

This would not work for Data Center version as Lookup Issues does not support custom fields yet.  Instead a call to the REST API would likely be needed: https://community.atlassian.com/t5/Jira-articles/How-Can-I-Sum-Up-Multiple-Issues-into-a-Parent-Number-Field/bc-p/2883071#M16701

Kind regards,
Bill

[Travis Hill]

@Bill Sheboy we are in cloud I believe!

[Travis Hill]

@Bill Sheboy that is helpful but I think the problem would be that I need to do this for several hundred tickets at a time and they dont have specific JQL other than them just being the same ticket type basically.

Also, with lookup issues isnt it only 100 at a time? We would exceed that I believe.

[Bill Sheboy]

Backing up a bit: what problem are you trying to solve by doing this?  That is, "why do this?"  Knowing that may help reveal other solution approaches.

Until we know that...I will try to answer each part of your post, so please let me know if I miss something...

I need to do this for several hundred tickets at a time

When a rule needs to update many issues, it helps to use a Scheduled Trigger with a JQL statement which can exclude issues that have already been processed (i.e., the "stopping criteria"), such as checking if a field is empty or a custom field to indicate the "last updated date / time by my rule".  This would be the "stopping criteria".  Without that, the rule will repeatedly process the same issues.

they dont have specific JQL other than them just being the same ticket type basically

The JQL for the trigger could be quite simple, perhaps just the issue type, although it still needs some form of "stopping criteria", as described above.

Also, with lookup issues isnt it only 100 at a time?

That is correct.  The Lookup Issues action can process a maximum of 100 issues, as the action relies on the REST API endpoint which has that limit.

The Scheduled Trigger has a higher limit, but the combined impact of the trigger and rule actions such as Lookup Issues could exceed other limits.  Please look here to learn more: https://support.atlassian.com/cloud-automation/docs/automation-service-limits/

[Travis Hill]

Yeah thats definitely a great question. The goal is essentially to do the following:

• We have teams that update a status weekly in a ticket that is team specific and uses that custom field
• Those tickets are children of a larger ticket that also has that same custom field
• Multiple teams will be working on the larger ticket which means you will have multiple responses that need to be rolled up
• Our leadership team would like to see reporting in that custom field based on that larger ticket (for simplicity sake)
  • They feel like the child ticket level is too granular
• So our goal is to essentially get those statuses from each child rolled up into the parent so they can look at the larger ticket in a structure board and see where each team is at
• Essentially this is a status update exercise for our senior leaders that dont have the time to drill into multiple sub layers of tickets for info@bill

[Travis Hill]

@Bill Sheboy sorry I forgot to tag you ^^

[Bill Sheboy]

Thanks for that information, @Travis Hill 

As this is a reporting need, have you considered investigating Atlassian marketplace apps / dashboard gadgets which could present a saved filter as a grid with selectable columns?  That may allow showing parents with child issues below each for that reporting field, and allow drill-down when needed to the children.

This would dramatically reduce the amount of updates to the issues and the consumption of automation monthly usage allocation.  And, reduce the risks of reporting errors if the rules fail / fail to run when expected.

[Travis Hill]

@Bill Sheboy Thanks for reaching out! Unfortunately most of the gadgets available dont serve the need were looking to satisfy here and I dont think add ons from the marketplace are a viable option for us at this time (although I would agree that there are several which would make this exponentially easier on us lol). :( 

[Kelly Arrey]

Hi @Travis Hill, If I understand correctly you're using the Structure by Tempo add-on on Cloud. If so, you might want to click on the column heading of the Progress column and select Edit. They have some built-in options for rolling up progress that you may be able to make use of. HTH,

Kel

[Travis Hill]

@Kelly Arrey thanks for the info, that could be helpful. However, its not quite about rolling up % here (although that plays into it a little), its about rolling up several strings into a field that are providing details on status.

So not, this Feature is 9% done but actually "this team is currently working on this aspect of the feature and is anticipating starting this aspect of the feature next" kind of thing.

[Travis Hill]

@Bill Sheboy Ive been messing with your recommendations and I think Im sort of close to getting whats need using the lookup issues functionality. Now im just getting this error

Its obviously stemming from something Im doing in the rule that I cant quite put my finger on. Any thoughts?

[Bill Sheboy]

Hi, @Travis Hill 

The JSON you show is missing a closing curly-bracket: they need to be paired up to match.

And, that is assuming that structure works for the field type.  When the field is text, the JSON would be this:

{
  "fields": {
    "customfield_12971": "{{customval}}"
  }
}

When the field is another type, please look here to learn the correct syntax: https://support.atlassian.com/cloud-automation/docs/advanced-field-editing-using-json/

[Travis Hill]

Thanks @Bill Sheboy that is helpful. The field is set up to be multi line text but Im still getting the error. 

[Bill Sheboy]

Please show images of your current Edit Issue Fields action and of the audit log showing thee rule execution.

[Travis Hill]

@Bill Sheboy Here is the info youre looking for!

[Bill Sheboy]

Sorry, as I had a typo missing the closing quotation mark around the "fields" element.  Please add that and re-test.

FYI...stuff like this is a good reason to first create the JSON in an editor which highlights matching syntax (i.e., quotes, brackets, etc.) and once it is correct, add it to the rule editor.

[Travis Hill]

@Bill Sheboy it looks like it ran "successfully" so were getting closer. But the results didnt get added to the field.

[Bill Sheboy]

Based on that log entry, there is something wrong with either your created variable or the edit.

The audit log entry showing what you tried for the JSON is incorrectly, so update it to exactly match what you are using in the edit issue action.

Next, please post an image of the create of customval and the edit.

[Travis Hill]

@Bill Sheboy done!

[Bill Sheboy]

Smart values are name, spacing, and case-sensitive.  When an unknown smart value is  used, that returns as null. 

Thus it appears your variable name does not match what is used in the edit's JSON.  Please change them to match and re-test.  Thanks!

[Travis Hill]

@Bill Sheboy hmmm, updated those to match (case, spacing etc) and its still throwing the same error. Unless youre saying the Smart Value in the Variable needs to be changed to match the JSON text. I didnt do that.

[Bill Sheboy]

Whatever the name of the variable is, the declaration and uses need to match.  Let's try for some alignment checks:

• please add a write to the audit log after the variable is created

my variable is: {{whatever the name is now}}

• test your rule
• post an image of the audit log details showing the latest rule execution
• post an image of the edit issue action
• post an image of the complete rule, in one single image (This could be done with a screen capture tool or browser addin which supports scrolling page capture.)

Seeing these together will confirm which steps are (or are not) executing.

[Travis Hill]

@Bill Sheboy thanks for your help on this I really appreciate it so far. Unfortunately my work wont allow for me to put any add ons or anything on (or extensions) so I did screenshots but lined them up way better this time.

[Bill Sheboy]

Thanks for that information!  The log is showing the variable is empty.

As the field is one of the text types, please change your created variable action to:

• name: customval
• smart value:

{{lookupIssues.customfield_12971}}

This will produce a comma-separated values list of the issue data.

If you wanted those to have the line breaks and bullets, change that to this:

• name: customval
• smart value:

{{#lookupIssues}}
* {{customfield_12971}}
{{/}}

[Travis Hill]

@Bill Sheboy You absolute genius, that worked!!

I just have a small formatting issue

As you can see, it pulled everything in but the spots with the * should be on a new line since those were the Status Notes from different tickets. Is there an easy way to set that up?

Heres how I set up my variable action

[Bill Sheboy]

Progress!

Let's try concatenating a newline after each value by updating that variable:

{{#lookupIssues}}
* {{customfield_12971.concat("\n")}}
{{/}}

[Travis Hill]

@Bill Sheboy nailed it! I dunno what I would do without you.

[Bill Sheboy]

Awesome!  One more recommendation...

I recommend pausing, reading through this thread and your rule, and explaining it to another person.  That will help solidify what helped in asking the question, the solution used, and tools to diagnose symptoms when rule-writing...and to share the knowledge with other teammates  :^)

Have a great rest of your week!

[Travis Hill]

@Bill Sheboy great recommendation. Thank you so much!