New Custom Field Types

Thanks to the powerful "Database Picker" Script Field, you can create new types of custom field to display any information stored in the Jira and Golive database tables.

More about Database Picker Script Fields: https://docs.adaptavist.com/sr4js/6.44.0/features/script-fields/built-in-script-fields/database-picker

You should also have basic knowledges of SQL and Groovy syntax.

Provided example is working fine with Postgres database. You may need to change the concatenation operator depending on the type of used database: Concatenation Operator

ANSI SQL defines a concatenation operator (||), which joins two distinct strings into one string value.

MySQL

MySQL supports CONCAT as a synonym for the ANSI SQL concatenation operator and uses the || operator for logical OR.

Oracle and PostgreSQL

Oracle and PostgreSQL support the ANSI SQL double vertical bar (||) concatenation operator. Oracle also supports CONCAT as a synonym for the ANSI SQL operator.

MS SQL Server

SQL Server uses the plus sign (+) as a synonym for the ANSI SQL concatenation operator.

Example 1: Application Custom Field

Goal of this field is to be able to link jira tickets to a Golive application.

Configure a readonlyhttps://docs.adaptavist.com/sr4js/6.44.0/features/resources/local-database-connection to the local Jira Database and name it "Local".
Create a new "Script Fields" and select "Database Picker" in the list and set the different fields:

Application Custom Field - Retrieve/validation SQL

SQL

select "ID","NAME"
  from "AO_459C0D_APPLI"
  where "ID" = cast(? as numeric)

Application Custom Field - Search SQL

SQL

select "ID","NAME"
  from "AO_459C0D_APPLI"
  where UPPER("NAME") like UPPER(?) || '%'

Application Field - Configuration Script

SQL

select "ID","NAME"
  from "AO_459C0D_APPLI"
  where UPPER("NAME") like UPPER(?) || '%'

Test your Application Field

Once configured, select a ticket number and click on "Preview" button to check the configuration.

Once the preview is ok, just add your newly created field to desired Jira issue types and projects.

Create other Types of Custom Field

You can base yourself on the "Application Field" example to create other types of custom field. You can retrieve information from following tables in Jira database to get data from Golive:

Table Names

Golive Object Type	Table
Application	AO_459C0D_APPLI
Environment Category	AO_459C0D_ENV_CAT
Environment Status	AO_459C0D_ENV_STATUS
Environment Permission Scheme	AO_459C0D_ENV_TEAM

All these table have ID and NAME columns. You can use the following SQL scripts by just replacing "REPLACE_THIS_BY_THE_RIGHT_TABLE_NAME" by the corresponding table name:

Retrieve/validation SQL template

SQL

select "ID","NAME"
  from "REPLACE_THIS_BY_THE_RIGHT_TABLE_NAME"
  where "ID" = cast(? as numeric)

Search SQL template

PostgreSQL / Oracle

SQL

select "ID","NAME"
  from "REPLACE_THIS_BY_THE_RIGHT_TABLE_NAME"
  where UPPER("NAME") like UPPER(?) || '%'

MS SQL Server

SQL

select "ID","NAME"
  from "REPLACE_THIS_BY_THE_RIGHT_TABLE_NAME"
  where UPPER("NAME") like UPPER(?) + '%'

MySQL

SQL

select "ID","NAME"
  from "REPLACE_THIS_BY_THE_RIGHT_TABLE_NAME"
  where UPPER("NAME") like CONCAT(UPPER(?),'%')