Automation to Roll up dates for target start and target end

[RXAVIER]

Hello to Everyone!

So I'm basically trying to do automation for a roll up from the custom fields "Target start" and "Target end" when a story or a work change any of this both fields.

I would like to reflect on the Epic level the earliest Target start from all the stories, and the latest Target end from all the stories. This automation should be able to update even when we remove stories or add stories to the epic, or just update dates.

I followed this pages for reference:

• https://community.atlassian.com/t5/Jira-questions/Is-it-possible-to-copy-value-from-quot-Target-End-date-quot-to/qaq-p/922787
• https://community.atlassian.com/t5/Jira-discussions/Set-Epic-Start-and-Due-Date-to-earliest-latest-Child-Dates/td-p/2353100#U2473939

The automation that I have built until now looks like this so far:

So there is goes my questions:

1. How I edit in the last step the target end once is a custom field and I believe that I can just do it by json, this was the input that I found but I believe that is not working
   {
   "fields": {
   "customfield_13603": "{{triggerIssue.Target End}}"
   }
   }
   
   
2. I believe for Target start the custom field should be another code right? And in the look up value step should be "asc"
3. To guarantee that we cover any change in the dates of the child issues of the epic (removed, edit or added) should I refacth the data at any point?

Thank you all in advance :) 

[Bill Sheboy]

Hi @RXAVIER 

Before proceeding, I recommend checking if the Target Start and Target End fields are supported by automation rules.  I recall earlier community posts that many Advanced Roadmap fields are not supported by rules.  You can check this using this how-to article to confirm if the fields are supported for an example issue: https://support.atlassian.com/cloud-automation/docs/find-the-smart-value-for-a-field/  If you find the fields, they are supported.

Let's assume the fields are supported...

For your question #1 and #2, you are trying to update the epic with the earliest Target Start and latest Target End, correct?  If so, the source for your edits should be the lookup results.  Like this, filling in the correct values for the custom field ids:

{
  "fields": {
    "customfield_13603": "{{lookupIssues.Target End.max}}"
  }
}

For Target Start, use the min instead of the max value.

For your question #3, please consider all of the scenarios you want to handle, as you may need separate rules with separate triggers:

• target start or target end fields change in a Work issue
• target start or target end fields change in an Epic issue (i.e., someone manually overrode the values)
• issue created and added to an existing epic
• issue removed from an epic
• issue deleted (and so removed from an epic)

Kind regards,
Bill

[RXAVIER]

Thanks you @Bill Sheboy  for you answer I think the automation is almost ready but I'm receiving the following error.

I already was reading this thread but I couldn't found the solution, I believe that my instance as date format as defined is there any walk around or way to check this out?

Thanks in advance,
Roberto

[Bill Sheboy]

This is one of those situations where Atlassian decided to implement non-standard formats for date and date/time fields.

The fix is to force the field's format during the edit in the JSON to match the requested format.  In this case, that would be jiraDate.  Using my earlier example, we would add this:

{
  "fields": {
    "customfield_13603": "{{lookupIssues.Target End.max.jiraDate}}"
  }
}

You can learn more about date/time smart values for similar situations here: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#--

[RXAVIER]

Thanks for sharing that @Bill Sheboy  didn't knew about it.

Even though I just tried to run it and got the same feedback from the JSON :(

This is the audit log (bellow is the JSON)

I didn't tried it for Target Start but I believe that the result would be quite the same. If you have any other option it would be really valuable. 

Thanks in advance for you attention,
Roberto 

[Bill Sheboy]

I think I see the problem: your source smart values may be incorrect in the lookup issues usage.  This is my mistake as I did not check the fields before suggesting using JSON.

Smart values are name, spacing, and case-sensitive.  When the wrong value is used, null is returned and often no error occurs.  The correct smart values are "Target start" and "Target end".

Let's eliminate that possibility by using the field id instead, so please try this:

{
  "fields": {
    "customfield_14684": "{{lookupIssues.customfield_14684.max.jiraDate}}"
  }
}

[RXAVIER]

Hello @Bill Sheboy I just tried it like that but even though the result is the same as the image above even changing for the field id instead

[Bill Sheboy]

This makes me wonder...

There are date fields in Jira which are not date or date/time type; instead they are text!  Let's try to force the date type first:

{
  "fields": {
    "customfield_14684": "{{lookupIssues.customfield_14684.toDate.max.jiraDate}}"
  }
}

[RXAVIER]

Hi @Bill Sheboy you are the man :) it's working now completely fine with the JSON above. So this struggle with the date is over, thank you so much for your help.

Just one thing that I would like to ask you (that's connected to my 3rd point when I opened this discussion) I couldn't find a trigger for the automation when a child issue is removed. There is a way to trigger this in a automation?

Thank you a lot,
Roberto

[Bill Sheboy]

Awesome; I am glad that part is working for you!

To detect an issue (i.e., child) being removed from an epic, you can try the generic Issue Updated trigger, and then check the if the changelog is different for the epic link field with and advanced compare condition:

• first value: {{#changelog.Epic link}}{fromString}}{{/}}
• condition: does not equal
• second value: {{#changelog.Epic link}}{{toString}}{{/}}

Followed by a condition to confirm the Epic link is now empty.

I believe that is the only way to do that check at this time.

[RXAVIER]

Hello @Bill Sheboy  I was checking in the triggers available and I was thinking to use this one: 

But I have to admit that I'm confused where should I used that compare condition should be here just after the trigger?

Thank you in advance,
Roberto

[Bill Sheboy]

For the child issue removed case, the rule must use the generic "Issue Updated" alone.  Only then will the changelog work as I noted for detecting the case.

After that change you may add the advanced compare condition I suggested immediately after the trigger.

[RXAVIER]

Just made a run regarding the compare condition and it gave no update to the epic.

I believe that this is related because of the trigger, once it is pointing to update issue, and that is the work not the epic, in this scenario is trying to update the work I believe or am I missing something?

[Bill Sheboy]

There are some missing pieces from your rule for this scenario.  I noted these when I first suggested using the changelog:

• First detect the child issue was removed from the epic; the current trigger and advanced compare cover this
• Add another condition to check if the current epic link is empty, meaning the issue was removed
• Finally, as the issue was removed, the branch on epic (parent) can no longer be used.  Instead a branch on JQL is needed, using the {{#changelog.Epic link}}{{fromString}}{{/}} as the key of the epic which requires updates

These rule differences are why a separate rule is needed to handle the case of removing a child issue from the epic.

[RXAVIER]

So I already added that extra condition to it and I used the Branch rule on JQL. 
Even though is getting now a different error, saying that the Epic link was deleted.

That is actually correct but I want to use the one that was there when was trigged :) (I tried using the "{{triggerIssue.epic.key}}" at the JQL but the error was the same as shown bellow)

Thanks in advance,
Roberto

[Bill Sheboy]

Let's try changing that second condition to check if

{{#changelog.Epic link}}{{toString}}{{/}} is empty.

And, the JQL for that branch should be:

key = {{#changelog.Epic link}}{{fromString}}{{/}} AND issueType = Epic

[RXAVIER]

I believe we are almost there!

So in the more lower "Action details" you can see the error that the JQL ({{#changelog.Epic link}}{{toString}}{{/}} is empty) got.

I just tried by curiosity to execute it without the 2nd condition and we got success with the expected result. Even trying with issue fields condition it gives the error of Epic Link deleted.

Thanks for you attention,
Roberto 

[Bill Sheboy]

For the second condition I noted, you want an advanced compare condition and not a JQL condition:

• first value: {{#changelog.Epic link}}{{toString}}{{/}}
• condition: equals
• second value: leave this empty

Although your testing surfaces another case to cover: when the epic changes for an issue, both the old and new epic should be updated.

[RXAVIER]

Hello @Bill Sheboy !

I changed to the advance compare condition as you said and it works fine now.

So yeah you are right the new epic is being update once covers the trigger of updated issue, but once the old epic is not taking any update the trigger is not covering it.

Do you have any idea how to force to run the lookup in the old epic?

Thank you in advance,
Roberto

[Bill Sheboy]

For that scenario, I believe the same rule can be used, with an adjustment...and I am paraphrasing here to only show the changes in italics :^)  With this one, I believe you can remove the new condition we just added.

• trigger...
• conditions to decided if we should proceed...
• branch on the {{fromString}} match to update the previous epic
  • lookup issues...
  • update the previous epic
• condition to check if the {{toString}} is not empty
• branch on the {{toString}} match to update the new epic
  • lookup issues...
  • update the new epic