How Velocity helps you do more with Elements Connect

Elements Connect users know that the Jira app is a powerful tool to fetch data from a wide variety of sources in order to make it available in Jira custom fields. To help you go beyond the basic types of custom field configurations, we wanted to demonstrate how Velocity can help you make smart queries with Elements Connect.

What is Velocity in Jira?

Velocity is a Java-based template engine used to generate web pages, SQL, PostScript and other output from templates. It can be used either as a standalone utility for generating source code and reports, or as an integrated component of other systems.

This is how Velocity is defined on velocity.apache.org. In other words, you can use Velocity to customize the query and make it more dynamic.

What does that mean? It means, by using Velocity we can analyze and treat different scenarios that an Elements Connect field might have. The user can define several conditions in the field queries, and based on the context, the appropriate query will be executed.

Velocity at Issue Creation

It is worth mentioning that at the issue creation, all Velocity variables will be available except the $issue.id and $issue.key because the issue has not been created yet. This case could be met by using the Elements Connect Post Function, to set field value on issue creation.

Below, we will discuss 3 use cases where the Velocity will be the solution to solve the problem and rend the query “smarter”.

Use case 1: Using $userInput without losing data when editing the query

In our documentation, we have covered the example of the autocomplete editor with the $userInput variable in the Edit view, and it’s a great combination when trying to display a large amount of data, which will consequently improve the field performance.

However, the user might face a common problem using the $userInput variable: the field is cleared when editing the issue!

This is a known problem when the Edit query is based on the $userInput variable. To solve it, we can either increase the “Max suggestions” parameter to make sure to retrieve whatever the user selected, or we can use Velocity to add a condition to test whether it’s a new input (creation) or an edit (so we keep the existing value).

Assume the below Edit query for a User Picker field based on the entered user group

SELECT username
FROM users
WHERE user_group LIKE '%$userInput%'

As mentioned above, we can use Velocity variables ($currentCustomfieldValue, $userInput) and Velocity functionalities (the #if condition, length function) to check if the field already contains a value or this is a value recently entered.

So, the Edit View query will be as below:

#if($currentCustomfieldValue.length() > 0 && $userInput.length() == 0)
SELECT username
FROM users
WHERE username = '$currentCustomfieldValue'
#elseif ($userInput.length() > 0)
SELECT username
FROM users
WHERE user_group LIKE '%$userInput%'
#end

If the Elements Connect field allows the user to select multiple values, we have to deal with them in our query, otherwise, only the first value will be selected.

That could be done by using Serialize function, the Edit query for a multi select field will looks like:

#if($currentCustomfieldValue.length() > 0 && $userInput.length() == 0)
SELECT username
FROM users
WHERE username in ($currentCustomfieldValue.serialize(","))
#elseif ($userInput.length() > 0)
SELECT username
FROM users
WHERE user_group LIKE '%$userInput%'
#end

Use case 2: Query Multiple tables in the same field based on user input

Each Elements Connect Field has one data source. By using Velocity, we can configure the field to fetch data from 2 different tables (in the same data source) based on the entered data.

Suppose that based on the user input (a reference like ELEMP2020 or ELEMT3031), we decide if we retrieve data from the procurement table or the technical table.

In this case, the query should be something like the below query (again the number of entered characters characters that we check at the beginning, is something we can adjust to any value that suits our needs):

#if( $userInput.length() < 5 )
$query.abort()
#else
#set( $prefix = $userInput.toString().substring(0,5) )
#if( $prefix == "ELEMP")
select ... from procurement where ... = $userInput
#elseif( $prefix == "ELEMT")
select ... from technical where ... = $userInput
#end
#end

It’s important to note that when dealing with $userInput or $issue, they are Elements Connect objects, they don’t return a String object. By using toString(), all the public methods of the String Java class will be available.

Use case 3: Retrieve Reporter Information from Active Directory or LDAP in a JSD project

If we are in a JSM project, and when creating an issue from the portal, we need to retrieve the reporter information from an LDAP. Should we use “$currentUser” or “$issue.reporter”?

When a Service Management agent is creating the ticket, the reporter field is filled with “$currentUser” logged in. In this case the $currentUser = $issue.reporter and our Elements Connect edit query will be based on “$issue.reporter”.

Now, suppose a customer is logging in and raising a ticket on behalf on another user. In this case the “$currentUser” won’t be relevant to retrieve the reporter information, “$issue.reporter” should be used instead.

#if ($currentUser.isInGroup("jira-servicedesk-users"))
ou=people,dc=planetexpress,dc=com?uid,cn,employeeType,mail?sub?(cn=$issue.reporter)
#else
ou=people,dc=planetexpress,dc=com?uid,cn,employeeType,mail?sub?(cn=$currentUser)
#end

To sum up, using Velocity will help you cover more scenarios and overcome some tricky use cases. In our documentation, we have a list of Velocity variables used in Elements Connect with some examples.