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; } } }

Sort not impacting subtasks in JQL Query for Automation

Wes Crockett

Hello, I am working on a Jira Automation to get currently active projects (tasks) and their subtasks. The subtasks have a custom field called 'Actual Start' and a 'Due Date.' Due Date is simply a date, while Actual Start is a DateTime stamp for the scheduled start time of the item.

My JQL is:

Project = 'Environment Management' AND issuetype in (Task) AND status not in (Archived) ORDER BY DueDate ASC

The top-level tasks seem to sort in the expected output by DueDate, but the subtasks do not.:

Any idea how to get this to sort correctly?

NOTE: I understand that this is a bit of an unconventional use of Jira, but I am using it for tracking system upgrade/implementation timelines and automating the weekly report that goes out to stakeholders for tracking progress.

Accepted answers

Bill Sheboy

Hi @Wes Crockett -- Welcome to the Atlassian Community!

Based on the details you posted in response to Trudy's questions, what you ask for also ordering the subtasks under each parent is not easy...

JQL only has basic ORDER BY syntax, and you have nested the subtask list within the results of the lookup issues results / iterations. And so the order of the subtasks will be based on how they exist within the parent issue: the ORDER BY has no impact.

Additionally, JQL has no GROUP BY syntax, as would an SQL, to simplify the groups.

To do what you asked, the parent issue and subtasks need to be returned at the same level within the JQL result, allowing the ORDER BY to apply to all of them together. So a workaround is to add a custom field which essentially build a sorting key for each issue, and then change to ORDER BY that field. This is often a brittle solution as the custom field would need updates whenever the due date changes.

Kind regards,
Bill

All comments

Trudy P Claspill

Hello @Wes Crockett

Welcome to the Atlassian community.

The output you have shared does not appear to be from any native functionality within Jira Cloud. Are you using a third party app to get the output organized by parent Task and then subtasks nested below that?

Wes Crockett

Thanks for the reply. I should have included more about the automation.

It is a three step automation doing a weekly email from a JQL lookup:

I am using {{#lookupIssues}} followed by {{#subtasks}} to generate the output in HTML tables with CSS for styling.

Sorry for that lapse in details.

Here is the main part of the email, minus the CSS and miscellaneous HTML parts:

{{#lookupIssues}}
<h2>{{summary}}</h2>
 <table>
  <thead>
   <tr>
    <th>Task</th>
    <th>Completion Status</th>
    <th>Scheduled Date/Time</th>
   </tr>
  </thead>
  <tbody>
{{#subtasks}}
   <tr>
    <td>{{summary}}</td>
    <td>{{status.name}}</td>
    <td>{{Actual start.convertToTimeZone("America/Los_Angeles").format("MM/dd/YYYY, h:mm a")}}</td>
   </tr>
{{/subtasks}}
  </tbody>
 </table>
{{/lookupIssues}}

Trudy P Claspill

You said you wanted the information ordered by Due Date, and you use that in your filter.

But your email is printing Actual Date, not Due Date. So how do you know they are not in the right order?

Bill Sheboy

Hi @Wes Crockett -- Welcome to the Atlassian Community!

Based on the details you posted in response to Trudy's questions, what you ask for also ordering the subtasks under each parent is not easy...

Additionally, JQL has no GROUP BY syntax, as would an SQL, to simplify the groups.

Kind regards,
Bill

Wes Crockett

Yes, the email is only showing the Actual Date, not Due Date, The JQL statement, I believe, is all that impacts the order of output.

Wes Crockett

Oooph. rough limitation

I may have to go to a API based solution and just use the JSON returned.

Thank you for the insight

Welcome!

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

Quick Links

Unanswered
Groups
Help

Find more posts tagged with

jira

cloud

jira-cloud

automation