Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,359,836
Community Members
 
Community Events
168
Community Groups

JQL query if user exists in custom field don't display in search result

Hi All,

  I have a bit of a challenge with compiling  JQL query to search on issues and scratching my head a little. Not even sure if it's possible as I've tried all the combinations.

Basically I have an "Assignee" field and "Additional Contributors" multi-user picker custom field. For the issue type when the "Assignee" or Additional Contributors" click a button to say they have completed their part, they automatically get added to another multi-user custom field labelled "Completed By".

In my JQL query filter I would like to show unresolved issues for the "Assignee and "Additional Contributors" but only if they do not exist in the "Completed by" field.

Can someone help me with the JQL logic to achieve this? Thanks.

2 answers

1 accepted

1 vote
Answer accepted

@Tom Lister thanks for your help. I managed to get it working with the following:

("Completed By" not in (currentUser()) OR "Completed By" is EMPTY OR Reporter = currentUser()) AND (assignee in (currentUser()) OR "Additional Contributors" in (currentUser())

Not working for me. It says below error.

 

An option provided by the function 'currentUser' for the field 'Developer[Select List (multiple choices)]' does not exist.

1 vote
Tom Lister Community Leader Jul 17, 2020

Hi @JBennett 

try

(assignee in (<yourusernames>) or <your username> in “Additional Contributors”) and <your username> not in “Completed by”

Thanks Tom. Didn't have any joy with that, I tried the following but it doesn't output the correct results.

(assignee in (currentUser()) or "Additional Contributors" in (currentUser())) and "Completed By" not in (currentUser())

It works up until this point:

(assignee in (currentUser()) or "Additional Contributors" in (currentUser())) and resolution = Unresolved

 

It's the "Completed By" bit that's causing the headache with this query :-(

Tried the following too but no joy :-(

 

(assignee in (currentUser()) AND "Completed By" not in (currentUser())) OR ("Additional Contributors" in (currentUser()) AND "Completed By" not in (currentUser()))

Tom Lister Community Leader Jul 17, 2020

try

currentUser() not in "Completed By"

It wont let me run the query around that way?

Tom Lister Community Leader Jul 18, 2020

"Completed by" != currentUser() works for me ( using a similar user picker on my system)

What's really frustrating is if I run the following query it returns all of the issues where the currentuser is either the assignee or additional contributor and is in the Completed by field. This is basically backwards to what I need to do so you would think it should be simple but it appears not? Unless I'm just getting confused....

(assignee in (currentUser()) OR "Additional Contributors" in (currentUser())) AND "Completed By" in (currentUser())

Sorry, tried again you are right. Tried the following but still no joy:

(assignee in (currentUser()) OR "Additional Contributors" in (currentUser())) AND
"Completed by" != currentUser()

Tried the following and still no joy. It's the AND bit which seems to be the issue.

(assignee = currentUser() OR "Additional Contributors" = currentUser()) AND "Completed by" != currentUser()

Tom Lister Community Leader Jul 18, 2020

Hi

this works on my test server

("Request participants" in (currentUser()) OR assignee in (currentUser()) ) and Recipients not in (currentUser())

tried with both single and multi user pickers. But I'm only checking syntax is accepted not correct results.

What is the error you see or is it incorrect results?

from https://support.atlassian.com/jira-core-cloud/docs/advanced-search-reference-jql-operators/

You may need to also check for empty

The "NOT IN" operator will not match a field that has no value (i.e. a field that is empty). For example, assignee not in (jack,jill) will only match issues that have an assignee and the assignee is not "jack" or "jill". To find issues that are assigned to someone other than "jack" or "jill" or are unassigned, you would need to type: assignee not in (jack,jill) or assignee is empty.

Tom

Hopefully this helps make sense of what I'm trying to achieve. The top query is without the "Completed By". As you can see in the bottom query DDREVIEW-1190 is missing which I would have expected to appear?

Query Issue.png

Tom Lister Community Leader Jul 18, 2020

Hi

I think it might be the empty Additional Contributors

although I'm not able to test this without doing a chunk of setup

(assignee in (currentUser()) 

OR ("Additional Contributors" in (currentUser()) OR "Additional Contributors" is empty) )

AND ("Completed by" != currentUser() AND "Completed by" is not EMPTY)

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
VERSION
8.7
TAGS

Atlassian Community Events