# Welcome to AdapTableQL Canonical page: https://www.adaptabletools.com/docs/adaptable-ql - AdapTableQL is AdapTable's bespoke human-readable query language - There is a single engine that powers Filters, Conditional Styles, Alerts, Calculated Columns, Reports etc. - You write (or build via UI wizards) a short **Expression** and AdapTableQL evaluates it against live grid data - One language covers many jobs: a boolean result can filter or style, a value can fill a Calculated Column - Expressions can be handwritten, created in the UI, shipped in Initial State, or run via the API - AdapTable also provides you with multiple aids so you can evaluate AdapTableQL on your server - Developers can add their own Functions — a Quant library if they want — and users can use them immediately AdapTableQL is the custom Query Language that powers many of AdapTable's most advanced features. - **One language everywhere**: one Expression can filter Grid, style cells, fire Alerts, build Reports & Calculated Columns - **Readable**: `[Desk] = 'Rates' AND [Notional] > '5M'` is the Expression, not a translation of it - **Looks across rows**: per-row aggregations, running totals, ranks, quantile buckets and previous / next row lookups - **Looks back in time**: Observable Expressions read each cell's change history, e.g. *ticked 3 times in 5 minutes* - **Composable**: Expressions can refer to other Expressions via [QUERY](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-query-function/index.md), or to developer-supplied values via [VAR](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-var-function/index.md) - **Easy to write**: Query Builder for simple conditions; Expression Editor with autocomplete, hover help and Quick Fixes - **Parsed and validated** Expressions are never executed as JavaScript; invalid Expressions cannot be run or saved - **Extensible**: Custom Functions (including Aggs) sit alongside shipped Functions, with same editor help & validation - **Server ready**: evaluate in the browser, or hand the Expression (and its AST) to your server ## Why AdapTableQL is Special Most DataGrids treat filtering, styling, alerting and calculated columns as separate languages and concerns. And generally you will need to write custom code to provide features like Conditional Styles or Alerts. - AG Grid has many powerful features but lacks a few advanced Expression features that AdapTable provides - Advanced Filter and Calculated Columns have different syntax, and conditional styles and alerts need code - AG Grid's formulas can only reach another row by its fixed row number - There is no cell history so it's not possible to create observable expressions AdapTableQL offers all these advanced features out of the box and provides a single language for all of them. Each of these is a single Expression - with no custom code: | Question | Expression | Type | | ----------------------------------------------------- | -------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------- | | Which Rates trades are over 5M? | `[Desk] = 'Rates' AND [Notional] > '5M'` | [Standard](https://www.adaptabletools.com/docs/adaptable-ql-expression-standard/index.md) | | What share of its Desk's Notional is this trade? | `[Notional] / SUM{[Notional], GROUP_BY([Desk])} * 100` | [Aggregation](https://www.adaptabletools.com/docs/adaptable-ql-expression-aggregation/index.md) | | Has total Open PnL gone below -500K? | `SUMIF{[PnL], [Status] = 'Open'} < '-500K'` | [Aggregation](https://www.adaptabletools.com/docs/adaptable-ql-expression-aggregation/index.md) | | What is each Book's running PnL? | `SUM{[PnL], GROUP_BY([Book]), CUMULATIVE([TradeDate])}` | [Cumulative](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md) | | What was each Ticker's day-on-day move? | `PERCENT_DIFF([Close], LAG{[Close], 1, GROUP_BY([Ticker]), OVER([Date])})` | [Window](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md) | | Which PnL decile is this trade in? | `QUANT{[PnL], 10}` | [Quantile](https://www.adaptabletools.com/docs/adaptable-ql-expression-quantile/index.md) | | Did the Price just jump by more than 5%? | `PERCENT_CHANGE([Price], 'INCREASE') > 5` | [Relative Change](https://www.adaptabletools.com/docs/adaptable-ql-expression-relative-change/index.md) | | Which Prices have ticked more than 3 times in 5 mins? | `CHANGE_COUNT{[Price], WITHIN(5m)} > 3` | [Observable](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) | | Which Prices have not ticked for 20 seconds? | `NO_CHANGES{[Price], WITHIN(20s)}` | [Observable](https://www.adaptabletools.com/docs/adaptable-ql-expression-observable/index.md) | | What is the VWAP for each Ticker? | `VWAP{[Price], WEIGHT([Quantity]), GROUP_BY([Ticker])}` | [Custom Aggregation](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-aggregation/index.md) (developer-provided; not shipped) | ## AdapTableQL Workflow The workflow when using AdapTableQL is straightforward and consistent 1. You give AdapTable an Expression — a short, human-readable string 2. AdapTableQL evaluates that string against live grid data and returns a result 3. The Module that owns the Expression decides what happens next The result of the Expression varies depending on the use case: | Result | Typical use | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **boolean** (`true` / `false`) | [Column Filters](https://www.adaptabletools.com/docs/handbook-column-filter/index.md), [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md), [Format Column](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md), [Alert](https://www.adaptabletools.com/docs/handbook-alerting/index.md), [Custom Report](https://www.adaptabletools.com/docs/handbook-exporting-reports-custom/index.md), [Plus Minus](https://www.adaptabletools.com/docs/handbook-editing-plus-minus/index.md), [Styled Column Badges](https://www.adaptabletools.com/docs/handbook-styled-column-badge/index.md) | | **single value** | [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) | | **aggregated value** | [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column-aggregated/index.md), [Alert](https://www.adaptabletools.com/docs/handbook-alerting-aggregation/index.md) | | **observable value** | [Alert](https://www.adaptabletools.com/docs/handbook-alerting-observables/index.md), [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column-observable/index.md), [Conditional Styles](https://www.adaptabletools.com/docs/handbook-column-formatting-conditions-observable/index.md), [Flashing Cells](https://www.adaptabletools.com/docs/handbook-flashing-cell/index.md) | - The Expression is the same whether a business user builds it in the UI or a developer ships it in [Adaptable State](https://www.adaptabletools.com/docs/dev-guide-adaptable-state-initial-state/index.md) - AdapTableQL re-evaluates as the data changes — you do not write a separate “live” version **Example: AdapTableQL in action** One Expression language: filter, style, alert, export and calculate - This demo uses **one Query Language** in five Modules at the same time: - A [Grid Filter](https://www.adaptabletools.com/docs/handbook-grid-filter/index.md) shows rows where `Language` is _JavaScript_ - A [Format Column](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) highlights rows where `Github Stars` > 50,000 - An [Alert](https://www.adaptabletools.com/docs/handbook-alerting/index.md) fires when `Open Issues` > 50 - An [Export](https://www.adaptabletools.com/docs/handbook-exporting/index.md) Report includes only rows with an _MIT License_ - A [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md) (`Full Github`) multiplies Stars × Watchers - Change the Grid Filter in the Toolbar — the other Expressions keep running - Edit a cell so `Open Issues` goes above 50 and watch the Alert fire - Open Settings to see each Expression as the same readable string 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 [template] 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 = { primaryKey: 'id', adaptableId: 'AdapTableQL in action', initialState: { Dashboard: { Tabs: [ { Name: 'Demo', Toolbars: ['GridFilter', 'Export', 'Alert'], }, ], }, StatusBar: { StatusBars: [ { Key: 'Center Panel', StatusBarPanels: ['Export', 'Alert', 'GridFilter'], }, ], }, Theme: {CurrentTheme: 'dark'}, CalculatedColumn: { CalculatedColumns: [ { ColumnId: 'full_Github', FriendlyName: 'Full Github', Expression: '[github_stars] * [github_watchers]', CalculatedColumnSettings: { DataType: 'number', }, }, ], }, Export: { CurrentReport: 'MIT Report', CurrentFormat: 'JSON', Reports: [ { Name: 'MIT Report', ReportColumnScope: 'ScopeColumns', ReportRowScope: 'ExpressionRows', Scope: { ColumnIds: ['name', 'language', 'topics', 'license', 'updated_at'], }, Expression: " [license] = 'MIT License'", }, ], }, FormatColumn: { FormatColumns: [ { Name: 'style-all', Style: { BackColor: 'var(--ab-color-palette-26)', ForeColor: 'var(--ab-color-palette-13)', }, Scope: { All: true, }, Rule: '[github_stars] > 50000 ', }, ], }, Alert: { AlertDefinitions: [ { Name: 'alert-Warning-42', MessageType: 'Warning', Scope: { All: true, }, Rule: '[open_issues_count] > 50 ', AlertProperties: { DisplayNotification: true, }, }, ], }, Layout: { CurrentLayout: 'Standard Layout', Layouts: [ { TableColumns: [ 'name', 'language', 'open_issues_count', 'license', 'github_watchers', 'github_stars', 'full_Github', 'created_at', 'has_wiki', 'updated_at', 'pushed_at', 'closed_issues_count', 'open_pr_count', 'closed_pr_count', 'has_projects', 'has_pages', 'week_issue_change', ], Name: 'Standard Layout', GridFilter: { Expression: '[language]="JavaScript" ', }, AutoSizeColumns: true, }, ], }, }, }; ``` ## Expressions **Expressions** lie at the heart of AdapTableQL. It is their evaluation that provides the power, elegance and simplicity of the single Query Language. An Expression is a human-readable query built from Columns, Functions (and operators) and values. Expressions come in 3 types (with specialist variants): - **Standard** Expressions (the most common) evaluate one row at a time, returning a single value — including [Relative Change](https://www.adaptabletools.com/docs/adaptable-ql-expression-relative-change/index.md) - **Aggregation** Expressions look across many rows — including [Cumulative](https://www.adaptabletools.com/docs/adaptable-ql-expression-cumulative/index.md), [Quantile](https://www.adaptabletools.com/docs/adaptable-ql-expression-quantile/index.md) and [Window](https://www.adaptabletools.com/docs/adaptable-ql-expression-window/index.md) - **Observable** Expressions read a cell's change history over time — e.g. how many times it ticked in the last 5 minutes, or its highest value today Read the [Guide to AdapTableQL Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) for more details ## Functions An Expression is typically one or more **Expression Functions** applied to Columns and values. AdapTableQL ships [a very large set of Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) — logical, text, numeric, date, aggregation, and more. Below are a few Functions which show how much a single Expression can do: | Function | Example | What it does | | ----------------------------------- | --------------------------------------------- | -------------------------------------------------------------------------------------------------------- | | `IS_WORKDAY` / `IS_HOLIDAY` / `WORKDAY` / `NETWORKDAYS` | `NETWORKDAYS([TradeDate], [SettlementDate])` | Workday maths using [Holiday Calendars](https://www.adaptabletools.com/docs/dev-guide-tutorial-holiday-calendars/index.md) | | `IFERROR` | `IFERROR([PnL] / [Notional], 0)` | Returns fallback value instead of error | | `SUMIF` / `COUNTIF` / `AVGIF` | `SUMIF{[PnL], [Status] = 'Open'}` | Excel-style filtered Aggregations | | `RANK` / `LAG` / `LEAD` | `RANK{GROUP_BY([Desk]), OVER([PnL], 'DESC')}` | Rank rows in given order | | `QUANT` / `QUARTILE` / `PERCENTILE` | `QUANT{[PnL], 10}` | Puts each row into a bucket | | `HIGHEST` / `LOWEST` | `HIGHEST{[Price], WITHIN(1h)}` | Highest/lowest cell value in time period | | `NO_CHANGES` | `NO_CHANGES{[Price], WITHIN(20s)}` | True when a cell has gone quiet | | `ROW_AGE` | `ROW_AGE{} < 30s` | How long ago a row was added | | `QUERY` | `QUERY("Big Orders") AND [Currency] = 'USD'` | Reuse [Named Query](https://www.adaptabletools.com/docs/handbook-named-queries/index.md) in 2nd Expression | | `VAR` | `[price] * VAR('VAT')` | Inject a developer-provided value | | `IF` / `CASE` | `[Comments] > 100 ? 'Big' : 'Small'` | Conditional logic (ternary or `CASE`) | | `$SCOPE` | `IS_POSITIVE($SCOPE)` | Stand-in for current column in Scope | Operators such as `=`, `>` and `AND` are Functions too. Additionally, Developers can register [Custom Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md) ([Standard](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-standard/index.md) (one row) or [Aggregated](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-aggregation/index.md)). They are treated as first-class functions: listed in the Expression Editor with documentation, validated like system Functions, and evaluated by the same engine. - You can add a single helper, or an entire **Quant library** if you wish - Run-time users see and access these Functions in Filters, Alerts, Calculated Columns; anywhere AdapTableQL runs ## UI Components AdapTableQL is human readable and writable with a clear, easy to understand syntax. But you do not have to write AdapTableQL by hand. AdapTable provides 3 [Expression UI Components](https://www.adaptabletools.com/docs/adaptable-ql-expression-ui/index.md) for creating and inspecting Expressions: | Component | Description | | -------------------------------------------------------------------- | ---------------------------------------------------------------------------------- | | [Query Builder](https://www.adaptabletools.com/docs/ui-query-builder/index.md) | Builds Boolean Expressions from dropdowns, starting point for standard expressions | | [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md) | Hand-written editor for any Expression Type; used for complex Expressions | | [Formula Editor](https://www.adaptabletools.com/docs/ui-formula-editor/index.md) | Inspect and inline-edit a Calculated Column formula | The Editors **validate as you type**; an invalid Expression cannot be run or saved **Example: Building an Expression in the Query Builder** A Boolean Expression built from dropdowns, not typed AQL - This demo opens the Grid Filter Settings Panel on the **Query Builder** - The Filter is a column comparison (`Github Stars` greater than `Github Watchers`) plus an AND / OR group for language - Change a dropdown — the Output Expression at the bottom updates as you build - Switch to the Expression Editor tab to see the same Expression as handwritten AQL 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 = { primaryKey: 'id', adaptableId: 'AdapTableQL Query Builder', expressionOptions: { editorOptions: { availableEditors: ['QueryBuilder', 'ExpressionEditor'], defaultEditor: 'QueryBuilder', defaultColumnView: 'expanded', }, }, 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: '[github_stars] > [github_watchers] 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); }; ``` - By default both the Query Builder and the Expression Editor are offered - Use `expressionOptions.editorOptions` to [choose which editors are available and which opens first](https://www.adaptabletools.com/docs/adaptable-ql-expression-ui/index.md) ## Where Next? | If you want to… | Go to | | ------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- | | Discover the 3 Expression types and which Modules use them | [Guide to AdapTableQL Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression/index.md) | | Save an Expression and reuse it with `QUERY` | [Named Queries](https://www.adaptabletools.com/docs/handbook-named-queries/index.md) | | Learn how to write an Expression (syntax, columns, `AND` / `OR`, `WHERE`) | [Guide to Writing Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-writing/index.md) | | Configure the Expression UI Editors | [Expression UI Components](https://www.adaptabletools.com/docs/adaptable-ql-expression-ui/index.md) | | Run an Expression from your own code | [Evaluating Expressions](https://www.adaptabletools.com/docs/adaptable-ql-expression-evaluating/index.md) | | See every Function available in the library | [Functions Library](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) | | Supplement AdapTableQL with your own Functions | [Writing Custom Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md) | ### FAQ **Do my users need to learn a new language?** No. The [Query Builder](https://www.adaptabletools.com/docs/ui-query-builder/index.md) builds Expressions from dropdowns, and a handwritten Expression reads almost like plain English. Power users can go straight to the [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md). **How is AdapTableQL different from SQL or Excel formulas?** It borrows the familiar parts — Column references, `AND` / `OR`, functions like `IF` — but it runs live against Grid data, and one Expression can filter, style, alert, calculate or export without being rewritten for each job. **Can a user run arbitrary code through an Expression?** No. Expressions are parsed and validated, never executed as JavaScript; only the shipped Functions and the Custom Functions you register can run. You can use JavaScript to define [Custom Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md). **Does AdapTableQL work with server-side data?** Yes. AdapTable gives you the Expression (and its AST) plus the relevant events, [so you can evaluate it on your server](https://www.adaptabletools.com/docs/adaptable-server-evaluation/index.md). **Can I evaluate Expressions from my own code?** Yes — the Expression API [evaluates any Expression on demand](https://www.adaptabletools.com/docs/adaptable-ql-expression-evaluating/index.md). **Can I add my own Functions — or hide some?** Yes. Register [Custom Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom/index.md), and use [Function Scope](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-scope/index.md) to control which Modules can use them (or to hide System Functions). **How are Expressions stored?** As plain, readable strings in [Adaptable State](https://www.adaptabletools.com/docs/dev-guide-adaptable-state-initial-state/index.md). Expressions are easy to ship in Initial State, share between users, or check in code review. **Do I need to write a separate "live" version of an Expression?** No, Expressions re-evaluate automatically as the data changes.