S-JQL formula for finding issues with missing links

[Mark Steudlein]

I am attempting to create a formula as a column of a Structure in order to label an issue that has no child issues linked. Recently I added a new issue type on our test environment that I am calling "requirement". I am linking requirements to Stories, then Stories to tests (Zephyr plugin). Essentially it follows this path: Requirement is implemented by Story, Story is implemented by Test. 

I have tried playing with the Formula area while trying to add a new column. I started out with the following:

issue in structure ("structure_name", "[type = Requirement] and child not in [type in (Story)]")

This seems to only place "???" in the column as it is not doing what I am trying to do. How would I get it to say "Full Trace" for items that have full requirement-story-test linkage? Also say "missing test" if requirement-story-No child, and "Missing Story" for Requirement-No child?

[Egor Tasa [ALM Works] · Accepted solution]

Hi Mark,

Sorry, I forgot to put "*Story*" there, since asterisks are recognized as wiki markup in text only replies. I often use SEARCH function instead, as it does not require wildcards, but it also requires reverse order of arguments. So you can either put asterisks around story or use SEARCH("story", JOIN#subtree#children{issuetype}) Same with sub-task

Regards,
Egor

[Mark Steudlein]

Your examples helped immensely. I have near zero knowledge of JIRA JQL and Structure so this helped me learn some new stuff. I made some minor modifications and ended up using two formulas. This gets be about 95% of the way to what I was looking for. 

Here are the final formulas:

Requirement Trace

IF (issuetype="Requirement" and MATCH(JOIN#subtree#children{issuetype}, "*Story*") and MATCH(JOIN#subtree#leaves{issuetype}, "*Test*"); "{color:lime}Trace Complete{color}"; issuetype="Requirement" and !MATCH(JOIN#subtree#children{issuetype}, "*Story*"); "{color:red}Story missing{color}"; issuetype="Requirement" and !MATCH(JOIN#subtree#leaves{issuetype}, "*Test*"); "{color:red}Test missing{color}")

Trace Issue

IF (issuetype="Requirement" and !MATCH(JOIN#subtree#children{issuetype}, "*Story*"); "No Story"; issuetype="Story" and !MATCH(JOIN#subtree#leaves{issuetype}, "*Test*"); "No Test")

The lingering issue is for the Requirement Trace. In Test Requirement 5, the Requirement Trace returns "Trace Complete" since there is at least one story and one test. However, there is one Story that is missing a Test. This gets picked up by the Trace Issue column. Not a big deal, and like I said it gets me nearly to what I was looking for. Thanks again. 

[Egor Tasa [ALM Works]]

Hi Mark,

IF (issuetype="Requirement" and !MATCH(JOIN#subtree#children{issuetype}, "*Story*"); "No Story"; issuetype="Story" and MATCH(JOIN#subtree#leaves{issuetype}; "Story"); "No Test")

Should work if you need to avoid stories with no Test children.

Regards,
Egor

[Egor Tasa [ALM Works]]

Hi Mark,

With Structure 6.1 you can use JQL and S-JQL in columns and formulas now. However, there does not seem to be the need for that in this case. You can use aggregation instead:

IF (issuetype="Requirement" and MATCH(JOIN#subtree#children{issuetype}, "Story"); "Story present"; "Stories missing")

similarly

IF (issuetype="Requirement" and MATCH(JOIN#subtree#children{issuetype}, "Story") and MATCH(JOIN#subtree#leaves{issuetype}, "Test"); "Full Trace"; issuetype="Requirement" and !MATCH(JOIN#subtree#children{issuetype}, "Story"); "Story missing"; issuetype="Requirement" and !MATCH(JOIN#subtree#leaves{issuetype}, "Test"); "Test missing")

Note that this works only if corresponding children are brought into the structure by extenders, formula would not check outside of structure scope.

Regards,
Egor

[Mark Steudlein]

The first formula seems to work, but has an issue if the Requirement has multiple stories. 

The second provides a similar (but cleaner) look. Same issue when a requirement has multiple stories. Also the Test Requirement 5 - Test Story 5 does not have a test case. How can I get this one to same Test Missing? Or would this formula only work on the requirement level?

*sorry for the poor markup on the screenshots