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

[Chris Reichenbach]

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.

[Bill Sheboy · Accepted solution]

Hi @Chris Reichenbach 

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

AND key != {{triggerIssue.key}}

Kind regards,
Bill

[Chris Reichenbach]

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.

[Chris Reichenbach]

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.

[Bill Sheboy]

I am glad to learn that work-around helped, and...

This one is curious...as you appear to be on a paid, Jira Standard license, I recommend working with your site admin to submit a support ticket to have Atlassian take a look.  They have access to logs which we users do not, and so they may see something we are missing.

[Chris Reichenbach]

Yeah, I think I'm going to have to try opening a support ticket. I ran some tests after I added the key filter and when I ran my rule on the older ticket it found the newer one. I would expect that the 

AND created < "{{issue.created.jqlDateTime}}"

clause should stop that from happening.