• Community
  • Questions
  • JQL JIRA query for custom field being equal to two other custom field values

JQL JIRA query for custom field being equal to two other custom field values

I want to query my project for all issues where 

customField1 != value(customField2) + value(customField3)

Is it possible to do this in a JQL query?

I tried "cf[123]!=cf[456]+cf[789]", but that didn't work.

3 answers

1 accepted

Scriptrunner has a scripted JQL function which could help as long as your custom fields are date, datetime or numeric fields.

see here: https://scriptrunner.adaptavist.com/latest/jira/jql-functions.html#_expression


This is cool, but I really think another library is overkill for this. All I want to do is a pure JQL jquery that checks for one field being equal to the sum of two others. Surely there's a way to do this in pure JQL.

The JQL functions provided by Scriptrunner plugin can just do this. Your attempt will never work since you can't compare two (or more) fields in out of the box JQL.

1 vote
Stephen Deutsch Community Champion Feb 14, 2016

Are you trying to say that customfield1 should be not equal to neither customfield2 nor customfield3?

In that case it would be like this: cf[123]!=cf[456] AND cf[123]!=cf[789]

If you are trying to actually add the fields together, that would be a different query.

I'm trying the latter, unfortunately. I'm looking for cf[123] where cf[123] is not equal to the sum of two other custom field values.

You can try using Script Field provided ScriptRunner plugin. Here is code example to get diffenrence between two date custom fields:

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.fields.CustomField

import java.sql.Timestamp

 * Get number of days between to customfields

CustomField startDate = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Дата выдачи согласованного MD.050");
CustomField endDate = ComponentAccessor.getCustomFieldManager().getCustomFieldObjectByName("Дата выдачи в тестирование.План");

if ((startDate == null) && (endDate == null))
    return ""

long startDateInMls = ((Timestamp) startDate.getValue(issue)).getTime();
long endDateInMls = ((Timestamp) endDate.getValue(issue)).getTime();

if ((startDateInMls == 0) && (endDateInMls == 0))
    return ""

return "" + ( startDateInMls - endDateInMls)/(1000*60*60*24)

This plugin is avalable only for server version.

Hm I really need a pure JQL query to do it, because I'm just using a node.js app to run this. It should be very lightweight, and this is really the only thing it does, so there should be a way to do this just in JQL

Suggest an answer

Log in or Join to answer
Community showcase
Jason Wong
Published yesterday in Agility Beta

Welcome to agility

Every team in the world is unique, and so   Atlassian believes   that each and every team's best way of working  needs to  be molded to their unique circumstances  – ...

264 views 5 14
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot