Jira Automation {{lookupIssues} Filter Approach

[Brandon Moberg]

Hi there!

I was interested in getting some thoughts from folks on the best way to approach this use case.

In our Jira workflow, we log a hidden date/time field (customfield_10260) any time someone moves a ticket into our QA status.

We then use an automation rule to alert our QA team via Slack when tickets are still in QA status and that the hidden date/time field (customfield_10260) log is now greater than/equal to 48 hours ago. 

That all works fine and dandy.

1. Trigger = daily schedule at 8am PST Monday through Friday
2. Lookup Issues = customfield_10260 <= -48h
3. Condition = {{lookupIssues}} does not equal empty
4. Action = Send slack message with list of issues

I want to add one nuance to this automation though: an iteration over the {{lookupIssues}} results to only include issues where the 48 hour check is limited to only factor in business days. What's the best way to go about folding in this type of limitation?

{{issue.customfield_10260.diff(now).businessDays}}

[Bill Sheboy · Accepted solution]

Hi @Brandon Moberg 

Yes, diff().businessDays would be a good place to start as there is no diff().businessHours built-in.

For your 48h figure, I assume you were initially checking for 2 calendar days of flow time, and not 6 business days (6d x 8h/d = 48h).

If you also assume your custom field and now values are never on weekend days...

The diff().businessDays uses the built-in range of Monday-Friday, 9am - 6pm: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-plus-unit---

And so if you first convertToTimeZone() for both date / times to your time zone, that may be accurate enough for what you are trying to do. https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Convert-timezone-date---

The calculation for the diff().businessDays function seems to increment as soon as you hit the next business day, regardless of time of day.  For example:

• if the start date / time is 3pm today and "now" is anytime before Midnight today, the diff equals 0...even though there were 3 work hours left in the day
• if the start date / time is 3pm today and "now" is anytime during tomorrow, the diff equals 1
• and so on

If you need more accuracy, you could check if the dates are in the same calendar week (or not), diff().hours, and then remove the weekend and non-working hours.

Kind regards,
Bill

[Brandon Moberg]

Thank you, @Bill Sheboy - very helpful! Yep, 48 hours of flow time. It only needs to be directionally accurate for now, not precise. Just trying to avoid alerting on Monday on something that may have moved into the QA status on Friday afternoon because of the weekend.

What is the most appropriate way to fold this logic into my automation rule since you can't add a condition on lookup issues? Is it using filter logic within the Slack message after the #lookupissues command?

[Bill Sheboy]

You may use smart value, list filtering to only include the results you need: https://community.atlassian.com/t5/Automation-articles/Filtering-smart-value-lists/ba-p/1827588

[Brandon Moberg]

Thanks, @Bill Sheboy

I seem to be doing it fine when filtering out results based on the straightforward examples there (i.e., only Stories), but I seem to be missing the approach to fold in the custom calculation check. I can easily filter to only stories successfully like this, but every attempt I've made to do a different if check where I look for 

{{issue.customfield_10260.diff(now).businessDays}}

to be >= 2 seems to not work. Any idea on the approach there?

[Bill Sheboy]

The expression you show in the message body is this:

{{#lookupIssues}}
{{#if(equals(issueType.name, "Story"))}}
* <{{url}}|{{key}}> ({{components.name}}:
{{customfield_10260.diff(now).businessDays}} business days or 
{{customfield_10260.diff(now).hours}} total hours)
{{/}}
{{/}}

I am unclear what you are doing with the parentheses around the components expression, so I will ignore that for now.

Let's assume you want to filter on "issueType.name = Story AND the date diff >= 2 days".  And so those can be combined in the if() clause using the and logical operator and a greater-than-or-equal-to function: 

{{#lookupIssues}}
{{#if(and(equals(issueType.name, "Story"), customfield_10260.diff(now).businessDays.gte(2))}}
* <{{url}}|{{key}}> ({{components.name}}:
{{customfield_10260.diff(now).businessDays}} business days or 
{{customfield_10260.diff(now).hours}} total hours)
{{/}}
{{/}}