Query to display Initiatives that are linked with Epics that have additional criteria

[Gregory Kremer]

Hi all, 

Could someone please assist me with a query that would grab Initiatives that have Epics with Field1 = ABC and Field2 = XYZ ?

I would greatly appreciate prompt assistance. And yes, we have ScriptRunner installed.

Again, thank you in advance for your help.

As @Trudy Claspill and I were trying to figure out what I am doing wrong and so far couldn't, is there someone from Adaptavist who would be able to help in resolving my problem? I'd greatly appreciate it.

[Kate Kabir]

Hi @GRE 

Allow me to chip in on this matter. 

In regard to this, ScriptRunner Enhanced Search does not provide the same portfolioChildrenOf() and portfolioParentOf() JQL functions that the server version provides in order out of the box in order to search for Portfolio issues which are now called Advanced Roadmaps and their hierarchy inside of Jira Cloud as the API to provide this function does not exist in the Jira Cloud as it does in Jira Server.

 In addition, I will give you a documentation page where you will be able to compare the functionalities in Server vs Cloud.

Feature Request

We would like to invite you to upvote the Nolt raised for the portfolioParentOf() JQL function so that our Product Manager will review this. 

• https://scriptrunner-for-jira-cloud.nolt.io/47

I hope this helps.

Kind Regards

Kate

[mauricio.groth]

Hi @Gregory Kremer 

I’m Maurício, a support engineer at Appfire and I’m here to help you.

Unfortunately, using JQL of Jira, you’ll not be able to do it dynamically.

In the app where my team works, JQL Search Extensions for Jira, you can use this query to find all Initiatives that have Epics with Field1 = ABC and Field2 = XYZ:

issue in parentsOfIssuesInQuery("type=Epic and Field1 = ABC and Field2 = XYZ")

Please contact our support if you have any other questions about this query.

We’ll be happy to help you!

Best regards,

Maurício

[Gregory Kremer]

@mauricio.groth Thank you for trying to help. Unfortunately this doesn't fo==wrok for me:

Please let me know what am I doing wrong. I'd appreciate it.

[Trudy Claspill]

@Gregory Kremer 

You would need to use the JQL Search Extensions for Jira app that @mauricio.groth  mentioned. Their solution does not work with ScriptRunner.

[mauricio.groth]

Hi @Gregory Kremer 

As per your screenshot, I believe you have tried to run the query in the Jira’s advanced JQL search. In order to use the JQL Search Extension, it is necessary to download it from the marketplace first, and then access it via “Apps”.

To run the query inside our app, please follow these steps:

1. Access Apps
   
2. Click on JQL Search Extensions
3. Run the query here
   

To use our functions in Jira’s JQL, you need to save the query as a filter inside our app and then use it in the Advanced Issue search.

Best Regards,
Maurício Groth

[Gregory Kremer]

Oh OK, I got it. Unfortunately I we not in the position to invest into additional extensions. As far as I am concerned, ScriptRunner should work, but it's not.

[Trudy Claspill]

Hello @Gregory Kremer 

There is not a native JQL capability to get what you want in a single filter.

Are you open to using third party apps to achieve your requirement?

[Gregory Kremer]

Hi @Trudy Claspill

It's nice to hear from you again. As I mentioned above, we have ScriptRunner installed. I was hoping to use "issueFunction" of the ScriptRunner Enhanced Search.

If this is a 3d party you are referring to, then yes, I am OK using it.

I've written such a query, but it doesn't see to work correctly:

issueFunction in parentsOf("issuetype = Epic and "Planned Quarter" = 2023Q3 and "Planning Commitment" = Yes")

That's where I need some help.

[Trudy Claspill]

Oh, sorry, I missed that you said you have Scriptrunner.

What type of field is "Planned Quarter"?

If you try to retrieve jus the Epics (without the parentsOf function), do you get results?

issuetype = Epic and "Planned Quarter" = 2023Q3 and "Planning Commitment" = Yes

[Gregory Kremer]

@Trudy Claspill yes, I get 8 records. Here is how my "regular" query looks like:

issuetype = Epic and "Planned Quarter[Dropdown]" = 2023Q3 and "Planning Commitment[Checkboxes]" = Yes and "Parent Link" is not empty

[Trudy Claspill]

What are the field types for Planned Quarter and Planning Commitment?

[Gregory Kremer]

@Trudy Claspill "Planning Commitment" is a checkbox with either Yes or Null, and "Planned Quarter" is a single select .

[Trudy Claspill]

If you take the same text that work

issuetype = Epic and "Planned Quarter[Dropdown]" = 2023Q3 and "Planning Commitment[Checkboxes]" = Yes

...and paste it into the Scriptrunner Enhanced Search dialog for Insert Functions and Users...

...and run that search then you don't get any results?

Is the JQL Synch Status showing as "fully synched"?

[Gregory Kremer]

We are in Cloud, so I don't have Functions/Users screen. We have 
a ScriptRunner Enhanced Search screen where we input our query:

And as you could see Sync Status is Fully Synced, but no records are being fetched, while regular query brings 7 records;

So my hope is that someone will tell me what I am doing wrong here. Thank you @Trudy Claspill for your time. I truly appreciate it and hope you'd be able to assist.

[Trudy Claspill]

I am also in Jira Cloud. You get the Functions/Users screen by clicking on the + in the search field.

I don't see anything obvious (to me) that is wrong with what you're doing.

Are the Initiatives in the same project as the Epics?

Are either Epics or Initiatives in Team Managed projects?

If you reduce the criteria for the Epics in the Enhanced Search screen do you get any results? For instance if you have just issuetype=Epic do you get results? And then add to that status != Rejected do you still get results? If you build up that part of the filter one criteria at a time does it give you results until one of the specific criteria is added.

[Gregory Kremer]

Got it, thanks for the pointer - never used it. I've tried to make my sub-query as simple as possible, having JUST Epic and still no results. But here is the deal. When I use your method of creating sub-query it shows me 7 records, but when I run enhanced query it still comes back with 0:

For the life of me I can't understand what I am doing wrong here. Unless I am using a wrong function (parentsOf).

[Trudy Claspill]

@Gregory Kremer 

Oh my. I just realized the issue.

parentsOf finds only parents of Subtask types of issues. It is not meant to find parents based on the issue hierarchy extension that is available for Advanced Roadmaps.

The ScriptRunner function is looking for data in the "Parent" field, whereas the extended issue hierarchy used the "Parent Link" field (currently) to track the parent issues.

I don't think ScriptRunner provides functionality specific to finding parent issues for the extended issue hierarchy.

[Gregory Kremer]

If this is true, then I think it is a great shame. I'll contact their tech support and ask to confirm (or not) that this is the case. Thank you Trudy. As always, your assistance is greatly appreciated. I hope that maybe someone from Adaptavist people here could chime in.