Automation for custo field to sum original estimate.

[Jeffrey_Denton]

I'm having trouble trying to get this automation to work:  

To ensure that the custom field Total Project Estimate field on an Idea ticket is automatically updated whenever the Original Estimate field of any linked Task or Story is modified. This automation should:

1. Track Time Changes: Monitor changes to the Original Estimate field on Task and Story tickets in the Execution Project.

2. Sum Estimates Across Linked Issues: Sum up the Original Estimates for all Tasks and Stories linked to the Idea via the "is implemented by" or "Implements" link type.

3. Update the Idea Field: Update the Total Project Estimate field on the linked Idea ticket in the Idea Project with the cumulative sum (in hours) of all linked Task/Story estimates.

---

Technical Workflow:

1. Trigger:

   • When the Original Estimate field is updated on a Task or Story in the Execution Project.

2. Branch to Related Issues:

   • Identify the linked Idea ticket using the relationship type "is implemented by" (from the Idea's perspective) or "Implements" (from the Task/Story's perspective).

3. Calculate the Total Estimate:

   • Aggregate the Original Estimates from all linked Task and Story tickets that are connected to the Idea.

4. Update the Idea Field:

   • Update the Total Project Estimate field on the linked Idea ticket with the aggregated total, converted from seconds to hours.

---

Key Considerations:

1. Cross-Project Linking:

   • The automation must handle issues across two projects:
     • The Idea Project (where Ideas are stored).
     • The Execution Project (where Tasks and Stories are created and updated).

2. Link Type Directionality:

   • The relationship between the Idea and the Task/Story must be clearly defined:
     • From the Idea’s perspective: "is implemented by".
     • From the Task/Story’s perspective: "Implements".

3. Field Accessibility:

   • The Total Project Estimate field must be editable on the Idea ticket.
   • The automation actor must have the necessary permissions in both projects.

4. Error Handling:

   • The automation should gracefully handle scenarios where:
     • No linked Idea exists for a Task/Story.
     • The Original Estimate field is missing or blank on certain issues.

---

Example Scenario:

1. An Idea ticket (IDP-1) in the Idea Project is linked to three tickets in the Execution Project:

   • A Task (EP-101) with an Original Estimate of 8 hours.
   • A Story (EP-102) with an Original Estimate of 6 hours.
   • Another Task (EP-103) with an Original Estimate of 4 hours.

2. A user modifies the Original Estimate on EP-102 to 10 hours.

3. The automation triggers and calculates the new total:

   • 8 + 10 + 4 = 22 hours.

4. The Total Project Estimate field on IDP-1 is updated to reflect 22 hours.

---

Automation Rule Structure:

1. Trigger: Field Changed – Original Estimate (Tasks/Stories).
2. Branch: Related Issues – Linked via "Implements" or "is implemented by".
3. Action 1: Create Variable – Aggregate Original Estimates.
4. Action 2: Update Field – Total Project Estimate on the Idea.

Here is the automation:

[Bill Sheboy · Accepted solution]

Hi @Jeffrey_Denton 

That rule description / information looks like stuff from a bot / AI-chat.  If it is, please review the community guidelines to identify sources when posting such content: https://community.atlassian.com/t5/custom/page/page-id/rules-of-engagement

Back to your questions...

As written, that rule will neither identify the one-and-only-one linked Idea in the JPD project nor will it sum the values.  Both may be solved using the Lookup Issues action.  For example:

• trigger: value changes for time tracking
• condition: issue type is Story or Task
• action: lookup issues, using JQL to find Idea linked to the trigger issue, based upon the link type
• smart values condition: to confirm there is only one Idea
  • first value: {{lookupIssues.size}}
  • condition: equals
  • second value: 1
• branch: on JQL to the single issue with key = {{lookupIssues.first.key}}
  • action: lookup issues, with JQL to gather the issues linked to the Idea with the correct single link type
  • action: edit issue, to sum the field with {{lookupIssues.timetracking.originalEstimateSeconds.sum|0}}

This rule will need multiple-project scope, and so your Jira Site Admin will need to help create the rule.

Kind regards,
Bill

[Jeffrey_Denton]

Thank you for your response.  I do appreciate.  I read the posted link you presented, thank you.  I will adhere to guidelines.  I attempted the automation myself and ran into issues then attempted other sources as you identified.  I will attempt it again with your suggestions.  Thank you 

[Jeffrey_Denton]

I tried what you suggested:

Here is the audit log.  You can see it did not update the custom field.  

[Bill Sheboy]

Thanks for that information, @Jeffrey_Denton 

Your new rule is missing the branch to the Idea found and the second Lookup Issues action to gather the issues linked to the Idea.  Please add those and re-test.  Thanks!

[Jeffrey_Denton]

You're right.  I added the Branch, which was a little tricky.  Now I'm having an issue trying to get it from seconds to hours.  I'm going to work on it a minute and try a couple more solutions /3600 doesn't seem to work. 

[Bill Sheboy]

Please try wrapping that in a math expression for the division to convert from seconds to hours:

{{#=}}{{lookupIssues.timetracking.originalEstimateSeconds.sum|0}} / 3600{{/}}

[Jeffrey_Denton]

I tried that and it didn't work, but what I tried and it worked was:

{{lookupIssues.timetracking.originalEstimateSeconds.divide(3600).sum|0}}

[Bill Sheboy]

That is curious as both should work, although producing different results...

I recommend performing the division after the sum:

{{lookupIssues.timetracking.originalEstimateSeconds.sum.divide(3600)}}

The reason is the smart value originalEstimateSeconds is an integer, and inline math operations preserve the typing.  Thus dividing inline will round-downward to an integer, and possibly drop up to 1799 seconds, or 29 minutes, per issue.  Summing first will combine the values before the division (although still losing any fractional hours).

[Trudy Claspill]

Hello @Jeffrey_Denton 

Is the problem that you think the rule you have should give you the desired result? Or is the problem that the rule doesn't give you the desired result and you are not sure how to fix it?

What are the details of the Create Variable and Edit Issue steps?

What is the output in the Audit Log when this rule runs? 

What is the Scope of the rule from the Rule Details page?

Can a Story/Task be linked to more than one Idea?

[Jeffrey_Denton]

The rule is not giving my results and I expect it to sum all original estimates into this custom field.  

Here is the audit Log

here is the details of the create variable: 

variable name:  sumOriginal Estimate

Smart Value:  {{issue.issuelinks.where("type = 'is implemented by'").issues.timeoriginalestimate.sum|0}}

Here is the Edit Issue Steps:

A task or story will not be linked to more than one idea.  

Scope:  Global