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>