I am trying to create an automation between two projects. The current JIRA hierarchy we follow is as below;
Portfolio Epic > Epic > User Story/Task
Problem Statement:
I have two projects Project A and Project B
I have an Epic in Project A - Epic 1 that is linked via "Related To" link to the overall Portfolio Epic in Project B. Both projects have two different project keys.
I am trying to set up an automation that looks at Epic 1 (Project A) and based on the "Relates To" link picks up the children in Project B - Portfolio Epic and sums up the story points and the % complete on children tickets and writes it back to Project A - Epic 1.
What I have got so far:
Currently the Automation is manually triggered while I test it out and I will move it to scheduled or auto triggered once I have it to where I need it.
Story points is calculated using the following - {{lookupIssues.Story Points.sum}}
At this point although the above rule is supposed to work it returns no value as I suspect the following may be blocking it. This automation only works if the rule is global; as project specific rules restrict the look up clause above. I need help validating my theory.
Second part to my problem is that I am unable figure out if there is a way to get the progress % over to Project A - Epic 1 from Project B
Any help and/or guidance would be greatly appreciated.
Hi @kenneth.gomes - You are correct that this needs to be a multiple project rule scope to span across your two projects. Once the scope is updated, you should have no issue with your second problem.
Be warned though that Relates To is a nasty link type when you're trying to automate because there is no "parent/child" relationship as you have with Blocks/Is Blocked By and Clones/Is Cloned By link types. This could cause some pain points in your automation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Your hypothesis is correct about the rule scope: a lookup issues action (or branch, etc.) can only see the issues within the selected scope of the rule.
Next regarding the % progress...first how do you want to measure progress? The image you show is of a parent and child issues. That progress indicator is by the status category of issues. There are other ways to measure progress, such as based on the aggregate of your estimation values.
Once you have selected your progress method, you could use the issues in the lookup issues and smart value list filtering (or separate lookup calls) to compute the result.
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bill,
Could you point me to the function that would help me aggregate the estimation values?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Certainly! Here are a couple of examples, using the math functions and smart value list filtering, with references below...
https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/
https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588
For the same percentage the parent/child shows based on count:
{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}}*100,0){{/}}
To instead do this by story points, try this:
{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}{{Story points|0}}+{{/}}{{/}}0)/{{lookupIssues.Story points.sum|0}}*100,0){{/}}
How these work...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bill,
I got access to run the rule globally but for some reason the story points are not being summed up. I haven't used your formula yet but instead I tried to keep it simple by using {{lookupissues.Story Points.sum}} but the automation still wont sum up the story points.
Log
Based on the automation it is able to pick up the linked PE but it is unable to sum up the epics underneath it.
CL - 6372 is a Epic
UIP-6 is a Portfolio Epic
I am trying to sum up the child issues underneath them.
Any suggestion or help would be greatly appreciated.
Thanks
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I notice two things: there is an extra space before the smart value and the case is incorrect. FYI that smart values are name, spacing, and case sensitive.
Please try this one if you are using a company-managed project (CMP):
{{lookupIssues.Story points.sum|0}}
If instead you are using a team-managed project (TMP), they use Story point estimate:
{{lookupIssues.Story point estimate.sum|0}}
I also added the default value of zero (0) so even if the fields are empty you will get a number.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bill,
I tried the changes you recommended but I am still unable to display the sum.
I tried everything at this point I am not sure what is wrong with the automation.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Please post an image of your complete, current rule. That may help to explain what is happening. Thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bill,
Here is the complete rule;
I trigger it manually from this epic
The look up is able to look up the related to ticket i.e. UIP-6
But it is unable to lookup the children within UIP-6 and sum up the SP values.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Bill,
I did some more research and it seems the above formula only works for regular projects not next gen projects. It seems I would need to look up how to automation rules work for next gen projects
Ken
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Did you see my note about using "Story point estimate" rather than "Story points"? That should work for a team-managed project (formerly called NextGen).
{{lookupIssues.Story point estimate.sum|0}}
Next, you appear to be trying to sum child issues of the epic, not the linked issues.
Please try changing the Lookup Issues JQL to this:
parent = {{triggerIssue.key}}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I have been struggling with a similar problem...
This is for company-managed projects. I want to calculate % complete on Epics based on the number of children. I have created an automation rule using your magic formula
{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}})*100,0){{/}}
but get this error
Your help would be greatly appreciated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Looking at your expression, there is a parenthesis in the wrong place. It appears my original post had an error, and I have corrected that. Tip: it helps to create expressions like this in a text editor which shows matched / mismatched parentheses and brackets.
Here is the corrected one:
{{#=}}ROUND((0+{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}1+{{/}}{{/}}0)/{{lookupIssues.size|0}}*100,0){{/}}
It can also help to build expressions like this, piece by piece, and then combine them. Here is an updated version of how I did that (except with the plus signs added at the front rather than behind the ones).
Completed issues count:
( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} )
Total issues count:
{{lookupIssues.size|0}}
The percentage divides those two values:
{{#=}}( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100{{/}}
Finally, the percentage, rounded to a whole number would be:
{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Perfect! It works!!!
Thank you so much for sharing your knowledge and for a quick turn-around.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
@Bill Sheboy - is there a limit on the number of children returned? I stumbled on an epic that has over 200 stories, but the formula only returns 100.
Any way to work around this?
Thank you
Coco
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is no way I know of around that: there is an automation rule limit of up to 100 issues for lookup issues and other aspects of rules have other limits: https://community.atlassian.com/t5/Jira-articles/Automation-for-Jira-Send-web-request-using-Jira-REST-API/ba-p/1443828
If you often expect more than 100 issues, you may need to define a predictable way to break this up into pieces, with additional JQL criteria, save the results in variables, and sum up later. For example, by labels, components, etc.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI @Bill Sheboy !
Thank you so much for explaining how you got
{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(equals(status.statusCategory.name,"Done"))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}
I am trying to also remove any issues in status "Not going to do" or "Cancelled" from the equation all together but having a hard time getting the syntax correct. Any help would be greatly appreciated!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
HI @Erin Ehlers -- Welcome to the Atlassian Community!
First thing...for older threads it can help to create a new question and add a link to original ones. That helps ensure more people see the question to offer suggestions.
Back to your question...
You can use various conditional logic functions to do that, such as not() and and() . I recommend doing this step by step to get the logic how you want it, testing as you go. For example:
and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) )
Then that can be used to substitute into then entire expresssion:
{{#=}}ROUND( ( 0{{#lookupIssues}}{{#if(and( not(equals(status.name, "Not going to do")), not(equals(status.name, "Cancelled")) ))}}+1{{/}}{{/}} ) / {{lookupIssues.size|0}} * 100, 0){{/}}
Please check the case of your status names match the expression exactly, or add the toLowerCase() function force them to a known value.
Kind regards,
Bill
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Show up and give back by attending an Atlassian Community Event: we’ll donate $10 for every event attendee in March!
Join an Atlassian Community Event!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.