How to Use Script Parameters

The concept of script parameters is to allow the SQL or SIL scripts to get data from the UI of the field or even 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 that can be used inside SQL scripts:

Internal parameters

The syntax of an internal parameter used in SQL scripts 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:

INSERT INTO countries(id, code, name, independence, gdp_capita, capital, is_eu) 
VALUES ({:1}, {:2}, {:3}, {:4}, {:5}, {:6}, {:7})

Example of UPDATE script:

UPDATE countries SET code = {:2}, name = {:3}, independence = {:4}, gdp_capita = {:5}, capital = {:6}, is_eu = {:7} 
WHERE id = {:1}

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:

SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries 
WHERE code LIKE '%' || {:2} || '%'

External parameters

The syntax of an external parameter used in SQL scripts 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 Standard Variables

• custom fields - This is a complete list of custom fields from your Jira instance

Example of SEARCH script with external parameters:

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

When using a SIL script in the configuration of a field you will benefit from a rich variety of routines to help you reach the desired results. The same two types of parameters can be used inside SIL scripts:

Internal parameters

The syntax of an internal parameter used in SQL scripts is argv[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 0.

Example of SEARCH script:

string sql = "SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries WHERE code LIKE ? || '%'";
string[] vals = sql("postgresDB", sql, argv[1]);
return vals;

This above script will retrieve the same results that its SQL equivalent.

External parameters