nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Adrian Avalos_ywpjg0

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

Accepted answers

Bill Sheboy

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

All comments

Bill Sheboy

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

Kind regards,
Bill

Adrian Avalos_ywpjg0

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

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_ywpjg0

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

Screenshot 2024-11-25 134446.png

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

cloud

jira-cloud

automation

jira-automation