More examples of Read-only Advanced Database Row Fields

After creating a new Advanced Database Row Field, you can configure it either via the Wizard or directly from the Advanced Configuration page.

There are three types of read-only Advanced Database Row Fields:

• Single select (S) - allows the selection of a single value from a list that is populated when the input is selected:

• Multi select (M)- allows the selection of multiple values from a list that is populated when the input is selected:

• Autocomplete (A) - allows the selection of a single value from a list that is populated according to the autocomplete settings (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15481863/Columns#Searchable):

Columns configuration

They all require exactly two columns in the configuration, one of which acts like a primary key, while the other is either Searchable = ‘Yes’ (S or M) or Searchable = ‘Autocomplete’ (A).

Example for S or M:

Example of A:

Scripts configuration

The mandatory scripts for the field’s core functionality (select and save values) are GET and SEARCH. These are automatically generated if you decide to use the configuration Wizard.

GET scripts use the primary key as parameter - this query executes whenever Jira needs to retrieve the value of the field (here is where you set the frequency of these calculations: https://appfire.atlassian.net/wiki/spaces/PSJ/pages/540574265/General+settings+for+read-only+fields#Refresh-type).

Example:

SELECT id, name FROM countries WHERE id = {:1}

SEARCH scripts for S & M don’t depend on any of the field’s internal parameters (described here https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15487441/How+to+Use+Script+Parameters#Internal-parameters).

Example:

SELECT id, name FROM countries WHERE name IS NOT NULL

The A field is the only one that should depend on internal parameter and this has to be reflected in the SEARCH script.

Examples:

Use the following script to select all countries that have name starting with the input string:

SELECT id, name FROM countries WHERE name LIKE {:2} || '%'

Or, if you want the search to be case-insensitive, transform the previous to:

SELECT id, name FROM countries WHERE UPPER(name) LIKE UPPER({:2}) || '%'

Or, maybe you want to select all countries that contain the input string ignore-case:

SELECT id, name FROM countries WHERE UPPER(name) LIKE '%' || UPPER({:2}) || '%'

DEFAULT script is not mandatory, it should be configured if you want to set a default value for the field. The default value will be calculated and stored to the issue immediately after it is created, only if no value has been selected in the Create screen.

External parameters

Other fields' values (screen or issue, more details here https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15487441/How+to+Use+Script+Parameters#External-parameters) can be passed as parameters in the scripts that define an Advanced Database Row Field.

Example 1 (S): we want our field to depend on a standard Jira text CF so that only the countries with GDP/capita greater than the value in the Jira CF should be available for selection. Here is how to write the SEARCH script in such a case:

SELECT id, name FROM countries WHERE gdp_capita > {screen:customfield_10501}::real

Notice how you also need to specify the external script parameters in the below section:

This results in:

Example 2 (M): we want our field to depend on the values selected for the Labels Jira standard field so that only the countries with a code that is found in the Labels list should be available for selection. Here is how to write the SEARCH script in such a case:

SELECT id,name FROM countries WHERE code IN ({screen:labels})

This results in:

Example 3 (A): if not selected, we want our field to take a default value - the country with the capital mentioned by the issue summary. Here is how to write the DEFAULT script in such a case:

SELECT id, name FROM countries WHERE POSITION(capital IN {issue:summary}) > 0

If you’re wondering why use issue:summary and not screen:summary, this is because at the time this query executes, the screen is no longer available but we can use the newly created issue’s summary.

An issue created with the following summary: ‘When in Rome, do as the Romans do’ will have the Country field’s value to Italy.

Auto-bind feature

This feature allows for instant calculation of the value of the field, each time the value of the dependent field changes. To activate it, enable the toggle in the General tab of the field configuration:

Here is a brief description of the mechanism behind:

• the field used as parameter is being watched;

• when this field’s value changes, the SEARCH script of the Advanced Database Row Field that has it as dependent field will run;

• the result will be directly filled to the Advanced Database Row Field (it can be empty, in which case it clears any previous value the field may have had).

Example (M): we want each time the Labels Jira standard field changes, the Country field to update value to contain all countries that have a matching code. The SEARCH script is identical to the above example:

SELECT id,name FROM countries WHERE code IN ({screen:labels})

Results, when successively setting labels:

Advanced Database Row Field as external parameter

Multiple row fields can be configured to depend on each other.

So, either you use {screen:customfield_xxxxx} or {issue:customfield_xxxxx} as a parameter, they will both be interpreted as that custom field’s primary key value.

Let’s take the following example:

We have field Country Code (S - with id customfield_10400), and we want to make the field Country Capital (S) to depend on field Country Code. We can even use the Auto-bind feature to get the Country Capital to sync with Country Code each time it changes. Assuming Country Code has the following column config:

and Country Capital has the following column config:

then the SEARCH script for Country Capital should be:

SELECT id, capital FROM countries WHERE id = {screen:customfield_10400}

Notice how the condition is id = {screen:customfield_10400} and not code = {screen:customfield_10400} (PK of customfield_10400 is the id column).

Result, when selecting a country code: