How can I send out email with automation that sorts and categorizes based off due date?

Adrian Avalos
Contributor
November 7, 2024

Hello. Is there a way I can modify my script to send email of table that sorts and categorizes by Due Date? For example, if Due Date is past due date list at top of table and display row as red, if Due Date is within 2 days list at middle of table and display row as yellow, and if Due Date is within 3 days list at bottom of table and display row as green.

 

Screenshot below of what we're trying to achieve and of my script attached. 

 

Screenshot 2024-11-07 100829.png

Screenshot 2024-11-07 095425.png

 

 

1 answer

1 accepted

1 vote
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 7, 2024

Hi @Adrian Avalos 

Are you using a Jira Automation rule to do this?  If so...I recommend implementing this incrementally:

Your rule could use the Lookup Issues action with JQL containing an ORDER BY clause to order the issues: https://support.atlassian.com/cloud-automation/docs/jira-automation-actions/#Lookup-issues

Then format the results as a table for your email by iterating over the lookup results: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-lists/#Combined-function-examples

Then once your email works as you want, you could add conditional logic with the Due Date field to adjust the color formatting: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-conditional-logic/

Kind regards,
Bill

Adrian Avalos
Contributor
November 18, 2024

Hi @Bill Sheboy 

Apologies for the delay. Yes, I'm setting up in Automation rule. I setup the first piece you shared but with Branch rule / related issues component (screenshot below) and am using {{#Issues}} instead of {{#lookupIssues}} as this piece would cause errors in rule.

 

For formatting table would you know how to iterate over the script below? I looked through the links you shared but didn't see anything to return a specific text based off due date

if due date<=3 days display Green text

if due date<=2 days display Red text

 

<td style="border: 1px solid #000000" valign="middle" align="center"> {{duedate}}</td>

 

Screenshot 2024-11-18 154720.png

Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 18, 2024

What version of Jira (and automation rules) are you using: Cloud, Server, or Data Center, as the features are different for each one?

  • The {{lookupIssues}} smart value is only available after using the Lookup Issues action in the rule, and that is only available for Jira Cloud and Data Center
  • The {{issues}} smart value is only used for Jira Server and Data Center, with the bulk-handling feature of the Scheduled Trigger or branches

 

Regarding the conditional colors, you may use the date / time difference functions and a conditional statement to determine the color used: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/#Date-difference---

For example:

{{#if({{now.diff(issue.duedate).days.le(3))}}use green color syntax{{/}}

However as your "red" color is a subset of that one (i.e., <= 2), I recommend carefully reviewing the date differences to determine which colors should take precedence.  Then use logical and() conditions to help get the ranges correct: https://support.atlassian.com/cloud-automation/docs/jira-smart-values-conditional-logic/#and

Adrian Avalos
Contributor
November 25, 2024

Thanks @Bill Sheboy did the attached and it seems to be working

Screenshot 2024-11-25 134446.png

Like Bill Sheboy likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events