Database Lookup++

Sometimes information you like to show may already exist in the database, either in JIRA's own database or in a different corporate database. Database Lookup++ field allows you to select a value from database based on the any custom field of the current issue. It is better explained with a sample scenario. 

Lets assume that you have a customer single select field. When you select a customer on the issue using this field, you also want to display total order count of the user from your order database of e-commerce site. Using Database Lookup++ field you can easily do this. Of course this is just a sample scenario, you may come up with different use cases.

To use Database Lookup++ field, first you need to define a datasource using add-on's configuration. If you have already defined a datasource previously, of course you can use it too. After defining your datasource you need to add new custom field of type Database Lookup++ and associate it with screens. Since this part is already well explained in JIRA's own user manual, we won't go details of it here.

Configuration of Custom Field


When the field is added initially, it is not configured and will not show any value on the issue. On the field configuration schema, click on 'Edit Database Lookup' link to open field configuration screen. 


There are two information you need to enter, the datasource to use and SQL Select. Datasource is obvious and determines which database to connect. You can write any SQL, even if multiple records returned from it, only the first column of the first row will be used


Database Lookup Custom Field Configuration


When you save the field configuration the SQL will be verified and any syntax error will be displayed on the page. After saving database lookup settings, you can return to field configuration screen by pressing 'Back' link, or using browser's back button. Currently active configuration will be displayed on the field configuration page. 


Using Expressions

Value of some build-in fields like reporter, or build in fields can be an object. For example reporter is an instance of ApplicationUser, and value of single and multi select fields are Option. For these kind of objects you can navigate to fields of object using '.' notation. For example:

Option Field (Single & Multi Select Fields):
{customfield_10100.id} //use id field of option which is a numeric value
{customfield_10100.value} //use value field of option which is string value (displayed value) of option
{customfield_11400.[0].optionId} //use option id of first item for a multiselect custom field

Any User Field:
{customfield_10100.key} //unique identifier of user
{customfield_10100.displayName} //name of user as displayed on Jira UI
{customfield_10100.emailAddress} //email of user