Formula Functions - Nextedy Documentation

Formula Context Object

Every formula function receives a single info parameter containing the following properties:

Property	Type	Description
`info.item`	`object`	The current row’s data object containing all field values, accessed by column binding ID (e.g., `info.item['sev']`)
`info.value`	`any`	The current stored value of the cell being calculated, before the formula runs
`info.cell`	`HTMLElement`	The DOM element representing the cell (available during rendering)

Formulas execute during cell rendering. When a formula result differs from the stored value, Risksheet can mark the item as edited. Formula changes respect the readOnly column setting. This means formulas only run when their column is visible in the current view.

How Formulas Are Defined

Formulas are declared as named entries in the top-level formulas object of sheet configuration. Each entry is a JavaScript function written as a string value:

formulas:
  myFormulaName: function(info){ /* calculation logic */ return result; }

A column references a formula by setting its formula property to the formula name:

{
  "columns": [
    {
      "id": "rpn",
      "header": "RPN",
      "bindings": "rpn",
      "formula": "commonRpn"
    }
  ]
}

Column Properties for Formula Columns

Property	Type	Default	Description
`formula`	`string`	`None`	Name of the formula defined in the `formulas` object. When set, the column becomes read-only by default
`readOnly`	`boolean`	`false`	Automatically set to `true` when `formula` is specified. Can be explicitly set to `false` to allow manual overrides of formula values
`bindings`	`string`	Column `id`	The data field the formula writes its result to. Also used in `info.item[bindings]` to read values from other columns
`type`	`string`	Auto-detected	Data type of the column. Formula columns can output any supported type (`int`, `float`, `string`, etc.)
`cellRenderer`	`string`	`None`	Name of a cell decorator function to apply visual styling based on the formula’s computed value

When formula is set on a column, Risksheet automatically makes the column read-only. If you set readOnly: false on a formula column, changes made outside Risksheet may drift from formula-calculated values. Use Menu > Rows > Check stored formulas to reconcile stored values with recalculated formula output (available since v24.5.1).

Data Access Functions

Direct Field Access

Access any field on the current work item using the column binding ID:

function(info) {
  return info.item['fieldId'];
}

Common field access patterns:

Pattern	Description
`info.item['fieldId']`	Read any field value from the current row by its binding ID
`info.item['fieldId_link']`	Read the HTML link content for item link columns (uses `_link` postfix convention)
`info.value`	The currently stored value for this formula’s own column

Linked Item Field Access

For item link columns, the _link suffix provides the pre-rendered HTML hyperlink:

function(info) {
  return info.item['sysReq_link'];
}

Built-In Formula Patterns

RPN Calculation (Initial Assessment)

The standard Risk Priority Number formula multiplies severity, occurrence, and detection ratings. This is the most common formula in FMEA configurations:

formulas:
  commonRpn: function(info){ var value = info.item['occ']*info.item['det']*info.item['sev'];
    return value?value:null;}

Parameter	Source Binding	Description
Severity	`info.item['sev']`	Severity rating value from the `sev` column
Occurrence	`info.item['occ']`	Occurrence rating value from the `occ` column
Detection	`info.item['det']`	Detection rating value from the `det` column

Return behavior: Returns the product of all three values. If any value is falsy (zero, null, or undefined), returns null to avoid displaying zero for incomplete rows.

RPN Calculation (After Mitigations)

The revised RPN uses the post-mitigation rating fields to show the residual risk after corrective actions:

formulas:
  commonRpnNew: function(info){ var value = info.item['occNew']*info.item['detNew']*info.item['sevNew'];
    return value?value:null; }

Parameter	Source Binding	Description
Revised Severity	`info.item['sevNew']`	Post-mitigation severity rating
Revised Occurrence	`info.item['occNew']`	Post-mitigation occurrence rating
Revised Detection	`info.item['detNew']`	Post-mitigation detection rating

Null-Safe Multiplication

Always guard against null inputs to avoid NaN results when data is incomplete:

function(info) {
  var a = info.item['fieldA'];
  var b = info.item['fieldB'];
  if (a == null || b == null) return null;
  return a * b;
}

Cross-Row Data Aggregation

getMasterRowsByColumnValue

Available since v24.9.1 The risksheet.ds.getMasterRowsByColumnValue() function enables cross-row data aggregation. This is critical for FMEA workflows where parent process step rows need to summarize characteristics from child risk items.

Function	Parameters	Returns
`risksheet.ds.getMasterRowsByColumnValue(columnId, value)`	`columnId`: binding ID of the column to match; `value`: the value to filter by	Array of row data objects where the specified column matches the given value

Use case: Aggregate unique enum values from downstream risk items In a process FMEA, the parent process step may need to collect all unique process characteristics from its child risk items:

formulas:
  aggregateUnique: function(info){ var rows = risksheet.ds.getMasterRowsByColumnValue('parentId',
    info.item['parentId']); var values = []; for(var i=0; i<rows.length; i++){ var
    v = rows[i]['characteristic']; if(v && values.indexOf(v)===-1) values.push(v);
    } return values.join(', '); }

Expanded for readability:

function(info) {
  var rows = risksheet.ds.getMasterRowsByColumnValue(
    'parentId',
    info.item['parentId']
  );
  var values = [];
  for (var i = 0; i < rows.length; i++) {
    var v = rows[i]['characteristic'];
    if (v && values.indexOf(v) === -1) {
      values.push(v);
    }
  }
  return values.join(', ');
}

Use case: Sum numeric values across child items

function(info) {
  var rows = risksheet.ds.getMasterRowsByColumnValue(
    'parentId',
    info.item['parentId']
  );
  var total = 0;
  for (var i = 0; i < rows.length; i++) {
    total += rows[i]['riskScore'] || 0;
  }
  return total > 0 ? total : null;
}

Use the summation pattern for rolling up numeric risk scores or counts. Use the unique-value pattern for collecting distinct enum values (e.g., process characteristics, risk categories) from multiple child risk items into a parent row.

String Functions

Field Concatenation

Combine multiple field values into a single display string:

function(info) {
  var hazard = info.item['hazardousSituation'] || '';
  var harm = info.item['harm'] || '';
  if (!hazard && !harm) return null;
  return hazard + ' - ' + harm;
}

Auto-Generated Title

Automatically construct a title from component fields:

formulas:
  autoTitle: 'function(info){ var h = info.item[''hazard'']||''''; var e = info.item[''effect'']||'''';
    return h && e ? h+'' - ''+e : null; }'

Conditional Logic Functions

Risk Matrix Lookup

Implement a risk acceptance matrix using conditional logic:

function(info) {
  var sev = info.item['severity'];
  var prob = info.item['probability'];
  if (sev >= 8 && prob >= 6) return 'Unacceptable';
  if (sev >= 5 && prob >= 4) return 'Further Investigation';
  return 'Acceptable';
}

Enum-Based Conditional

Compare against enum IDs (not display names):

function(info) {
  var status = info.item['riskStatus'];
  if (status === 'mitigated') return 'Complete';
  if (status === 'open') return 'Action Required';
  return 'Pending';
}

Formula comparisons must use enum IDs (e.g., 'mitigated'), not display values (e.g., 'Mitigated'). The enum type identifier in sheet configuration must match the definition in Polarion’s XML custom field file. Check your .polarion/documents/fields/custom-fields.xml for the correct ID values.

Top Panel Function Integration

For complex calculations that exceed inline formula capacity, define functions in the risksheetTopPanel.vm file and call them from formulas. This pattern is also the only way to access document-level custom fields or external data sources.

Pattern Overview

The formula in sheet configuration calls a function defined in risksheetTopPanel.vm:

risksheet.json                    risksheetTopPanel.vm
─────────────────                 ─────────────────────
"formulas": {           calls     <script>
  "risk": "function   -------->    function calcRisk()
    (info){                          { ...complex logic }
    return calcRisk               </script>
    (info);}"
}

Step 1. Define a JavaScript function in risksheetTopPanel.vm:

// Inside <script> block in risksheetTopPanel.vm
function calculateRiskLevel(info) {
  var sev = info.item['severity'];
  var prob = info.item['probability'];
  if (sev >= 8 && prob >= 6) return 'Unacceptable';
  if (sev >= 5 && prob >= 4) return 'Further Investigation';
  return 'Acceptable';
}

Step 2. Reference the top panel function from a formula in sheet configuration:

formulas:
  riskLevel: function(info){ return calculateRiskLevel(info); }

Accessing Document Custom Fields via Top Panel

Document-level custom fields are not directly available in sheet configuration formulas. Access them via $doc.getOldApi().getValue('customFieldID') in the risksheetTopPanel.vm Velocity script section, then bridge the values into your formulas through global JavaScript functions:

#set($myField = $doc.getOldApi().getValue('customFieldID'))
<script>
  var docCustomValue = '$myField';
  function getDocField(info) {
    return docCustomValue;
  }
</script>

Then reference from sheet configuration:

formulas:
  docField: function(info){ return getDocField(info); }

Dynamic Risk Matrices from External Sources

The risksheetTopPanel.vm can reference external data sources via Velocity context and Polarion APIs (e.g., reading risk acceptance matrix values from an XML sheet configuration). This enables dynamic risk matrix definitions shared across projects without duplicating formula logic in each sheet configuration.

Accessing Work Item Data in Top Panel Functions

Work item data can be accessed in top panel functions via info.item['fieldId']. Prepare all needed values within functions defined in the top panel file, then return the result to sheet configuration formulas:

// risksheetTopPanel.vm
function computeComplexResult(info) {
  var val1 = info.item['customField1'];
  var val2 = info.item['customField2'];
  // Complex calculation logic here
  return val1 + val2;
}

Formulas and Cell Decorators

Formulas compute the value; cell decorators apply visual styling based on that value. The two work together but are configured separately in sheet configuration.

The cellDecorators entry for RPN values applies CSS classes based on risk thresholds:

cellDecorators:
  rpn: function(info){ var val = info.value; $(info.cell).toggleClass('boldCol', true
    ); $(info.cell).toggleClass('rpn1', val>0 && val <= 150 ); $(info.cell).toggleClass('rpn2',
    val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val > 250);}

RPN Risk Threshold CSS Classes

CSS Class	Threshold	Background Color	Text Color	Meaning
`rpn1`	`val > 0 && val <= 150`	`#eaf5e9` (light green)	`#1d5f20` (dark green)	Low risk
`rpn2`	`val > 150 && val <= 250`	`#fff3d2` (light yellow)	`#735602` (dark yellow)	Medium risk
`rpn3`	`val > 250`	`#f8eae7` (light red)	`#ab1c00` (dark red)	High risk
`boldCol`	Always applied	—	—	Bold font weight (`font-weight: 600`)

Row Header Decorator

The row header can also display risk color based on the revised RPN value:

cellDecorators:
  rowHeaderRpnNew: function(info){ var val = info.item['rpnNew']; $(info.cell).toggleClass('rpn1',
    val>0 && val <= 150 ); $(info.cell).toggleClass('rpn2', val>0 && val > 150 &&
    val <= 250); $(info.cell).toggleClass('rpn3', val>0 && val > 250);}
headers:
  rowHeader:
    renderer: rowHeaderRpnNew

For full cell decorator reference, see Cell Decorators.

Formula Visibility and Execution

Formulas execute during cell rendering, which means a formula only runs when its column is visible in the current view.

If a formula column is hidden (not visible in the current saved view), its formula does not execute. If other formulas or cell decorators depend on the hidden column’s value, they will read a stale or null value. Use Saved Views for export-specific layouts, but never set an export-only view as the default view since formulas run on sheet load.

Check Stored Formulas

Available since v24.5.1 When formula columns have readOnly: false, or when items are edited outside Risksheet (for example, through Polarion’s standard work item editor), stored values may drift from formula-calculated results. To reconcile:

Open the Risksheet document
Navigate to Menu > Rows > Check stored formulas
Risksheet recalculates all formula columns and flags any differences

Formula-generated fields may trigger save failures when permissions restrict editing. If stored values differ from formula output (e.g., after data migration where titles were truncated), Risksheet tries to save the recalculated values, which are then blocked by permissions. Verify stored values match formula output before enabling strict permission enforcement.

Formula Return Types

Return Value	Cell Display	When to Use
`number`	Numeric value, formatted per column `format` property	RPN calculations, scores, counts
`string`	Text content	Concatenated labels, status text, risk level names
`null`	Empty cell	Incomplete data — avoids displaying zero for partially filled rows
`undefined`	Empty cell	Same behavior as `null`
`0`	Displays as zero	When zero is a meaningful value (e.g., zero remaining risk)
`boolean`	Checkbox state	True/false indicators

Complete Example

A full FMEA configuration with initial and revised RPN formulas, cell decorators, row header styling, and corresponding CSS:

{
  "formulas": {
    "commonRpn": "function(info){ var value = info.item['occ']*info.item['det']*info.item['sev']; return value?value:null;}",
    "commonRpnNew": "function(info){ var value = info.item['occNew']*info.item['detNew']*info.item['sevNew']; return value?value:null; }"
  },
  "cellDecorators": {
    "rpn": "function(info){ var val = info.value; $(info.cell).toggleClass('boldCol', true ); $(info.cell).toggleClass('rpn1', val>0 && val <= 150 ); $(info.cell).toggleClass('rpn2', val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val > 250);}",
    "rowHeaderRpnNew": "function(info){ var val = info.item['rpnNew']; $(info.cell).toggleClass('rpn1', val>0 && val <= 150 ); $(info.cell).toggleClass('rpn2', val>0 && val > 150 && val <= 250); $(info.cell).toggleClass('rpn3', val>0 && val > 250);}"
  },
  "styles": {
    ".boldCol": "{font-weight:600;}",
    ".rpn1": "{background-color: #eaf5e9 !important;color: #1d5f20 !important;}",
    ".rpn2": "{background-color: #fff3d2 !important; color: #735602 !important;}",
    ".rpn3": "{background-color: #f8eae7 !important;color: #ab1c00 !important;}"
  },
  "columns": [
    {
      "id": "sev",
      "header": "S",
      "bindings": "sev",
      "type": "rating:severity",
      "level": 2
    },
    {
      "id": "occ",
      "header": "O",
      "bindings": "occ",
      "type": "rating:occurrence",
      "level": 2
    },
    {
      "id": "det",
      "header": "D",
      "bindings": "det",
      "type": "rating:detection",
      "level": 2
    },
    {
      "id": "rpn",
      "header": "RPN",
      "bindings": "rpn",
      "formula": "commonRpn",
      "cellRenderer": "rpn",
      "level": 2
    },
    {
      "id": "sevNew",
      "header": "S (new)",
      "bindings": "sevNew",
      "type": "rating:severity",
      "level": 2
    },
    {
      "id": "occNew",
      "header": "O (new)",
      "bindings": "occNew",
      "type": "rating:occurrence",
      "level": 2
    },
    {
      "id": "detNew",
      "header": "D (new)",
      "bindings": "detNew",
      "type": "rating:detection",
      "level": 2
    },
    {
      "id": "rpnNew",
      "header": "RPN (new)",
      "bindings": "rpnNew",
      "formula": "commonRpnNew",
      "cellRenderer": "rpn",
      "level": 2
    }
  ],
  "headers": {
    "rowHeader": {
      "renderer": "rowHeaderRpnNew"
    }
  }
}

This configuration creates two RPN columns (initial and revised), each with color-coded risk thresholds applied via cell decorators. The row header uses the revised RPN color for at-a-glance residual risk assessment.

Formula Syntax — expression structure and operator reference
Formula Examples — ready-to-use formula configurations for common scenarios
Calculated Columns — column configuration for formula-driven columns
Cell Decorators — visual formatting based on cell values
Top Panel Template — defining JavaScript functions accessible from formulas
Conditional Formatting — CSS style definitions for risk thresholds
Configuration Properties Index — full property reference

​Formula Context Object

​How Formulas Are Defined

​Column Properties for Formula Columns

​Data Access Functions

​Direct Field Access

​Linked Item Field Access

​Built-In Formula Patterns

​RPN Calculation (Initial Assessment)

​RPN Calculation (After Mitigations)

​Null-Safe Multiplication

​Cross-Row Data Aggregation

​getMasterRowsByColumnValue

​String Functions

​Field Concatenation

​Auto-Generated Title

​Conditional Logic Functions

​Risk Matrix Lookup

​Enum-Based Conditional

​Top Panel Function Integration

​Pattern Overview

​Accessing Document Custom Fields via Top Panel

​Accessing Work Item Data in Top Panel Functions

​Formulas and Cell Decorators

​RPN Risk Threshold CSS Classes

​Row Header Decorator

​Formula Visibility and Execution

​Check Stored Formulas

​Formula Return Types

​Complete Example

​Related Pages

Formula Context Object

How Formulas Are Defined

Column Properties for Formula Columns

Data Access Functions

Direct Field Access

Linked Item Field Access

Built-In Formula Patterns

RPN Calculation (Initial Assessment)

RPN Calculation (After Mitigations)

Null-Safe Multiplication

Cross-Row Data Aggregation

getMasterRowsByColumnValue

String Functions

Field Concatenation

Auto-Generated Title

Conditional Logic Functions

Risk Matrix Lookup

Enum-Based Conditional

Top Panel Function Integration

Pattern Overview

Accessing Document Custom Fields via Top Panel

Accessing Work Item Data in Top Panel Functions

Formulas and Cell Decorators

RPN Risk Threshold CSS Classes

Row Header Decorator

Formula Visibility and Execution

Check Stored Formulas

Formula Return Types

Complete Example

Related Pages