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.
Enter a Field Name, Field Description and Field Note.
In this example, select Local under Connection.
The first SQL statement inRetrieval/Validation SQLis retrieving a value from the database to display.
select id, pname from project where id = cast(? as numeric)CODE
idis the data you wish to retrieve and store in the Jira database. In this case, the Project ID.
pnameis the data you wish to display in the field on the View Issue screen. In this case Project Name.
projectis the name of the queried table (for this example, jiradb.project).
castis required in this case, as the
project.idcolumn (the ID column being queried) in the Jira database schema is numeric. Note that if using mysql you may need to cast to
?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.
The second SQL statement inSearch SQLretrieves a value from the database to display.
select id, pname from project where lower(pname) like lower(?) || '%'CODE
lowerchanges 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.
||operator is a SQL standard, but verify the query using the target database… in mysql for instance you would need to use
%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.