Versions Compared

Old Version 7

changes.mady.by.user Cristian Pintea

Saved on

compared with

New Version 8

changes.mady.by.user Shane Smith

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
We've encountered an issue exporting this macro. Please try exporting this page again later.

It is possible to search for specific values within a database field using JQL. The search can be done in two ways: using dbcfExpr JQL function or using issue.property syntax.

The following Advanced Row Field will be used as example for each of the two search options:

dbcfExpr JQL function

The dbcfExpr JQL function is available starting with version 6.1.820.2 and it requires only one argument: a valid JQL string. This JQL string is allowed to refer to any Advanced Database Row Field by its name.

Note

Please make sure the name of the Advanced Row Field is unique and it doesn’t clash with other fields you might have on the same Jira instance (name case doesn’t matter). Otherwise, searching by field name using dbcfExpr function might not work properly.

Here is how you can use it to search for issues:

Code Block
key in dbcfExpr("Country ~ Spain")
Info

If a custom field name (or value) contains spaces (or any special character), please enclose it in single quotes. For a field named, My Country with value Trinidad and Tobago the query would look like:

key in dbcfExpr("'My Country' ~ 'Trinidad and Tobago'")

If you only want to search for a certain column in the Advanced Database Row Field, you can, using the following syntax:

Code Block
key in dbcfExpr("Country.code ~ ES")

When you do not specify a column name the search is done on all columns.

Starting with version 6.2.820.3, to respond to some performance issue that were reported, the dbcfExpr function has an extra parameter that represents the maximum number of results to be shown:

Code Block
key in dbcfExpr("Country ~ Spain", maxResults)

maxResults is optional and it needs to be an integer value. If it is missing, the number of results is limited to a default value of 1000.

Info

It is highly recommended that if you need to apply additional filters to narrow down the search results, to do it inside the dbcfExpr condition, like this:

key in dbcfExpr("Country ~ Spain and project = DemoProject")

issue.property syntax

The complete syntax to search for issues using this method:

Code Block
issue.property["dbcf"].customfield_10300_name ~ Spain

This method only works using custom field id, not name or alias.

To see how this function works, lets break it apart:

  • Prefix - This part of the function is static. This syntax should never change:

Code Block
issue.property["dbcf"]

  • Field and label - The next part of the function is the id of the custom field that is getting searched (Ids will vary across instances. To look up the id of a custom field use the Custom Field Usage tool). After the underscore is the label, this corresponds to the column of the database field.

Code Block
customfield_10300_name

  • Operator and value - It is important to ensure that you are using the proper operator type.

    Code Block
    ~ Spain

    1. String - When searching strings it is recommended to use “~” and “!~” (contains and does not contain) instead of “=” and “!=” to get the appropriate results.

    2. Numbers and Dates - When searching the normal numerical operators will work, such as “=, !=, <, >

More Examples

  1. Search for issues missing a value for country:

    Code Block
    key in dbcfExpr("Country is empty")

  2. Search for countries having gdp_capita greater than 20000:

    Code Block
    key in dbcfExpr("Country.gdp_capita > 20000")

  3. Searching for a country by name and code:

    Code Block
    key in dbcfExpr("Country.name ~ Spain and Country.code ~ ES")
Table of Contents

See More

Child pages (Children Display)
pageAdvanced Database Row Field (Next Gen)