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

Automation: How can I query for issues created before trigger issue?

unknown

I have a rule that searches the description of the ticket and pulls an Id from it and sets a custom field. I am trying to make it check for any other issues with the same custom field that were created prior to the issue the rule is running on, close the new issue and then associate it to the older rules. I have most of this done but am coming into some issues in the JQL where it's searching for issues created prior to the current issue.

I have tried various different ways,

cf[11618] ~ "{{issue.customfield_11618}}" AND {{created.isBefore(issue.created)}}

cf[11618] ~ "{{issue.customfield_11618}}" AND "{{created.isBefore(issue.created)}}" = true

cf[11618] ~ "{{issue.customfield_11618}}" AND "{{created.isBefore(issue.created)}}" = "true"

but none of these work. After a lot of trial and error I eventually came to using

cf[11618] ~ "{{issue.customfield_11618}}" AND created < "{{issue.created.jqlDateTime}}"

but this is still catching the triggering issue.

The only suggestion I found online was to use the advanced compare condition instead of JQL but I need JQL for this for the branching. If anyone has any suggestions please share.

Accepted answers

Bill Sheboy

Hi @[deleted]

Have you tried adding a clause to the JQL to exclude the trigger issue, such as:

AND key != {{triggerIssue.key}}

Kind regards,
Bill

All comments

Bill Sheboy

Hi @[deleted]

Have you tried adding a clause to the JQL to exclude the trigger issue, such as:

AND key != {{triggerIssue.key}}

Kind regards,
Bill

unknown

I used

key != {{issue.key}}

It does exclude the trigger issue, but I shouldn't need to do that if I could use the datetime smart fields. I guess that's my main question, is it possible to use the boolean datetime smartfields in automation JQL, and if it is how?

Bill Sheboy

One big challenge is JQL is not a SQL...and so all of us regular customers do not know exactly how it is performing comparisons of things like datetime types. Instead we must assume date comparisons are accurate when like fields are compared. (Aside: that is apparently not true for some things, such as related to history comparisons with the WAS operator, which has several open defects.)

To confirm we are aligned on your rule, would you please post images of your complete rule and the action details? Those will provide some context. Thanks!

I see you tried adjusting datetime formats with some of your example JQL statements, however we cannot easily adjust the format of "created" for any searched issues to confirm like matching. So perhaps try the absolute minimum:

project = yourProjectName
 AND cf[11618] ~ "{{triggerIssue.customfield_11618}}"
 AND created < {{triggerIssue.created}}

The created field is a datetime and hopefully this will compare correctly for you.

unknown

I did try that first, but got the following error.

"(cf[11618] ~ "bc04ffe5-baee-46c4-bd38-c57f6d2ca07c" AND created < 2023-07-13T13:54:14.5+0000) AND (project in (12634))" - Error in the JQL Query: The character '+' is a reserved JQL character. You must enclose it in a string or use the escape '\u002b' instead. (line 1, character 88)

I tried

project = yourProjectName
 AND cf[11618] ~ "{{triggerIssue.customfield_11618}}"
 AND created < "{{triggerIssue.created}}"

and got this error

"(cf[11618] ~ "bc04ffe5-baee-46c4-bd38-c57f6d2ca07c" AND created < "2023-07-13T13:54:14.5+0000") AND (project in (12634))" - Date value '2023-07-13T13:54:14.5+0000' for field 'created' is invalid. Valid formats include: 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd', or a period format e.g. '-5d', '4w 2d'.

I tried

project = yourProjectName
 AND cf[11618] ~ "{{triggerIssue.customfield_11618}}"
 AND {{created.jqlDateTime}} < {{triggerIssue.created.jqlDateTime}}

and got this error

"(cf[11618] ~ "bc04ffe5-baee-46c4-bd38-c57f6d2ca07c" AND 2023-07-13 13:54 < 2023-07-13 13:54) AND (project in (12634))" - Error in the JQL Query: Expecting operator but got '13:54'. The valid operators are '=', '!=', '<', '>', '<=', '>=', '~', '!~', 'IN', 'NOT IN', 'IS' and 'IS NOT'. (line 1, character 68)

I tried

project = yourProjectName
 AND cf[11618] ~ "{{triggerIssue.customfield_11618}}"
 AND "{{created.jqlDateTime}}" < "{{triggerIssue.created.jqlDateTime}}"

and got this error

"(cf[11618] ~ "bc04ffe5-baee-46c4-bd38-c57f6d2ca07c" AND "2023-07-13 13:54" < "2023-07-13 13:54") AND (project in (12634))" - Field '2023-07-13 13:54' does not exist or you do not have permission to view it.

And that is when I tried the one that finally worked.

It's not ideal but adding the 'key != key' does make it do what I need, untill I do run into a place where I need the date smart values. Thanks for taking a look and giving your suggestions.

Welcome!

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

Quick Links

Find more posts tagged with

cloud

jira-software

jira-software-cloud

automation