Connected items using a Database datasource

Let's take a look at how to configure Connected items depending on their types:

• Select list (single choice or multiple choices)
• Read-only

Select list

For Connected items of type "Select list", the template is called on each result of the result set. Let's see through an example of how it works.

Step by step example

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)

In a more formal way, the format is: PRINTER_ID (OFFICE_LOCATION) 

Template

As we said, 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

In a FreeMarker template, we have access to the variable with this syntax: ${variable_name }

Therefore, the template of our printer item is:

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

The variable name is case sensitive. row.office  is different from row.Office 

Read only

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, let's see how.

1 - 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>

2 - 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>