> ## Documentation Index
> Fetch the complete documentation index at: https://learn.nextedy.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Formula Functions

> Nextedy RISKSHEET formulas are JavaScript functions that calculate derived values for grid cells. Each formula receives an `info` context object and returns the computed value.

export const LastReviewed = ({date}) => {
  if (!date) return null;
  const formatted = new Date(`${date}T00:00:00Z`).toLocaleDateString("en-US", {
    year: "numeric",
    month: "long",
    day: "numeric",
    timeZone: "UTC"
  });
  return <p className="mt-10 text-sm text-gray-400 dark:text-zinc-500 not-prose">
      Last reviewed on {formatted}
    </p>;
};

## 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)                                                  |

<Note title="Formula Execution Timing">
  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.
</Note>

## 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:

```yaml theme={null}
formulas:
  myFormulaName: function(info){ /* calculation logic */ return result; }
```

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

```yaml theme={null}
{
  "columns": [
    {
      "id": "rpn",
      "header": "RPN",
      "bindings": "rpn",
      "formula": "commonRpn"
    }
  ]
}
```

<Frame>
  <img src="https://mintcdn.com/none-17b4493f/yWl5nA2D0IzEnZC1/risksheet/diagrams/reference/formulas/formula-functions/diagram-1.svg?fit=max&auto=format&n=yWl5nA2D0IzEnZC1&q=85&s=f9c163c02e9b84cdc4823a4d78c3d0de" alt="diagram" style={{ maxWidth: "580px", width: "100%" }} width="580" height="220" data-path="risksheet/diagrams/reference/formulas/formula-functions/diagram-1.svg" />
</Frame>

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

<Warning title="Formulas on Read-Only Columns">
  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).
</Warning>

## Data Access Functions

### Direct Field Access

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

```javascript theme={null}
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:

```javascript theme={null}
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:

```yaml theme={null}
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:

```yaml theme={null}
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:

```javascript theme={null}
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:

```yaml theme={null}
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:

```javascript theme={null}
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**

```javascript theme={null}
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;
}
```

<Tip title="Numeric Summation vs. Unique Collection">
  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.
</Tip>

## String Functions

### Field Concatenation

Combine multiple field values into a single display string:

```javascript theme={null}
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:

```yaml theme={null}
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:

```javascript theme={null}
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):

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

<Warning title="Enum IDs vs. Display Names">
  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.
</Warning>

## 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`:

```text theme={null}
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`:

```javascript theme={null}
// 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:

```yaml theme={null}
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:

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

Then reference from sheet configuration:

```yaml theme={null}
formulas:
  docField: function(info){ return getDocField(info); }
```

<Accordion title="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.
</Accordion>

### 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:

```javascript theme={null}
// 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.

<Frame>
  <img src="https://mintcdn.com/none-17b4493f/yWl5nA2D0IzEnZC1/risksheet/diagrams/reference/formulas/formula-functions/diagram-2.svg?fit=max&auto=format&n=yWl5nA2D0IzEnZC1&q=85&s=b6f1468d030edc0f709a6d85617a3e8c" alt="diagram" style={{ maxWidth: "520px", width: "100%" }} width="520" height="250" data-path="risksheet/diagrams/reference/formulas/formula-functions/diagram-2.svg" />
</Frame>

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

```yaml theme={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);}
```

### 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:

```yaml theme={null}
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](/risksheet/reference/styling/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.

<Warning title="Hidden Column Formulas Do Not Execute">
  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](/risksheet/reference/saved-views) for export-specific layouts, but never set an export-only view as the default view since formulas run on sheet load.
</Warning>

## 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:

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

<Tip title="Formula-Permission Interaction">
  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.
</Tip>

## 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:

```yaml theme={null}
{
  "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.

## Related Pages

* [Formula Syntax](/risksheet/reference/formulas/formula-syntax) -- expression structure and operator reference
* [Formula Examples](/risksheet/reference/formulas/formula-examples) -- ready-to-use formula configurations for common scenarios
* [Calculated Columns](/risksheet/reference/columns/calculated-columns) -- column configuration for formula-driven columns
* [Cell Decorators](/risksheet/reference/styling/cell-decorators) -- visual formatting based on cell values
* [Top Panel Template](/risksheet/reference/templates/top-panel-template) -- defining JavaScript functions accessible from formulas
* [Conditional Formatting](/risksheet/reference/styling/conditional-formatting) -- CSS style definitions for risk thresholds
* [Configuration Properties Index](/risksheet/reference/configuration/properties-index) -- full property reference

<LastReviewed date="2026-06-24" />