...
The concept of script parameters is to allow the SQL or SIL scripts to get data from another fields on the issue. This will allow the query to retrieve different results based on the dependent fields.
SQL parameters
There are two types of parameters you can use inside SQL scripts:
Internal parameters
The syntax of an internal parameter is {:columnIndex} where columnIndex is a number that represents the position of the column in the field configuration (Columns). The columnIndex of the first column is 1.
The internal parameters are used when you want to obtain the current values for the field’s columns.
Example
Let’s assume we have the following list of columns for a field configuration:
If you want to insert or update a record in the database, you click on the Insert/Update (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/544113815/Using+the+Advanced+Database+Row+Field#Set-the-value-of-a-generic-field) option, you fill in the form values and then you submit it, triggering the run of the INSERT/UPDATE script.
Example of INSERT script:
| Code Block |
|---|
INSERT INTO |
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
Where {:1}...{:4} are the parameters corresponding to the columns and their values come from the interface.
{issue:customfield_11300} is the set value of the custom field in the issue.
| Info |
|---|
If you are in Edit screen and you want to get the value displayed on the custom field you should use |
After setting up the scripts and saving the configuration go to an issue and insert/update the field.
...
The internal parameters are used to refer the actual values that were filled in the Insert/Update form.
Similarly, let’s take a Search form with inputs that correspond to columns in the configuration:
In order to use their values when running the SEARCH script, they will have to be referred to using internal parameters.
Example of SEARCH script:
| Code Block |
|---|
SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries
WHERE code LIKE '%' || {:2} || '%' |
External parameters
The syntax of an external parameter is {screen:fieldNameOrId} or {issue:fieldNameOrId}.
Use issue prefix for parameters if you want to employ their value stored in the Jira issue.
Use screen prefix for parameters if you want to employ their value displayed in the screen.
This can happen when someone is creating a new issue and has not yet submitted it, so the issue value doesn’t exist. Or simply when you want to correlate with the value of that field from the current screen as it might have changed from what is saved in the Jira issue.
The fieldNameOrId can be chosen from:
standard variables - This is the list of fields that come out-of-the-box with Jira like assignee, description, summary, etc. For a full list of fields see Variable Resolution
custom fields - This is a complete list of custom fields from your Jira instance
Example of SEARCH script with external parameters:
| Code Block |
|---|
SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries
WHERE name LIKE {screen:summary} || '%' |
Important: external parameters need to be explicitly declared in the Script parameters multi-select, otherwise their values will not be correctly calculated:
The previous script means the values that will be returned by the Search are those database records that have their name column starting with the screen value of the summary Jira standard field.
SIL parameters
| Table of Contents |
|---|