Automation to compare Sprint dates with another field

[Narsha Bac]

Hello Team,

I need some help with an automation. I have to check if delivery date for an issue is within the start date and end date of a sprint (sprint in which the issue is). For e.g, if the issue's delivery date is not within the start date and end date of the sprint (sprint in which the issue is), then the delivery date will be updated to NULL.

The issue is that I cannot check if the date 'is between' the sprint end date. I have only options greater than and less than. Any help on this please?

Also, if an issue is in a sprint but the sprint is not active yet, how will I build the automation please?

Thanks.

[Bill Sheboy]

Hi @Narsha Bac 

First thing, there are some limitations to what you are trying:

• If an issue has been in multiple sprints, the rule will need to decide which one to use
• If an issue is in a future sprint with no dates, what do you want to happen?

Next thing, confirm the type of your custom field.  If it is a date picker field you can compare the value directly with no conversions or formatting.  That means removing both value from the comparison and the formatting to jiraDate.

If the field is another type, you will need to ensure the values can be compared.

The same methods can be used with the startDate for the sprint.

Kind regards,
Bill

[Narsha Bac]

Hello @Bill Sheboy ,

1. If an issue has been in multiple sprints, it will have to take the latest one.
2. If a future sprint has no date, then delivery date must be updated to EMPTY. For this one, how will the automation check this please?

Thanks for this point, I will do some tests and if necessary I will not convert it.

Another thing is how will I check if the date 'is between' the sprint end date. I have only options greater than and less than. Any help on this please?

Thanks.

[Bill Sheboy]

Thanks for that information!

Regarding your responses, I see a problem: if an issue was in a sprint and does not finish, and is moved to a future sprint with no dates, there may not be a way to easily know that.  You can try the field change smart values for added / deleted values, as my testing indicates they may be working better now than in the past:

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/#--fieldChange--

For the actual comparison of dates, you could use a Smart Values Condition (previously called Advanced Compare Condition) to test if the Delivery Date is within the sprint boundaries of any sprint found.

This could be done in several ways:

• using the field change smart values, check if the Delivery Date is greater than or equal to the startDate AND less than or equal to the endDate
• use multiple conditions, one after the other, with the field change smart values
• try the same conditions, but use the max and min functions on the sprint field

[Narsha Bac]

Thanks Bill. I was able to create the conditions and check the values of the sprint end date and Delivery date.

However, I had another question. When and if my issue is meeting my conditions, I want to edit the sprint field with the previous value; let me explain with an example.

Use case: delivery date already specified and sprint is changing from (1) null to ABC, or (2) DEF to XYZ

1. if delivery date within start and end date of new sprint, i.e. ABC or XYZ
   then leave delivery date as-is
   sprint = new value of sprint (ABC or XYZ) - This is working fine
2. else consider as an error (e.g. delivery date = 15-Nov-2023, new sprint dates [01-Dec-2023;15-Dec-2023]
   leave delivery date as-is
   sprint = previous value (null or DEF)
   end if

My question is for the second part. How do I edit the sprint field with the previous value? Previous value could be another sprint or NULL. Is this feasible please?

Thanks.

[Bill Sheboy]

Please consider...I recommend listing out your test cases (i.e., before / after values, expected results, etc.)  Once you have these, then create a rule in a test project and exercise all of the cases.   This will reduce the churn on your "production" project for the team.  When the rule is finished, it can be copied over to the project by your admin (or re-implemented by you).

Back to your follow-up questions...

You are using the field value changed for Sprint trigger, and so those special values I noted should contain the prior values: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-issues/#--fieldChange--

And so your rule could use if / else conditions on the dates to make the sprint changes accordingly.  One challenge is...

The changelog for list fields like Sprint does not always work as expected...particularly when values change several times.  If issues move between several sprints this rule may be unreliable.  Here is one of the open defects for the changelog and list-like fields: https://jira.atlassian.com/browse/JRACLOUD-80486

[Narsha Bac]

Hi Bill,

Indeed I am creating these on sandbox itself and once validated and successful, these will be moved in production.

I managed to use the field change in the advanced editing part and it is working but partly. 

else consider as an error (e.g. delivery date = 15-Nov-2023, new sprint dates [01-Dec-2023;15-Dec-2023]
leave delivery date as-is
sprint = previous value (null or DEF)
end if

This is working fine when there is an actual value for sprint, in our case DEF. But for the case where it was NULL previously, it does not work and I get an error "Error while parsing additional fields. Not valid JSON."

Also, I have to check if the delivery date is within the sprint end date/equals the sprint end date (>= enddate, <= enddate), how will I do this as I only have options "greater than", "less than" and "equals". Should I be doing it separately? This part is a bit confusing for me.

Thanks. 

[Bill Sheboy]

For your first question, I believe you will need to conditionally set the sprint either to the from value or to null.  If that does not work, let me know so I can experiment a bit.

For your second question, date fields are usually stored as numbers, and so the mathematical operators for gte() and lte() should work: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-math-expressions/

This approach will change your conditions, so first try this writing the results to the audit log before trying to make issues changes.  Once you confirm the behavior, update the rule to make the edits.

[Narsha Bac]

For the first point, I believe you will need to conditionally set the sprint either to the from value or to null - This is not working. Grateful if you can send me the code/automation for this one? 

The second one, I used the following code. When I remove the 'Not' it is working perfectly fine, but I need the 'Not' in it. I think I am getting the syntax wrong here.

{{if
(and(not(issue.customfield_12807.gte(issue.Sprint.endDate.jiraDate), issue.customfield_12807.lte(issue.Sprint.endDate.jiraDate))))
}}
{
"fields": {
"customfield_12807":{{fieldChange.from}}
}
}

[Bill Sheboy]

Well, that took quite a bit of testing!  First, some learnings:

• The sprint smart value's startDate and endDate values cannot be used in rule functions; they apparently always collapse to null.  Even converting to known date formats does not work.  (A work-around was to save the values in created variables and then convert to dates again...although that seemed hacky.)
• Those same smart values can be used with functions, if the smart values are the first thing in an expression
• It seems the compareTo() function can be used with these date / time values, but other functions do not consistently work: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-compare---
• The sprint field can in fact be cleared by just editing the field in a rule and leaving the value empty.  And so the rule conditions can just use the conditional logic to set the field to either empty or to the prior sprint id value, with {{fieldChange.from}}

For the specifics of the smart value compare...

The first one tests if {{issue.sprint.startDate.compareTo(issue.duedate)}} is less than 1, meaning the start date is before, or equal to, the due date.

And the second tests if {{issue.sprint.endDate.compareTo(issue.duedate)}} is greater than -1, meaning the end date is equal to, or greater than, the due date.

[Narsha Bac]

Hi Bill,

Apologies for late reply. 

Thanks for your help and advice. It was working partly but eventually the requirements were changed and we dropped this solution.

Regards,

Narsha 

[Bill Sheboy]

Thank you for the follow-up, Narsha.