Running a query from inside a Jira issue

[#1]

Background

In one of our Jira projects, we have a cascaded select field in which the parent is the product name and the child is the customer name.

The field is called 'Prod-Cust'. So we have values like this:

• ProdA - Customer1
• ProdA - Customer2
• ProdB - Customer2
• ProdB - Customer3

Problem Description

The owner of the project raised a challenging requirement:

In an issue, have the ability to display the last 10 issues in the project for the same Product-Customer. 

Possible Approaches

My first thought was to use a webhook to send a request to Jira, but the overhead of using webhook is quite large.

So next I looked at using 'lookup issues' based on smart values with lists. But it looked complicated.

I then realized that a search in Jira can be executed by sending the browser a full search URL, which is the Jira URL concatenated with /issues/?jql=<JQL-here>.

An advantage is this approach is that your browser is already logged in to Jira, so you don't need any authentication - just send the URL.

Solution

So I came up with this plan (let's call it Plan A, you will soon see why).

Plan A

1. Create a new custom field of type URL, add it to the relevant screens
2. Create an automation to populate the new field
   1. The automation extracts the parent and child values from the cascade field into 2 smartvalues (see below how), then generates the search URL (see below).
   2. The URL must be percent-encoded (e.g., a double quote is represented by %22, a space by %20, etc.)
3. For searching the other tickets, see the JQL syntax below
4. The automation is triggered by the Create Issue/Update Issue events 
5. Once the URL field is populated, the user can click on it and a new browser window/tab opens with the search results

This worked fine until it started failing. The reason is that URL fields in Jira cloud are limited to 255 characters (there is an open feature request to remove this limitation). So some search URL were longer than 255.

So I came up with Plan B.

Plan B

1. Create a big text field instead
   1. Make sure it uses a wiki-style renderer (so the link is clickable)
2. The rest is steps 2-5 in Plan A

This works without a hitch.

Reflections and Limitations

1. Ideally, Jira will have a field type of button with an action tied to it (like all modern web frameworks have). Since there isn't, a clickable field is the closest thing.
2. The automation takes 4 seconds to execute and it takes 10 seconds until the URL field is populated
3. I can trigger the automation using a schedule, but since the project has > 30K, that would cause an unnecessary load on Jira
4. The requestor wanted to display only the most recent 10 issues, but Jira search does not support limiting the results (yet another known gap in Jira).

The Gory Details

1. Getting child/parent values from a cascade field into a smartvalue variable:

{{issue.your-field-name.value}}        // Parent
{{issue.your-field-name.child.value}}  // Child

2. The JQL for searching by a value of a cascaded field must use this syntax:

 <your-field-name> in cascadeOption(<parent-value>, <child-value>)

3. Construction of the text that goes into the big text field:

[Find related tickets|https://xyzzy.atlassian.net/issues/?jql=project%20%3D%20MY-PROJ%20%20and%20%22Prod-Cust%22%20in%20cascadeOption(%22{{SmartValueParent}}%22%2C%20%22{{SmartValueChild}}%22)%20ORDER%20BY%20CREATED%20DESC]