Sort not impacting subtasks in JQL Query for Automation

Wes Crockett July 29, 2024

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.:

image.png

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.

 

 

2 answers

1 accepted

2 votes
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.
July 29, 2024

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

Wes Crockett July 29, 2024

Oooph. rough limitation :(

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

Thank you for the insight

1 vote
Trudy Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 29, 2024

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 July 29, 2024

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:

image.png

 

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 Claspill
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
July 29, 2024

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?

Wes Crockett July 29, 2024

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.

Suggest an answer

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

Atlassian Community Events