# Tracing Calculated Columns Formulas

Canonical page: https://www.adaptabletools.com/docs/handbook-calculated-column-tracing-formula

- AdapTable provides 2 UI components to allow run-time users to follow (and edit) a Calculated Column's formula 
  - **Formula Trace** shows - Excel-style - how a Calculated Column value is derived
  - The **Compact Expression Editor** lets the Expression be edited inline

[Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) are typically defined at design-time by developers.

But AdapTable also provides a rich run-time UI so that users can create, manage and - crucially - **understand** Calculated Columns.

## Formula Trace

AdapTable includes an Excel-style **Formula Trace** which lets run-time users see - and inline edit - how a Calculated Column value is derived.

There are 2 ways to open Formula Trace for a Calculated Column cell:

- **Double-click** the cell
- Select the **Show Formula** item in the cell's [Context Menu](https://www.adaptabletools.com/docs/ui-context-menu/index.md)

Both triggers are enabled by default, but each can be turned off - see [Formula Trace Options](#formula-trace-options) below

When opened, Formula Trace displays a popup - anchored to the cell - containing the Column's [Expression](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) with every referenced Column shown in its own colour.

At the same time, AdapTable highlights the referenced data in the Grid using the **same colours** (in the manner of Excel):

- for `Standard` Calculated Columns the referenced **cells** in the row are outlined and tinted
- for `Aggregated` (and other multi-row) Calculated Columns the referenced Column **headers** are highlighted

For `Standard` Calculated Columns the popup also shows each referenced Column's **name above its actual cell value** for that row, followed by the computed result (e.g. `= 42`) - so you can see the calculation "worked through" for the current row

### Editing a Formula

The Formula Trace popup contains an **Edit** button which opens the **Compact Expression Editor**.

This is a small, moveable and resizable version of the full [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md), giving quick access to the Expression without opening the full Wizard.

It provides the same power as the full Editor, including:

- the **Functions** dropdown listing all available [Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md)
- live **validation**
- **colour-coded** Column references (matching the Formula Trace highlights)

Edits are applied **live** as you type - the Calculated Column re-evaluates immediately.

An invalid Expression will show `#INVALID` in the cells until it is corrected

The Compact Editor also provides 2 buttons:

- **Revert** - restores the Expression to how it was when the Editor was opened
- **Open Full Editor** - closes the Compact Editor and opens the full Calculated Column Wizard

The Compact Expression Editor is available for all 6 Calculated Column types: `Standard`, `Aggregated`, `Cumulative`, `Quantile`, `Window` and `Observable`

### Showing Formula Trace via the API

Formula Trace can also be opened programmatically using the [`Calculated Column API`](https://www.adaptabletools.com/docs/reference/calculatedcolumnapi.md):

- `showFormulaTrace(columnId, primaryKeyValue)` - opens the Formula Trace popup for the given Calculated Column cell
- `hideFormulaTrace()` - closes the popup (and clears the Grid highlights)

**Example: Using Calculated Columns: Formula Trace**

Calculated Columns: Formula Trace and the Compact Editor

- This demo contains 3 Calculated Columns:
  - `Total PRs` (Standard) - sums the `Open PRs` and `Closed PRs` Columns
  - `Issues Open/Total Ratio` (Standard) - divides `Open Issues` by total Issues
  - `Sum Pop by Lang` (Aggregated) - sums `Github Watchers` grouped by `Language`
- After 5 seconds the demo calls `showFormulaTrace` to open the Formula Trace popup for the first `Total PRs` cell (see `onAdaptableReady.ts`)

- Wait for the Formula Trace popup to appear, or double-click any `Total PRs` cell yourself
- Note how the two referenced cells (`Open PRs`, `Closed PRs`) are highlighted in matching colours
- Click the Edit button in the popup and change the Expression in the Compact Editor - the Column updates live
- Double-click a `Sum Pop by Lang` cell and note the referenced Column **header** is highlighted instead

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts        [template]
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts
  rowData.ts           [template]
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

```ts
import {AdaptableOptions} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Using Calculated Columns',
  initialState: {
    Dashboard: {
      ModuleButtons: ['CalculatedColumn'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'CalculatedColumn'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'open_pr_count',
            'closed_pr_count',
            'total_pr_count',
            'open_issues_count',
            'closed_issues_count',
            'open-total-issue-ratio',
            'language',
            'github_watchers',
            'sum-popularity-by-lang',
            'github_stars',
          ],
          Name: 'Standard Layout',
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Total PRs',
          ColumnId: 'total_pr_count',
          Expression: '[open_pr_count] + [closed_pr_count]',
          CalculatedColumnSettings: {
            DataType: 'number',
            Filterable: true,
          },
        },
        {
          FriendlyName: 'Issues Open/Total Ratio',
          ColumnId: 'open-total-issue-ratio',
          Expression: '[open_issues_count] / ([open_issues_count] + [closed_issues_count])',
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
        {
          ColumnId: 'sum-popularity-by-lang',
          FriendlyName: 'Sum Pop by Lang',
          Expression: 'SUM{[github_watchers], GROUP_BY([language])}',
          CalculatedColumnSettings: {
            DataType: 'number',
            Groupable: true,
          },
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

```ts
import {AdaptableReadyInfo} from '@adaptabletools/adaptable';

export const onAdaptableReady = ({adaptableApi}: AdaptableReadyInfo) => {
  // After a short delay, programmatically open the Formula Trace popup for the
  // first row's `Total PRs` cell, so users scrolling the page see the UI without
  // having to double-click a cell themselves.
  setTimeout(() => {
    const firstRowNode = adaptableApi.gridApi.getFirstRowNode();
    if (!firstRowNode) {
      return;
    }
    const primaryKeyValue =
      adaptableApi.gridApi.getPrimaryKeyValueForRowNode(firstRowNode);
    adaptableApi.calculatedColumnApi.showFormulaTrace(
      'total_pr_count',
      primaryKeyValue
    );
  }, 5000);
};
```

## Cell Tooltip

A Calculated Column can also show its formula as a **tooltip** when the user hovers over a cell.

For per-row Calculated Columns (e.g. `Standard`) the tooltip shows the formula and, underneath, the same formula with this row's values filled in, followed by the result.

For multi-row Calculated Columns (e.g. `Aggregated`) the tooltip shows the formula.

Turn it on by setting `ShowToolTip` in the Calculated Column's `CalculatedColumnSettings` (or checking **Show Expression as Cell Tooltip** in the Calculated Column Wizard):

```ts {8}
calculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'TotalPRs',
      Expression: '[open_pr_count] + [closed_pr_count]',
      CalculatedColumnSettings: {
        DataType: 'number',
        ShowToolTip: true,
      },
    },
  ],
},
```

`ShowToolTip` is _false_ by default

### Configuring Formula Trace

Formula Trace is configured via the `formulaTrace` property in [Calculated Column Options](https://www.adaptabletools.com/docs/technical-reference-adaptable-options/index.md).

There are 2 properties available to set the formula trace behaviour:

### `enableDoubleClick`

Whether double-clicking a Calculated Column cell opens Formula Trace

By default, users can open Formula Trace by double-clicking any Calculated Column cell.

Set this to *false* to disable the double-click trigger (e.g. if double-click is used for something else in your application).

```ts {4}
  calculatedColumnOptions: {
    formulaTrace: {
      enableDoubleClick: false,
    },
  },
```

### `showContextMenuItem`

Whether the "Show Formula" item appears in the cell Context Menu

By default, a `Show Formula` item is added to the [Context Menu](https://www.adaptabletools.com/docs/ui-context-menu/index.md) for Calculated Column cells.

Set this to *false* to remove that Context Menu item.

```ts {4}
  calculatedColumnOptions: {
    formulaTrace: {
      showContextMenuItem: false,
    },
  },
```