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 screen or issue. This will allow the query to retrieve different results based on the values of dependent fields.

There are two types of parameters that can be used inside the configuration scripts:

Internal parameters

The internal parameters are needed when you want to use the UI values of the field’s columns. Here is a comparison of how the internal parameters are used in SQL & SIL scripts:

columnIndex is a number that represents the position of the column in the field configuration (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 scripts:

1. SQL

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

2. SIL

string sql = "INSERT INTO countries(id, code, name, independence, gdp_capita, capital, is_eu) VALUES (?::int, ?, ?, ?::date, ?::real, ?, ?::boolean)";
sql("postgresDB", sql, argv[0], argv[1], argv[2], argv[3], argv[4], argv[5], argv[6]);

Example of UPDATE scripts:

1. SQL

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

2. SIL

string sql = "UPDATE countries SET code = ?, name = ?, independence = ?::date, gdp_capita = ?::real, capital = ?, is_eu = ?::boolean WHERE id = ?::int";
sql("postgresDB", sql, argv[1], argv[2], argv[3], argv[4], argv[5], argv[6], argv[0]);

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 scripts:

1. SQL

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

2. SIL

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;

External parameters

The external parameters are needed when you want to use the values of another Jira fields.

There are two types of external parameters: issue and screen.

Use issue prefix for parameters if you want to employ the field value that is stored in the Jira issue.

Use screen prefix for parameters if you want to employ the field value that is displayed in the screen. This can be useful 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.

Here is a comparison of how the external parameters are used in SQL & SIL scripts:

The fieldNameOrId can refer to:

• 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:

1. SQL:

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

2. SIL:

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

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. If the issue has ‘Sw’ value for summary, countries like Sweden and Switzerland will both show up in the search results.