Define calculated values

This topic describes calculated values, field values that will be automatically set during synchronization if certain conditions are met. For example, you can define a rule that, when the connection creates a new story, its Tested value will be set to false.

Overview

Calculated values are field values that are automatically set during synchronization, when the specified conditions are met. For example, you can define a calculated value rule that automatically sets Tested to false when a connection creates a new story.

This example uses the FindFirst script to set a reference.

The example below references the ALM Octane parent story to set a Jira Bug text field to the parent’s name.

C

For details about structuring your queries correctly, see Filter and query data.

Back to top

Add a calculated value

You add a calculated value in the Connection Rules window.

To add a calculated value:

  1. In the Connection Rules window, choose a type and click Add Type Rule.
  2. In the Connection Type Rule dialog box, select the Calculated Values tab for either the Target or Master.

  3. Click the Add Calculated Value button. The Add Calculated Value window opens:

  4. Specify the When query. The When query determines the condition that an asset must meet in order for the calculated value to be applied. For details on specifying the When query, see Add projects and rules to a connection.

    Note: If you do not specify a When query, the Calculated Value is always applied when an asset is synchronized.

  5. In the Set Field drop down, choose the field whose value you want to set.

  6. In the To Value field, specify the value that you want to assign to the field. Use one of the following options:

    • Type a static value ("123"), or choose a value from the dropdown list ("Open").
    • For reference fields, you can set the value using one of the Find functions. These functions discover the item according to the criteria you specify. For a list of the available From functions, see Functions.
    • For date fields, you must use the long date format with an offset specifier:
      YYYY-MM-DDTHR:MIN:SEC+/-OFFSET.
      For example, 2016-01-01T00:00:00+00:00. Other formats result in an error.

  7. If you entered a function in the To Value field, define a From query. For details, see Add projects and rules to a connection.

  8. Click OK to save.

Back to top

Synchronize reference fields

You can use Micro Focus Connect to synchronize reference fields, by creating linkage to the referenced entity.

For example, suppose you are synchronizing a Jira project with an ALM Octane workspace. The Jira bugs and ALM Octane defects have a Release field. In ALM Octane, the Release field is a reference field, meaning that its value is linked to a release entity in the workspace. When creating a defect in ALM Octane, Micro Focus Connect can populate the Release field with the appropriate value, and also link the value to an existing release entity in ALM Octane.

To create a link between reference fields in the Set Field section, you can use the FindFirst functions. These functions search for an item in the product's database that matches the criteria defined in the From section. For details, see Functions.

To find the matching release, you must refer to the field value in the item being synchronized. To do so, use the My->fieldName or Other->fieldName variables.

  • Use My->fieldName to refer to the product for which you are creating the calculated value—target or master.
  • Use Other->fieldName to refer to the product on the other end of the synchronization.

Note: For a field name, specify the field's system name—not the field's label.

The following example demonstrates synchronizing the Release field in an ALM Octane defect:

Back to top

Assign items to a default user

You can use calculated fields to set user fields, such as Assigned to and Detected by to a different user, other than the one set in the starting endpoint.

For example, at one endpoint, users 1,2,3, and 4 detected defects and assigned them to users 5, 6, 7, and 8. When you migrate the defects to another endpoint, you want to assign all of them to user 9.

To assign the defects to another user:

  1. Open the Add Calculated Value screen. For details, see Add a calculated value.

  2. Set a calculated value for the target. For example:

    Set field: Assignee

    To value: Person # 9 Display Name

In this example, defects are always assigned to a specific default user, Person #9 Display name in Jira, regardless of who created the defect, or who it was assigned to in ALM/QC.

Tip: This strategy can be used for any user field, such as the Jira reporter field.

Back to top

Functions

You can dynamically set field values using functions. Functions are usually used in combination with values or variables in the From area.

General functions

Function Result
Max(type, property) The largest of a set.
Min(type, property) The smallest of a set.
csv(type) A comma-separated string of the values.

Find functions

Function Result
FindFirst(type) The first in a set that satisfies the find criteria.
FindAll(type) Returns all in a set that satisfy the find criteria.
FindFirstOrThrow(type) The first in a set that satisfies the find criteria. Returns an error if not found.
FindAllOrThrow(type) Returns all in a set that satisfy the find criteria. Returns an error if not found.
FindFirstInProperty(syncItemProperty) The first in a set that satisfies the find criteria, looking only at the SyncItems in the given property.
FindAllInProperty(syncItemProperty) Returns all in a set that satisfy the find criteria, looking only at the SyncItems in the given property.
FindAllInProperties(property1,property2…) Same as above, but allows you to specify multiple properties, allowing you to apply multiple mappings for a single field.
FindFirstInPropertytOrThrow(syncItemProperty) The first in a set that satisfies the find criteria, looking only at the SyncItems in the given property. Returns an error in not found.
FindAllInPropertiesOrThrow(property1,property2…) Same as above, but allows you to specify multiple properties, allowing you to apply multiple mappings for a single field.

Date and time functions

These functions support relative date and time calculations using this syntax:

FirstFunction([param]).subFunction([param]).subFunction([param])

Function names are case insensitive.

Function Result
Now([TimeZoneSpecifier])

The current time in the specified time zone. TimeZoneSpecifier can be in the following formats:

  • Now(). The current time in the current system's timezone.

  • Now(UTC). The current UTC time.

  • Now(Europe/Berlin). The current time in the Europe/Berlin timezone.
Today([TimeZoneSpecifier])

The current date in the specified timezone. For example, Today() returns the current date in the system's timezone.

Tip: This can also be retrieved using Now().TruncateHours

The following table lists the sub-functions that are supported for the date and time functions. All parameters for the sub-functions are numeric.

Action Sub-function
Add time

The following sub-functions allow you to add to the current date or time:

  • PlusSeconds(seconds)

  • PlusMinutes(minutes)

  • PlusHours(hours)

  • PlusDays(days)

  • PlusWeeks(weeks)

  • PlusMonths(months)

  • PlusYears(years)

Subtract time

The following sub-functions allow you to subtract from the current date or time:

  • MinusSeconds(seconds)

  • MinusMinutes(minutes)

  • MinusHours(hours)

  • MinusDays(days)

  • MinusWeeks(weeks)

  • MinusMonths(months)

  • MinusYears(years)
Truncate the date string

The following sub-functions allow you to truncate the date or time string:

  • TruncateHours()/TruncateHours

  • TruncateDays()/TruncateDays

  • TruncateMonth()/TruncateMonth

  • TruncateYears()/TruncateYears

This following examples illustrate some common uses of these functions.

When using sequential sub-functions, each sub-function uses the value returned by the previous sub-function.

Example Result
Now().TruncateMinutes

12/03/2021 13:00:00

Today(UTC+12).TruncateHours

12/03/2021 00:00:00

Now().PlusHours(5) Five hours ahead of the current synchronization time.
Today().PlusMonth(1))

One month ahead of the current synchronization date.

Now().PlusHours(2).MinusMinutes(2).TruncateSeconds

One hour and fifty-eight minutes ahead of the current time, hiding the seconds.

Back to top

Sample mappings

The following sections provide mapping examples using calculated values:

Example: Uni-directional mapping

Example: Jira bug mapped to an ALM/QC requirement

Example: Azure DevOps Resolved mapped to ALM/QC Fixed

Example: ALM/QC Fixed mapped to Azure DevOps Resolved

Example: Uni-directional mapping

This section provides an example of a uni-directional mapping of an ALM Octane (Master) List field to a target string field.

Create a calculated value for the target of the connection, for example Jira or ALM/QC, as described above.

Follow the format shown in the image below, where the ALM Octane Detected in Build field is mapped to the target’s string field.

.

Set a calculated value for the target. In this example:

  • Set field: Detected in Build (Jira or ALM/QCfield)

  • To value: Other -> Detected in Build.label

Example: Jira bug mapped to an ALM/QC requirement

This example shows the mapping of a Jira bug to an ALM/QC requirement using a calculated value.

To perform this mapping, we created a new ALM/QC requirement as a child of an existing one, whose name matched the name of the related Jira bug.

In this example, the following syntax gets the name of the bug from the Jira Epic link:

Example: Azure DevOps Resolved mapped to ALM/QC Fixed

This example shows the mapping of Azure DevOps Resolved to ALM/QC Fixed using a calculated value.

Example: ALM/QC Fixed mapped to Azure DevOps Resolved

This example shows the mapping of ALM/QC Fixed to Azure DevOps Resolved using a calculated value.

This mapping needs to be done in two stages, since you cannot perform two update operations with a single calculated value. In addition, in Azure DevOps, a Reason cannot be set to Fixed before setting the State to Closed.

To perform this mapping, first add this calculated value rule to set the State:

Then add an additional rule to set the Reason:

Back to top

Calculated values scripting

You can use JavaScript to perform calculated value mappings and utilize all JavaScript capabilities such as concatenating strings and numerical operations.

The syntax is Eval(<java-script-code>). Replace <java-script-code> with ECMAScript (ECMA-262) JavaScript code.

The following is list of common expressions::

  • Eval(myItem.stringField)

  • Eval(myItem.relatedItem.stringField)

  • Eval(myItem.p1 + myItem.p2)

  • Eval(myItem.relatedItem.p1 + myItem.p2)

  • Eval(myItem.integer1 + myItem.double1)

  • Eval(myItem.substring(1))

  • Eval(myItem.relatedItems.filter((item) => item.type === 'Story')

  • Eval(let prefix = 'Item: '; prefix + myItem.name)

  • Eval(`Value: ${myItem.stringField}`)

Guidelines

Consider the following when adding JavaScript expressions:

  • The syntax of My- > Other -> field is not supported in JavaScript code, so you cannot use an Other reference at arbitrary points. Instead use two separate expressions, myItem and otherItem, as aliases to current or opposite items, for example myItem.feature, otherItem.feature, and so forth.

  • JavaScript can be used in When, Set, and From expressions.

  • Specify a field's system name—not the field's label.

  • If the property in the field is a string, the JavaScript field is evaluated as the string value for the property.

  • If the property in the field is a number (Int, Long, Float, or Double) then the JavaScript field is evaluated as a number value for the property.
  • Use single quotes to customize the format of your result.
  • To separate terms within a single Eval statement, use a semicolon.

In this example, JavaScript expressions are used for the Description and Severity calculated values.

Note: When a field’s system name contains an invalid character such as a whitespace, colon, or asterisk, you must use the following Eval expression: Eval(myItem['<fieldname>']). For example, the following expression uses the Short description field in a ServiceNow calculated value expression:
Eval(otherItem['Sys ID'] + ":" + otherItem['Short description']).

Limitations

The following limitations apply when using JavaScript expressions as calculated values:

  • When using the Jira and ALM Octane connectors, the used properties are not detected and therefore they are not automatically added. This occurs in the following cases: When the property is part of a condition (if, while, etc.) or in array items.

  • Labels are not supported such as referencing fields by labels, or enum labels. Instead, use the field's system name.

Back to top

See also: