# Expression Editor

Canonical page: https://www.adaptabletools.com/docs/ui-expression-editor

- AdapTable provides an advanced Expression Editor for writing AdapTableQL Expressions
- It is used for every Expression Type: Standard, Aggregated, Observable, Cumulative, Quantile & Window
- However, the Functions dropdowns and toolbar chips change to match the Type being written
- Columns, Fields and Lookups can be dragged in for ease of expression completion
- Other features include live validation and extensive context-sensitive help

Run-time Expressions are created using AdapTable's **Expression Editor**.

- **A real code editor**: autocomplete for Columns, Functions, clauses, Named Queries and Variables, plus hover help on everything in the text
- **Mistakes caught as you type**: errors are underlined straight away, with one-click Quick Fixes for the common ones - and an invalid Expression cannot be saved
- **Help in context**: description, signatures and examples for the Function under the cursor, and an Examples panel for the current Expression Type
- **See the answer before saving**: a Result preview evaluates the Expression against sample values
- **One Editor for every Type**: Standard, Aggregated, Observable, Cumulative, Quantile and Window - the menus adapt to each
- **Your Functions included**: Custom Functions appear with their own documentation, exactly like shipped ones
- **Explain the numbers**: pair it with [Formula Trace](https://www.adaptabletools.com/docs/handbook-calculated-column-tracing-formula/index.md) to show users how each Calculated Column value was derived

- AdapTable also provides a [Query Builder](https://www.adaptabletools.com/docs/ui-query-builder/index.md) for Boolean Expressions
- Additionally there is a [Formula Editor](https://www.adaptabletools.com/docs/ui-formula-editor/index.md) for Calculated Column formulas

## One Editor, Many Scenarios

There is a **single** Expression Editor available for the many different Expression types that AdapTableQL supports.

However the Functions menus and toolbar chips it offers, changes according to the Expression type.

| Expression Type                           | What the Editor shows                                                    |
| ----------------------------------------- | ------------------------------------------------------------------------ |
| Standard (incl. Boolean) | Row-level Functions (`IF`, `ROUND`, `CONTAINS`, `TODAY`, `IS_BLANK`, …)  |
| Aggregated                                | Aggregation Functions (`SUM`, `AVG`, `COUNT`, `GROUP_BY`, `WHERE`, …)    |
| Observable                                | Observable Functions and clauses (`CHANGE_COUNT`, `HIGHEST`, `WITHIN`, …) |
| Cumulative                                | `CUMULATIVE` and related Aggregation chips (`SUM`, `AVG`, `GROUP_BY`, …) |
| Quantile                                  | `QUANT`, `QUARTILE`, `PERCENTILE`, `GROUP_BY`                            |
| Window                                    | `LAG`, `LEAD`, `FIRST`, `LAST`, `RANK`, `OVER`                           |

- The [Query Builder](https://www.adaptabletools.com/docs/ui-query-builder/index.md) is only for Boolean Standard Expressions
- Aggregated, Observable, Cumulative, Quantile and Window Expressions can only use the Expression Editor

**Example: AdapTableQL Expression Editor**

How to use the Expression Editor in AdapTable

- This demo opens the Grid Filter Settings Panel on the **Expression Editor** tab
- The current [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) is shown in the Editor - it uses the `Popular Projects` [Named Query](https://www.adaptabletools.com/docs/handbook-named-queries/index.md), which itself uses the `MIN_STARS` [Query Variable](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-var-function/index.md)
- 2 Query Variables are provided (`MIN_STARS` and `FOCUS_LANGUAGE`) so the **Lookups** panel is populated
- There are also 2 [Calculated Columns](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) to show the Editor for other Expression Types:
  - `Language Share %` - an Aggregated Expression: `[github_stars] / SUM{[github_stars], GROUP_BY([language])} * 100`
  - `Stars High (1h)` - an Observable Expression: `HIGHEST{[github_stars], WITHIN(1h)}`

- Use the Functions dropdowns and toolbar chips to change the Expression
- Open the Lookups panel and drag in `VAR('FOCUS_LANGUAGE')` to replace one of the languages
- Type `Pop` in the Editor - autocomplete suggests the `Popular Projects` Named Query
- Switch to the Query Builder tab — both editors are available; Expression Editor is just the default
- Edit the `Language Share %` and `Stars High (1h)` Calculated Columns - the Functions dropdowns and toolbar chips change to match each Expression Type

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: 'Expression Editor',
  expressionOptions: {
    editorOptions: {
      availableEditors: ['QueryBuilder', 'ExpressionEditor'],
      defaultEditor: 'ExpressionEditor',
    },
    queryVariables: [
      {
        name: 'MIN_STARS',
        value: 30000,
        description: 'Number of Github Stars a project needs to count as Popular',
      },
      {
        name: 'FOCUS_LANGUAGE',
        value: () => 'TypeScript', // would normally come from user settings or a server
        description: 'The language the team is currently focusing on',
      },
    ],
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
      ModuleButtons: ['CalculatedColumn', 'NamedQuery'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'GridFilter'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Popular Projects',
          Expression: "[github_stars] > VAR('MIN_STARS')",
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'languageShare',
          FriendlyName: 'Language Share %',
          Expression: '[github_stars] / SUM{[github_stars], GROUP_BY([language])} * 100',
          CalculatedColumnSettings: {DataType: 'number'},
        },
        {
          ColumnId: 'starsHigh',
          FriendlyName: 'Stars High (1h)',
          Type: 'Observable',
          Expression: 'HIGHEST{[github_stars], WITHIN(1h)}',
          CalculatedColumnSettings: {DataType: 'number'},
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'languageShare',
            'starsHigh',
            'github_watchers',
            'updated_at',
            'description',
            'created_at',
            'has_wiki',
            'pushed_at',
            'open_issues_count',
          ],
          GridFilter: {
            Expression:
              'QUERY("Popular Projects") AND ([language] = "TypeScript" OR [language] = "JavaScript")',
          },
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

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

export const onAdaptableReady = ({adaptableApi}: AdaptableReadyInfo) => {
  setTimeout(() => {
    adaptableApi.filterApi.gridFilterApi.openUIEditorForGridFilter();
  }, 1000);
};
```

## Editor Features

The Editor is designed to make it as easy as possible for runtime users to write and validate AdapTableQL.

It includes, for this reason, a number of powerful features, including:

- **Autocomplete** - Columns, Functions, clauses, Named Queries and Variables are suggested as you type
- **Hover help** - hover any Function, operator or Column in the text to see its documentation
- **Drag and drop** (or click) Columns, Fields, Functions and operators into the text
- **Toolbar chips** for the operators that are most useful for the current Expression Type
- **Functions dropdowns** listing every Function available for that Type (including [Custom Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md))
- A **Columns** panel with search, optional sample / test values, and any defined [Fields](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-row-data/index.md)
- A **Lookups** panel for [Named Queries](https://www.adaptabletools.com/docs/handbook-named-queries/index.md) (`QUERY`) and [Query Variables](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-var-function/index.md) (`VAR`)
- Live **validation** — errors underlined as you type, with one-click **Quick Fixes** for common mistakes; Apply / Run / Save stay disabled until Expression is valid
- **Formatting** - tidy the Expression with one click (or `Shift+Alt+F`)
- A **Result** preview evaluated against the sample / test values
- An **Examples** panel showing typical Expressions for the current Expression Type
- **Save As Query** - save a Boolean Expression as a reusable [Named Query](https://www.adaptabletools.com/docs/handbook-named-queries/index.md)
- Context-sensitive **help** for Function or Column at cursor — description, signatures, metadata and examples

### Autocomplete

Suggestions appear as you type, grouped into Columns, Variables, Named Queries, Clauses and Functions:

- Typing `[` lists the Columns
- Typing letters lists matching Columns, Functions and clauses
- Inside `WITHIN(`, `LAST_N(` and `TRIGGER(` it suggests typical values (e.g. `5m`, `1h`, `'edit'`)
- `Ctrl+Space` opens the full list at any point

Only Functions which are available for the current Module and Expression Type are suggested

### Hover Help

Hovering over a word in the Expression shows its documentation in a tooltip:

- **Functions and operators** - description, return type, signatures, clauses and examples
- **Columns** - friendly name, Column Id and data type

### Quick Fixes

Some validation errors offer a one-click fix in the error tooltip, e.g.:

| Written           | Quick Fix             |
| ----------------- | --------------------- |
| `SUM([PnL])`      | Rewrite as `SUM{[PnL]}` |
| `ROW_AGE{} > 30`  | Did you mean `30s`?   |

### Formatting

Click the **Format** button (or press `Shift+Alt+F`) to lay out the Expression consistently - upper-case keywords, standard spacing and quotes, and clauses in their usual order.

Formatting never changes what the Expression does

### Examples

The **Examples** panel (collapsed by default, under the Editor) lists typical Expressions for the Expression Type being written - e.g. Boolean, Aggregated, Observable or Window - each with a short description.

### Save As Query

When writing a Boolean Expression (e.g. a Grid Filter), the **Save As Query** button opens the Named Query wizard pre-filled with the current Expression, so it can be reused elsewhere via `QUERY("name")`.

The button is shown only when the Query Builder is also available, and the Named Query Module is editable

Set `showDocumentationLinks` to false in [User Interface Options](https://www.adaptabletools.com/docs/technical-reference-ui/index.md) to hide links from the Editor to these Help Pages

## Validation

AdapTableQL validates the Expression **as you type**.

It checks that the Expression:

- can be parsed
- uses Functions that are available for the current Module and Expression Type
- references Columns (and Fields) that exist and are queryable
- has matching types (e.g. a numeric Function is not given a text Column)

Errors are underlined in the Editor text and listed underneath. Warnings (for example a type that looks wrong but still parses) appear in the same place.

Apply, Run and Save stay disabled until the Expression is valid; the user cannot continue with an invalid Expression

When the Expression is valid, a **Result** preview is evaluated against the sample / test values in the Columns panel.

Set `performExpressionValidation` to _false_ in [Expression Options](https://www.adaptabletools.com/docs/technical-reference-expression/index.md) to allow invalid Expressions to be run or saved. That is mainly useful when [evaluating on your server](https://www.adaptabletools.com/docs/adaptable-server-evaluation/index.md).

## Functions Dropdown

[Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) are listed in dropdowns on the toolbar above the Editor.

For **Standard** Expressions the Functions are grouped as:

- Logical
- Text
- Numeric
- Date
- General
- Custom (any Function without a category, plus those registered with `category: 'custom'`)

A **Search functions** box searches across all groups.

Hovering a Function opens a documentation panel (what it does, arguments, return type, examples).

Clicking or dragging the Function inserts it in the Editor at the cursor.

- This list includes any [Custom Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md) provided by developers at design-time
- A custom `category` string creates an extra dropdown with that name

For **Aggregated**, **Observable**, **Cumulative**, **Quantile** and **Window** Expressions the Functions menu is sectioned for that Type (e.g. Aggregation, Observable Events, Window) instead of the Standard groups.

- Some aggregation expressions allow a `WHERE` clause to be used to filter what is returned
- If activated, the Standard groups (Logical, Text, …) are displayed so the expression can leverage Boolean Functions

## Toolbar Chips

Commonly used operators appear as chips under the Functions menus.

The chip set follows the Expression Type — for example comparison and maths chips for Standard Expressions, Aggregation chips (`SUM`, `COUNT`, `GROUP_BY`, …) for Aggregated, and Event chips for Observable.

Chips [can be customised](#configuring-the-editor) with `expressionOptions.expressionEditorButtons`.

## Sidebar

The Expression Editor includes a sidebar on the left, which includes:

- Every [Queryable](https://www.adaptabletools.com/docs/adaptable-ql-expression-managing/index.md#limiting-queryable-columns) **Column** using the Column Header by default
- A **search** box filters by Header or Column Id
- **Fields** (if defined) appear in the same list and insert as `FIELD("name")`
- If the Module supplies a Column scope, **`$SCOPE`** is pinned at the top
- A **Lookups** tab listing Named Queries and Query Variables when `QUERY` and/or `VAR` are available

Clicking a lookup inserts `QUERY("name")` or `VAR('name')`

The panel can be **expanded** to show an editable sample / test value for each Column.

This is seeded from data in the first row in the Grid.

- Set `editorOptions.defaultColumnView` to `'expanded'` to open the panel with sample values visible
- Users are still able collapse or expand it at run-time

## WHERE Clause

Aggregated and Observable Expressions can include a `WHERE` clause to limit which rows take part.

When `WHERE` is part of the Expression (inserted via the **Where** chip, or typed), the Functions menus and chips add the Boolean / Standard operators needed to write the filter.

You can use (the Excel-like) `SUMIF` / `COUNTIF` / `AVGIF` as an alternative to `WHERE` for filtered Aggregations

## Column Ids & Headers

Many Columns have a separate Id and Header.

The Id is typically the field in the data source, or a unique identifier. The Header is what appears in the Grid.

The Expression Editor uses both:

- Columns are listed using **Header** (Friendly Name) by default; there is a checkbox to show Column Ids instead
- In the Editor text the **Column Id** is used inside square brackets, because it is guaranteed to be unique

Sharing a Header is common with Column Groups — e.g. multiple groups each have `Buy` and `Sell` Columns

Wherever an Expression is shown but not being edited (e.g. in the Settings Panel), the Column Header is used.

## Configuring the Editor

There are a couple of useful properties available to developers to configure the Expression Editor.

### `editorOptions.defaultColumnView`

Initial Columns / Fields panel layout in the Expression Editor

- `collapsed` — Column names only (more room for the Editor)
- `expanded` — names plus sample / test value inputs

```ts {3}
expressionOptions: {
  editorOptions: {
    defaultColumnView: 'expanded',
  },
},
```

### `displayColumnFriendlyNamesForExpressions`

Show Column Headers instead of Column Ids in Expression overviews

When _true_ (the default), read-only Expression displays — Settings Panel lists, wizard summaries, Grid Filter / Alert / Report overviews — rewrite `[columnId]` to the Column Header.

The Expression stored in State, typed in the Editor, and evaluated by AdapTableQL still uses Column Ids.

Set this to _false_ to keep Column Ids in those overviews.

```ts {3}
expressionOptions: {
  displayColumnFriendlyNamesForExpressions: false,
},
```

### Editor Buttons

The buttons displayed in the Expression Editor can also be configured.

### `expressionEditorButtons`

Customise the function / operator chips above the Expression Editor

Called per editor surface with AdapTable's `defaultButtons` for that Type.

Return those unchanged to keep defaults, or amend / replace them (useful when adding chips for Custom Functions).

The `editorType` is one of `scalar`, `aggregated`, `cumulative`, `quantile`, `window` or `observable`.

The function receives an [`ExpressionEditorButtonsContext`](https://www.adaptabletools.com/docs/reference/expressioneditorbuttonscontext.md) defined as follows:

| Property | Type | Description |
| --- | --- | --- |
| [defaultButtons](https://www.adaptabletools.com/docs/reference/expressioneditorbuttonscontext.md#defaultbuttons) | [`ExpressionEditorButton`](https://www.adaptabletools.com/docs/reference/expressioneditorbutton.md)`[]` | AdapTable&#39;s default buttons for this editor type |
| [editorType](https://www.adaptabletools.com/docs/reference/expressioneditorbuttonscontext.md#editortype) | [`ExpressionEditorType`](https://www.adaptabletools.com/docs/reference/expressioneditortype.md) | Coarse editor category: `scalar` \| `aggregated` \| `cumulative` \|<br/>`quantile` \| `window` \| `observable` (the Observable Calculated Column<br/>editor — hosts that add chips to `scalar` usually want them here too) |
| [adaptableContext](https://www.adaptabletools.com/docs/reference/expressioneditorbuttonscontext.md#adaptablecontext) | `any` | Custom application Context provided in `AdaptableOptions.adaptableContext` |

```ts {2}
expressionOptions: {
  expressionEditorButtons: (context: ExpressionEditorButtonsContext) => {
    if (context.editorType !== 'scalar') {
      return context.defaultButtons;
    }
    return [
      { functionName: 'AND', data: 'AND', text: 'AND' },
      { functionName: 'OR', data: 'OR', text: 'OR' },
      { functionName: 'CONTAINS', data: 'CONTAINS', text: 'Contains' },
      { functionName: 'IN', data: 'IN', text: 'In' },
      { functionName: 'IS_BLANK', data: 'IS_BLANK()', text: 'Is Blank' },
    ];
  },
},
```

Only chips [whose Function is available for the current Module](https://www.adaptabletools.com/docs/adaptable-ql-expression-managing/index.md) are rendered

**Example: Custom Expression Editor Buttons**

Replacing the default Expression Editor chips

- This demo replaces the default Standard chips (AND / OR plus maths and comparison icons) with a smaller labelled set: AND, OR, Contains, In, Between, Is Blank
- The Grid Filter uses two of those chips: `CONTAINS([description], "web") AND [language] IN ("TypeScript", "JavaScript")`
- Other Expression Types keep AdapTable's default chips

- Note the toolbar chips under the Functions menus — they are not the default icon row
- Click a chip to insert it into the Expression

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,
  ExpressionEditorButtonsContext,
} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Custom Expression Editor Buttons',
  expressionOptions: {
    editorOptions: {
      availableEditors: ['QueryBuilder', 'ExpressionEditor'],
      defaultEditor: 'ExpressionEditor',
    },
    expressionEditorButtons: (context: ExpressionEditorButtonsContext) => {
      if (context.editorType !== 'scalar') {
        return context.defaultButtons;
      }
      return [
        {functionName: 'AND', data: 'AND', text: 'AND'},
        {functionName: 'OR', data: 'OR', text: 'OR'},
        {functionName: 'CONTAINS', data: 'CONTAINS', text: 'Contains'},
        {functionName: 'IN', data: 'IN', text: 'In'},
        {functionName: 'IS_BLANK', data: 'IS_BLANK()', text: 'Is Blank'},
      ];
    },
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
    },
    StatusBar: {
      StatusBars: [
        {
          Key: 'Center Panel',
          StatusBarPanels: ['Layout', 'GridFilter'],
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'name',
            'github_stars',
            'language',
            'github_watchers',
            'updated_at',
            'description',
            'created_at',
            'has_wiki',
            'pushed_at',
            'open_issues_count',
          ],
          GridFilter: {
            Expression:
              'CONTAINS([description], "web") AND [language] IN ("TypeScript", "JavaScript")',
          },
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

**`src/onAdaptableReady.ts`**

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

export const onAdaptableReady = ({adaptableApi}: AdaptableReadyInfo) => {
  setTimeout(() => {
    adaptableApi.filterApi.gridFilterApi.openUIEditorForGridFilter();
  }, 1000);
};
```

## Theming

The Expression Editor is fully themable.

AdapTable ships with a number of CSS Variables to allow full UI control: `--ab-color-aql-*` for the syntax and column-reference colours (also editable in the DevTools Theme Builder) and `--ab-cmp-aql-editor__*` for the editor chrome.

You can override these for both light and dark Themes, like any other AdapTable component variables

Developers can choose which colours to use for syntax highlighting, token colours, writing-surface colours etc.

See [Customising Themes — Expression Editor](https://www.adaptabletools.com/docs/handbook-theming-custom/index.md#expression-editor) for the full variable list, with light / dark defaults and an override example
