Skip to main content

​
Prerequisites

  • A Risksheet document with at least one configured column
  • Access to the risksheet.json configuration file (admin privileges required --- canAdmin must be true)
  • Familiarity with calculated columns basics

​
Understanding Formula and Visibility Interaction

Formula columns in Risksheet execute client-side JavaScript functions to compute derived values such as Risk Priority Numbers (RPN). A critical behavior to understand is that formulas only execute when their column is visible in the current grid view. Hidden columns skip formula execution entirely, which leads to stale or missing values in the underlying Polarion work items. This happens because formulas run during the cell rendering phase. If a column is not rendered (because it is hidden), the formula function is never invoked. The stored Polarion value remains whatever it was the last time the formula executed with the column visible --- or empty if the item was created while the column was hidden. diagram
If a formula column (such as a title column driven by a formula) is hidden when you create new risk items, the Polarion work item will receive an incorrect or empty value. For example, hiding a title formula column during item creation causes new items to show just a number instead of the computed title. This is by design --- Risksheet cannot run formulas for columns that are not rendered in the grid.

​
Step 1: Define Formulas in Configuration

Add formula definitions to the formulas section of your risksheet.json. Each formula is a named JavaScript function that receives an info object containing the current row data. The function must return the computed value, or null if the computation cannot be performed (e.g., when input fields are empty).

​
Basic RPN Formula

The most common formula pattern calculates Risk Priority Numbers by multiplying severity, occurrence, and detection ratings: 
{
  "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; }"
  }
}

​
Title Concatenation Formula

Another common pattern concatenates multiple fields into a title or description: 
{
  "formulas": {
    "titleFormula": "function(info){ return info.item['failureMode'] + ' - ' + info.item['effect']; }"
  }
}

​
The info Object Reference

The formula function receives a single info parameter with these properties:
PropertyTypeDescription
info.itemobjectThe current row’s data object. Access any column’s value using the column binding as a key (e.g., info.item['sev']).
info.valueanyThe current cell value before formula execution. Useful for formulas that modify rather than replace the existing value.
Always guard against empty or undefined input values. If any of the formula’s input fields are empty, returning null prevents displaying NaN or 0 in the cell. The pattern return value ? value : null; handles this for multiplication-based formulas.

​
Step 2: Assign Formulas to Columns

Reference a named formula in the column definition using the formula property. When a column has a formula set, Risksheet automatically sets readOnly to true for that column, preventing users from manually overriding computed values. 
{
  "columns": [
    {
      "id": "rpn",
      "header": "Initial RPN",
      "binding": "rpn",
      "type": "int",
      "formula": "commonRpn",
      "level": 2
    },
    {
      "id": "rpnNew",
      "header": "Revised RPN",
      "binding": "rpnNew",
      "type": "int",
      "formula": "commonRpnNew",
      "level": 2
    }
  ]
}
Key properties for formula columns:
PropertyTypeDefaultDescription
formulastringNoneName of the formula function defined in the formulas section. When set, the column becomes read-only by default.
readOnlybooleanfalse (auto-set to true for formula columns)Formula columns are automatically read-only. Setting readOnly: false explicitly allows external reconciliation via Check stored formulas.
visiblebooleantrueControls whether the column appears in the grid. Directly affects formula execution --- hidden columns do not calculate.
levelnumber1Hierarchical level at which this column appears. Not relevant to formula behavior but important for FMEA structures.
typestringauto-detectedData type of the column. For RPN formulas, use int or float.

​
Step 3: Manage Visibility for Formula Columns

You can control column visibility through the visible property on individual columns, through saved views, or through user-toggled column visibility. Each approach has different implications for formula execution.

​
Option A: Permanent Visibility with visible Property

Set visible: true on formula columns that must always compute: 
{
  "columns": [
    {
      "id": "rpn",
      "header": "RPN",
      "binding": "rpn",
      "formula": "commonRpn",
      "visible": true
    }
  ]
}
This is the safest approach --- the formula always executes because the column is always rendered.

​
Option B: Saved Views for Context-Specific Visibility

If you need different column layouts for interactive editing versus export, use saved views. Define a full editing view that includes all formula columns, and a separate export view that omits them: 
{
  "views": [
    {
      "name": "Editing View",
      "columns": ["id", "title", "severity", "occurrence", "detection", "rpn", "rpnNew"]
    },
    {
      "name": "Export View",
      "columns": ["id", "title", "severity", "occurrence", "detection"]
    }
  ]
}
Formulas execute on sheet load. If the default view hides formula columns, the formulas will not recalculate when the sheet opens. Always keep the full editing view (with all formula columns visible) as the default. Switch to the export view only immediately before exporting.

​
Option C: Column Visibility Toggle

Users can hide and show columns interactively through the grid’s column visibility controls. When a user hides a formula column and later shows it again, the formula recalculates on the next render. However, any items created or modified while the column was hidden will retain their stale values until the formula re-executes.

​
Step 4: Reconcile Stored Formula Values

Starting with version 24.5.1, Risksheet provides a Check stored formulas feature that synchronizes formula-computed values with the values stored in Polarion. This feature addresses several scenarios where stored values diverge from formula outputs:
  • External edits: Changes made to risk items outside of Risksheet (e.g., via Polarion’s native work item editor or a bulk import) are not reflected in formula-dependent fields.
  • Hidden column creation: Items created while formula columns were hidden have empty or incorrect computed fields.
  • Data migration drift: Polarion upgrades or migrations may truncate or alter stored values (e.g., title fields truncated to 80 characters), causing formula outputs to differ from stored values on subsequent loads.

​
Running the Reconciliation

  1. Open the Risksheet document in your browser.
  2. Ensure all formula columns are visible in the current view. Switch to the editing view if needed.
  3. Navigate to Menu > Rows > Check stored formulas.
  4. Risksheet recalculates every formula column for every visible row and compares the computed values against the stored Polarion values.
  5. Any discrepancies are flagged, and the items are marked as edited.
  6. Save the document to persist the corrected values back to Polarion.
Run this reconciliation after any of these events: bulk data imports via Polarion’s XLSX importer, Polarion version upgrades, manual edits to work items outside Risksheet, or after discovering that formula columns were hidden during a data entry session.
When formula columns are explicitly set to readOnly: false, the Check stored formulas feature can update their values. However, if Polarion Permissions Management restricts editing of those fields in the current workflow state, the save will fail. Risksheet attempts to save the recalculated values, but the permission check blocks the write. To resolve this, either temporarily change the work item status to one that allows editing, or adjust the field permissions.

​
Step 5: Handle Title Formula Columns Safely

A common FMEA pattern uses a formula to compute the work item title by concatenating fields like failure mode and effect. Title columns require special care because Polarion uses the title for display throughout its interface (work item lists, search results, traceability views).
  1. Always keep the title formula column visible during item creation. This ensures new items receive the correct computed title immediately.
  2. After initial creation, if you must hide the title column for layout reasons, do so only after saving the document with all formulas executed.
  3. If items were created with the title column hidden, use Check stored formulas (v24.5.1+) to reconcile the title values.

​
Example Title Formula Configuration

{
  "formulas": {
    "riskTitle": "function(info){ var fm = info.item['failureMode'] || ''; var eff = info.item['effect'] || ''; if(!fm && !eff) return null; return fm + ' - ' + eff; }"
  },
  "columns": [
    {
      "id": "title",
      "header": "Title",
      "binding": "title",
      "formula": "riskTitle",
      "visible": true
    }
  ]
}
Polarion may truncate title fields during platform migrations (e.g., to 80 characters). After such a migration, the stored title is shorter than what the formula produces. On next Risksheet load, the formula regenerates the full title, detects a difference from the stored value, and marks the item as edited. If permissions block title editing in the current workflow state, this triggers a save failure. Use Check stored formulas in a draft or editable workflow state to resolve the discrepancy.

​
Step 6: Combine Formulas with Conditional Formatting

Formula columns work particularly well with cellDecorators to provide color-coded visual feedback on computed values. For RPN columns, apply threshold-based styling to highlight risk levels: 
{
  "formulas": {
    "commonRpn": "function(info){ var value = info.item['occ']*info.item['det']*info.item['sev']; 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 <= 350); $(info.cell).toggleClass('rpn3', val > 350);}"
  },
  "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;}"
  }
}
This configuration produces a three-tier visual indicator:
CSS ClassRPN RangeBackgroundText ColorRisk Level
rpn11—150Light green (#eaf5e9)Dark green (#1d5f20)Low
rpn2151—350Light yellow (#fff3d2)Dark amber (#735602)Medium
rpn3> 350Light red (#f8eae7)Dark red (#ab1c00)High
The cell decorator executes after the formula, so it always operates on the freshly computed RPN value.

​
Verification

After configuring formula columns and visibility settings, you should now see:
  1. ✅ Formula columns display computed values automatically (e.g., RPN = Severity x Occurrence x Detection)
  2. ✅ Hiding a formula column and then showing it again causes the formula to recalculate on the next render
  3. ✅ New items created while formula columns are visible receive correct computed values
  4. ✅ The Check stored formulas option is available under Menu > Rows (requires version 24.5.1 or later)
  5. ✅ Saved views correctly toggle formula column visibility without losing stored values
  6. ✅ Conditional formatting (cell decorators) reflects the computed formula values with appropriate color coding

​
See Also

KB ArticlesSupport TicketsSource Code
  • PolarionAppConfigManager.java
  • AppConfig.ts
  • ColumnsHelper.ts
  • risksheet.json
  • CellPreviewFormatter.ts