Templates for a database data source

Let’s take a look at how templates can be used on data from a database data source in order to customize and improve how it is displayed in Jira.

Applying a template on a select list

Context

In this first example, we will take the case of a “Select list” connected field.

Result set

The result set returned by the query execution step contains printers IDs and office locations:

Requirement

The requirement is to display the results like this:

• DB-ENT-6515 (Dubaï)

• HK-ENG-MS (Hong-Kong)

• TLS-ENG-SL (Toulouse)

• SF-ENT-MFC (San Francisco)

Basically, the format is: PRINTER_ID (OFFICE_LOCATION).

Template

For select lists, the template is called for each row of the result set. In the template we have access to the current row of the result set through a variable: row.
Each column of the result set is an attribute of the row variable.

In our printer example, row  has 2 attributes: 

• ID  - the printer ID

• office  - the printer office location

Therefore, the template of our printer item is:

${row.ID} (${row.office})

Applying a template on a read-only field

The following example is only available for connected items, where templating with FreeMarker is available.

Context

The template of a read only Connected item is called only once. The entire result set is passed as a parameter to the template through the variable resultSet.

Depending on the use case:

• the Connected item query returns one row

• the Connected item query returns multiple rows

The writing of the template differs depending on the format of the result set, let's see how.

Single row result set

Result set

The result set returned by the query execution step contains details related to a specific printer:

Requirement

The requirement is to display the printer details in a list:

• Brand: Samsung

• Location: 1st floor - Sales

• Monthly duty cycle: 80000

• Maintenance: From Jun 8, 2018 to Mar 29, 2020

• Support: +33562244762

Template

In the template, we have access to the entire result through the variable resultSet. 

This variable is an array, each element of the array is a row of the result set.
In our example, the result set contains only 1 row, we assign this row to a local variable and use this variable in our template.

<!-- Assign the first row of the result set in a variable -->
<#assign printer = resultSet[0]>

<!-- We use the HTML <ul> & <li> tags to display results in a list -->
<ul>
  <li><strong>Brand:</strong> ${printer.Brand}</li>
  <li><strong>Location:</strong> ${printer.Location}</li>
  <li><strong>Monthly duty cycle:</strong> ${printer.MonthlyDutyCycle}</li>
  <li><strong>Maintenance:</strong> From ${printer.MaintenanceStart} to ${printer.MaintenanceEnd}</li>
  <li><strong>Support:</strong> <a href="tel:+${printer.Support}">+${printer.Support}</a></li>
</ul>

Multi rows result set

Result set

The result set returned by the query execution is a printer list:

Requirement

The requirement is to display the printer list in a table with a red warning when the maintenance period is over.

Template

The corresponding template is:

<!-- We use the "aui" class to benefit from Atlassian AUI style -->
<table class="aui">
  <thead>
    <tr>
      <th>Brand</th>
      <th>Location</th>
      <th>Monthly duty cycle</th>
      <th>Maintenance</th>
    </tr>
  </thead>
  <tbody>
    <!-- Iterate over each row of the result set -->
    <#list resultSet as printer>
    <tr>
      <td>${printer.Brand}</td>
      <td>${printer.Location}</td>
      <!-- We use the string.number built-in to format a number -->
      <td style="text-align:right;">${printer.MonthlyDutyCycle?string.number}</td>
      <td>
        ${printer.MaintenanceStart} to ${printer.MaintenanceEnd}
        <!-- Compare the maintenance end date to the current date -->
        <#if (.now?date gte printer.MaintenanceEnd?date)>
          <br/><span class="aui-lozenge aui-lozenge-error">Out of maintenance</span>
        </#if>
      </td>
    </tr>
    </#list>
  </tbody>
</table>

Applying a template on a read-only date field

Context

In this example, we will take the case of a “Read-only Date” connected field which data source is a SQL Database. Let’s say we’d like to display in this field the release date for a given laptop model, previously selected by a user and thus used as a dependency.

Result set

Let’s assume the result set is the following:

We’d like to retrieve and display the release date, meaning the “startDate” attribute.

👉 Data source date should be ISO formatted. Here, “2019-11-18" is a standard date formatting, thus Elements Connect will be able to translate it into a date field.

Template

For this release date to be displayed as a read-only date field, we need to specify where to find it in the result set, with the following format: ${data.your_attribute}:

Value will be converted into a Jira date field, formatted "DD Month AAAA"

This field will now be displayed as follow: