Feeding filters into other filters

[Jason J_ Fedelem]

I'm having issues trying to build some filters that I need.  They both share the fact that I need to build one filter, then substitute the results of that filter into another.

Example 1

I have to have a board where our COO can look at all the cards that he has determined to be a priority.  I don't want him getting into the weeds on how we accomplish the goal, just to see the goal.  This means that for the board, I exclude any non Epic that has an Epic link:

XYZ = "XYZ Priority" AND "Epic Link" is EMPTY

Where XYZ is a custom field checkbox that allows us to track this as a card that the COO cares about.  

However, I need to have my own list where I can track these cards to make sure they are moving thru the process.  So I can create a filter that looks like:

issuetype = Epic AND XYZ = "XYZ Priority"

Lets call this "Filter for Epic XYZ"

I want to then be able to make some statement like:

Epic Link in (Filter = "Filter for Epic XYZ").  

I can't seem to make this work. Error message is 

Error in the JQL Query: Expecting ',' or ')' but got '='. (line 1, character 25)

Example 2

I need a report that allows me to see cards that were marked blocked, but the blocking card is now in the "Done" status.  Its easy for cards to get buried and follow up to not happen when it needs to.

I'm thinking something like this, but I know this isn't formatted properly:

linkedIssue not in Resolved

I'm just not sure how to make that work.  I've tried a number of things, but haven't found the right combination.

Any help is appreciated.

[Jason J_ Fedelem]

Answer for use case 1:

Oh my word this got complicated.  I almost gave up several times.

The COO wanted his own board, where all he had to do was order things and not see the child cards.  That part was easy.

project = BLAH AND customField = XYZ AND status != "Done" AND "Epic Link" is EMPTY ORDER BY Rank ASC

So far so good.  

The second board is for everyone executing on these priorities so that these cards rise above the noise.  It also includes cards that members of middle management have marked as cards they consider important.

The first is the filter for the board, which I name Leadership Board v2:

project = BLAH AND "Epic Link" is EMPTY AND status != "Done" and customField is not EMPTY AND type != Epic OR issueFunction in issuesInEpics("mgmt is not EMPTY") AND status not in ("Done") AND type != "Epic" OR issueFunction in issuesInEpics("mgmt is EMPTY") AND status not in ("Done") AND type != Epic AND customField is not EMPTY

In English, this breaks down to three things:

1.  My customField is not empty, and it doesn't have an Epic link and isn't an Epic itself

2.  For a child of any Epic, my customField is not empty on the Epic itself, status of the child card is not Done and isn't an Epic itself

3.  For a child of any Epic, my customField is empty on the Epic itself, status of the child card is not Done and isn't an Epic itself

This gets me all the cards for the board. However, the next challenge is that I need both the cards that have the custom field marked as XYZ and the ones whose epic Link has its custom field marked as XYZ to show in the appropriate swim lanes for XYZ

So I build a query in ScriptRunner named Epic Link is XYZ Priority that says this:

issueFunction in epicsOf("project = BLAH AND filter = "Leadership Board v 2" AND customField is EMPTY order by created DESC") AND customField = "XYZ"

This basically says "give me all the cards on the leadership board where their customField is Empty, return their Epics, and give me all the Epics where custom Field is XYZ"

Then I need to get all of the children of these cards that are not in certain statuses:

issueFunction in issuesInEpics(filter = "Epic Link is XYZ Priority") AND status not in (<list of statuses>)

I make copies of these two filters for when customField is not equal to XYZ.

Now I can apply these filters to the swimlanes on the board.

It's all looking good now.  It just took way too long to build.

[Jason J_ Fedelem]

So I wanted to come back and here and post the result of this.

Atlassian Support confirmed that we could not do this with JIRA natively.  I confirmed that there was not a way to do this with Automator for JIRA which we were already paying for.

I signed up for ScriptRunner and got it working.

A couple of things about ScriptRunner that were not obvious to me at first:

1. ScriptRunner will sync the filter with JIRA, but when you are returning a list of cards in ScriptRunner dynamically, JIRA only displays the list of ScriptRunners results.  Fortunately ScriptRunner updates the filter every 5 minutes so its not a big deal, but they do appear to be visually different.

2.  The first search where you build the list of cards to run the second search against is done with Script Runner's language, all within parenthesis.  Then within script runner you can add your normal JQL filters.

3.  To handle use case 2 above regarding blocked cards, I had to use linkedIssuesOfRecursive

4.  Script Runner times out way too easily.  I had to limit the search to cards updated in the last 4 weeks, which is probably ok but not ideal.

5.  Filters for statuses within the scriptrunner piece have to have single quotes, not double.

The final query that returns all cards blocked is this:

issueFunction in linkedIssuesOfRecursive("status in ('blerg') AND updated > '-4w'", "blocks") and type != "blah" and status not in ("blerg")

I haven't built the filter for use case 1 but will post here when I do

[Kat Warner]

I use filters in JQLs but stick to the operators = or !=.

These are interesting puzzles but I was unable to find anything  help you solve them.

For example 2 I cannot think of how to combine "issueLinkType = "is blocked by" and then query the linked issues.

[Jason J_ Fedelem]

Thanks Kat!  I tend to agree that I can't seem to find a way to solve them.

Puzzle 1 seems like it should be solvable is I pass a filter instead of actual JQL.

Puzzle 2 I've played around with, but haven't come up with anything close.  

[Jason J_ Fedelem]

I should mention that the obvious answer for puzzle 1 is to specify the epic cards.  the problem with this is that when a new Epic card ends up mattering, I have to remember to manually change the filter.  This is not likely to happen until myself or the COO realizes that cards that need to be developed are not getting attention.

[Kat Warner]

Could you use a label to identify Epics on the COOs radar?

[Jason J_ Fedelem]

That's how I originally had it, but its the same problem because I still can't set up a JQL filter to say "you know that other dynamic filter?  Use those results here".  As far as I can tell, it doesn't matter if its a label or a custom field.