Page Comparison

Image Added

GET

The GET script is triggered when the field is displayed in a Jira page. The frequency with which it runs depends on the Refresh type that is configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/540574265/General+settings+for+read-only+fields#Refresh-type ).

Important: the column set as Primary Key is the only indexed parameter that should be used by this script.

SQL Example:

SELECT id, code, name, independence, gdp_capita, capital, is_eu FROM countries WHERE id = {:41}

Get

SQL used to determine how data will be retrieved from the database.

Example:

SELECT id, year, make, model FROM auto_info WHERE year = {:2} AND make = {:3} AND model = {:4}

Set

SQL code used to determine how data will be set in Jira, not the database.

Insert

SQL code used to determine how data will be inserted into the database.

Example:

INSERT INTO auto_info(id, year, make, model

Image Added

SET

The SET script is triggered every time the field value is stored in Jira. It is not mandatory to configure it, it's just a helpful instrument in case you need to take an additional step, simultaneous with setting the field’s value.

If the security configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15482145/General+settings+for+generic+fields#Security-Type ) allows it, users are able to search and set values for the field in Jira. Clicking on the following icon:

Image Added

will open a dialog in which the SEARCH script will be run, providing it with values present in the dialog’s form that is composed of all the columns marked as searchable in the column list configuration https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15481863/Columns#Searchable.

If a value from the result set is selected, it is stored in Jira and the SET script is triggered.

Important: the column set as Primary Key is the only indexed parameter that should be used by the SET script.

SQL Example: Let’s assume every time you set a value for the field you want to trigger a script that writes in a separate audit table. You will have to configure the SET script like this:

INSERT INTO countries_audit (issue_key, country_id, update_date) VALUES ({issue:key}, {:1}, CURRENT_DATE)

Image Added

INSERT

The INSERT script is triggered every time the Insert action is completed.

If the security configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15482145/General+settings+for+generic+fields#Security-Type ) allows it, users are able to insert new rows in the database. Clicking on the following icon:

Image Added

will render a form with inputs corresponding to the columns in the field configuration. Submitting this form, which can be done either using ‘Insert only’ or ‘Insert & Set’ buttons, will run the INSERT script, providing it with the values filled in the form.

The parameters to be used in the INSERT script should be coded like: {:1}, {:2} etc. which, at runtime, will receive the form value for the field’s column with the specified index.

Important: you have to refer to the same columns previously defined in the Columns tab and make sure you are using the correct index equivalent.

SQL Example: If you want to allow adding new rows to your table, you have to configure the INSERT script like this:

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

Image Added

UPDATE

The UPDATE script is triggered every time the Update

SQL code used to determine how data will be updated in the database.

Example:

action is completed.

If the security configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15482145/General+settings+for+generic+fields#Security-Type ) allows it, users are able to update existing rows in the database. Clicking on the following icon:

Image Added

will render a form with inputs corresponding to the columns in the field configuration.

Submitting this form will run the UPDATE script, providing it with the values filled in the form.

The parameters to be used in the UPDATE script should be coded like: {:1}, {:2} etc. which, at runtime, will receive the form value for the field’s column with the specified index.

Important: you have to refer to the same columns previously defined in the Columns tab and make sure you are using the correct index equivalent.

SQL Example: If you want to allow updating rows from your table, you have to configure the UPDATE script similar to this:

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

Image Added

DELETE

The DELETE script is triggered every time the Delete

SQL code used to determine how data will be deleted from the database.

Example:

DELETE FROM auto_info WHERE id = {:1}

Search

SQL code used to determine how data will be searched in the database.

Example:

SELECT id, year, make, model FROM auto_info WHERE model ~ {:4}

Seq

SQL code used to determine a sequence value for the ID when inserting new values into the database. If not specified, it will try to use the value provided in the inputs, and if that does not exist, it will rely on the database to have a mechanism for the ID.

Example:

action is completed.

If the security configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15482145/General+settings+for+generic+fields#Security-Type ) allows it, users are able to delete existing rows from the database. Clicking on the following icon:

Image Added

will ask for confirmation to delete the currently selected row. Accepting it will run the DELETE script, providing it with values present in the field.

The parameters to be used in the DELETE script should be coded like: {:1}, {:2} etc. which, at runtime, will receive the value for the field’s column with the specified index.

Important: you have to refer to the same columns previously defined in the Columns tab and make sure you are using the correct index equivalent.

SQL Example: If you want to allow deleting rows from your table, you have to configure the DELETE script similar to this:

DELETE FROM countries WHERE id = {:1}

Image Added

SEARCH

The SEARCH script is triggered every time the field has to display the list of possible values for the user to select from.

If the security configured for the field (https://appfire.atlassian.net/wiki/spaces/PSJ/pages/15482145/General+settings+for+generic+fields#Security-Type ) allows it, users are able to search and set values for the field in Jira. Clicking on the following icon:

Image Added

will open a dialog in which the SEARCH script will be run, providing it with values present in the dialog.

The parameters to be used in the SEARCH script should be coded like: {:1}, {:2} etc. which, at runtime, will receive the value for the field’s column with the specified index.

Important: you have to refer to the same columns previously defined in the Columns tab. The output of this script has to respect the names, data types and exact order in which the columns are configured.

SQL Example: Let’s assume you want to use as search criteria the code and name of the countries and you want to search for countries having a code or name that contains the values entered in the screen inputs. You will have to configure the SEARCH script like this:

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

Image Added

SEQ