Confluence has been updated to version 6.15.9

Page tree
Skip to end of metadata
Go to start of metadata

This article describes the syntax of the SQL placeholders.You can use placeholders to get values from Jira elements and populate the grid with these values.Below you will find some examples of queries which help to get the values from Jira-specific elements.

Syntax

To get the value from Jira element use the following placeholder syntax

{<jira_element>:<value>:<field>:<selector>}


<jira_element> - Jira element that includes the data you want to set in a grid. You can get values from the Jira elements like issue, parent issue, project, user etc.

<value> - the value you want to get from the previously specified Jira element. Check the supported values.
For example, get the  due date from the current issue

{issue:due_date}


<field> -  a specific field of the selected Jira value(warning) This parameter is applicable only for multi-value Jira elements.

Some elements in Jira have a set of different fields. You can get the value from any of these fields.
For example, labels have an id and a name. You can get a list of labels' ids or names. 

  • Get the list of labels' ids separated by comma 
{issue:labels:id}
  • Get the list of labels' names separated by comma
{issue:labels:name}


<selector> - (warning) this parameter is used only in multi-value fields. It specifies the exact value you want to get. Can be an index number or all

If the element has multiple values and you want to get a specific one you can use the index number of this value to get it from this element.

For example, you can get the third value from the labels field of the current issue with the following query

{issue:labels:name:3}


Examples

SQL query with the placeholder to get issue summary 

select * from jiraissue where summary = '{issue:summary}'


Setting a default column value with the help of the placeholder

{issue:logged}

Check if the first selected version from the custom field with id 12345 is released or not. The returned value would be true or false.

{customfield:12345:is_released:1}

Custom field values

Single Value custom fields  

Get the URL from the custom field with id 10506  

{customfield:10506}

Multi-Value types

When you get data from the multi-values custom field you will get a list of all values separated by comma.


Get a list of users from the custom field of type user picker(multiple choices).

{customfield:12345:full_name}


Get all option names from the custom field select list(multiple choices). The custom field id is 12345. These names will be separated by a comma.

{customfield:12345:name}
 Populate select list options with data from another custom field

 Use case

You have a grid with the select list column and you want to populate the options with the data from a custom field.

You can add dynamic options to your select list and query data from the custom field using Jira values.


Pre-requisites

  • Custom field of type number which includes information about rates for the provided services. The ID of the custom field is 54321
  • Custom field of type list which includes a list of all services types. The ID of the custom field is 12345


Solution

Get a list of company names and ids which have a rate lower or equal to the one specified in the custom field Rate.

SELECT company_id, company_name FROM company_list WHERE service_type = '{customfield:12345}' and rate <= '{customfield:54321}'
 Calculate values from other custom fields in a separate grid column

 Use case

You can calculate values from other custom fields in the grid formula column type.


Pre-requisites

  • Custom fields of type number called "Price". The ID of the custom field is 10701
  • Custom field of type integer called "Quantity". The ID of the custom field is 10097


Example use

Get the quote in a grid, based on other custom fields: Price and Quantity

return {customfield:10701} * {customfield:10097}
  • No labels