Select Jira Project

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

Warning: 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.

Note: The example below assumes that one database connection has already been set up with the name local.

Enter a Field Name, Field Description and Field Note.
In this example, select Local under Connection.
The first SQL statement in Retrieval/Validation SQL is retrieving a value from the database to display.
1
2
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).
Note:where id = cast(? as numeric) ensures that the ID is cast as a numeric. If the ID column is alphanumeric, this is not required.
The second SQL statement in Search SQL retrieves a value from the database to display.
1
2
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.
Tip: 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(?), '%')'.
Note: Without the % character, the control does not show anything unless the end-user types something that is an exact match to the database record.
Click Add to create the field.
Now create an issue and test the field:
Add the scripted field to any screens or projects and enter a value on an issue that has this field in its context.
Return to the Script Fields screen, click the Cog and select Edit on the field just created.
Set the Preview Issue Key to be the issue just created.
Click Preview to see how the new field displays.

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 see the Database Picker Customizations section.