The Database Picker scripted field displays database records returned by a pre-configured SQL query from a connected database.

Examples for usage include:

Select Jira Project Example

This example outlines selecting a project from the Jira Projects database table.

The following serves as an example only - in practice there is an existing Project picker custom field provided out of the box which you would use.
The example below assumes that one database connection has already been set up with the name local.
  1. Enter a Field Name, Field Description and Field Note.

  2. In this example, select Local under Connection.

  3. The first SQL statement in Retrieval/Validation SQL is retrieving a value from the database to display.

    select id, pname from project
      where id = cast(? as numeric)
    • id is the data you wish to retrieve and store in the Jira database. In this case, the Project ID.

    • pname is the data you wish to display in the field on the View Issue screen. In this case Project Name.

    • project is the name of the queried table (for this example, jiradb.project).

    • cast is required in this case, as the project.id column (the ID column being queried) in the Jira database schema is numeric. Note that if using mysql you may need to cast to SIGNED.

    • ? is the stored value (in this case, ID).

      where id = cast(? as numeric) ensures that the ID is cast as a numeric. If the ID column is alphanumeric, this is not required.
  4. The second SQL statement in Search SQL retrieves a value from the database to display.

    select id, pname from project
      where lower(pname) like lower(?) || '%'
    • lower changes input into lower-case for both the input value and the column being searched, allowing for a case-insensitive search.

    • ? is the parameter filled with what the user types into the custom field drop-down.

    • The query concatenates the input with a %, which means "match anything starting with this". So as the user types, this query executes, and the project name results are displayed.

      The || operator is a SQL standard, but verify the query using the target database…​ in mysql for instance you would need to use concat(lower(?), '%')'.
      Without the % character, the control does not show anything unless the end-user types something that is an exact match to the database record.)
      project picker usage
  5. Click Add to create the field.

Now create an issue and test the field:

  1. Add the scripted field to any screens or projects and enter a value on an issue that has this field in its context.

  2. Return to the Script Fields screen, click the Cog and select Edit on the field just created.

  3. Set the Preview Issue Key to be the issue just created.

  4. Click Preview to see how the new field displays.

project picker preview

Modify the SQL query to change what information is shown, giving you more control over the display. In this example of picking a project from the Jira database, to display both the project name and the key we could use:

select id, pname || ' - ' || pkey from project
  where id = cast(? as numeric)

If you want more control over how the field renders, please get in touch.

Select from Excel Sheet

There are JDBC drivers available for all commonly-used databases and Excel and CSV files. Therefore, you can connect to a spreadsheet containing a list of items you want to be available in a custom field.

  1. First, add the spreadsheet as a connection in setting up an external database connection.

  2. Create a scripted field.

  3. Give the field an appropriate name and description then enter the following into Retrieval/Validation SQL:

    select id, "First Name" || ' - ' || "Experience Level" from devs
      where id = ?
  4. Enter the following into Search SQL:

    select id, "First Name" from devs
      where "First Name" like ? || '%'
csv field picker

When applied to an issue context, the results display as a drop-down:

csv pick user

Have questions? Visit the Atlassian Community to connect, share, and learn with other Atlassian users and experts, including Adaptavist staff.

Ask a question about ScriptRunner for JIRA, Bitbucket Server, or Confluence.

Want to learn more? Check out courses on Adaptavist Learn, an online platform to onboard and train new users for Atlassian solutions.