Welcome to AdapTableQL

View as Markdown

Summary

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.

Why use AdapTableQL?

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, or to developer-supplied values via VAR
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.

Note

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.

What Questions can AdapTableQL Answer?

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
What share of its Desk's Notional is this trade?	`[Notional] / SUM{[Notional], GROUP_BY([Desk])} * 100`	Aggregation
Has total Open PnL gone below -500K?	`SUMIF{[PnL], [Status] = 'Open'} < '-500K'`	Aggregation
What is each Book's running PnL?	`SUM{[PnL], GROUP_BY([Book]), CUMULATIVE([TradeDate])}`	Cumulative
What was each Ticker's day-on-day move?	`PERCENT_DIFF([Close], LAG{[Close], 1, GROUP_BY([Ticker]), OVER([Date])})`	Window
Which PnL decile is this trade in?	`QUANT{[PnL], 10}`	Quantile
Did the Price just jump by more than 5%?	`PERCENT_CHANGE([Price], 'INCREASE') > 5`	Relative Change
Which Prices have ticked more than 3 times in 5 mins?	`CHANGE_COUNT{[Price], WITHIN(5m)} > 3`	Observable
Which Prices have not ticked for 20 seconds?	`NO_CHANGES{[Price], WITHIN(20s)}`	Observable
What is the VWAP for each Ticker?	`VWAP{[Price], WEIGHT([Quantity]), GROUP_BY([Ticker])}`	Custom Aggregation (developer-provided; not shipped)

AdapTableQL Workflow

The workflow when using AdapTableQL is straightforward and consistent

You give AdapTable an Expression — a short, human-readable string
AdapTableQL evaluates that string against live grid data and returns a result
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, Grid Filter, Format Column, Alert, Custom Report, Plus Minus, Styled Column Badges
single value	Calculated Column
aggregated value	Calculated Column, Alert
observable value	Alert, Calculated Column, Conditional Styles, Flashing Cells

Note

The Expression is the same whether a business user builds it in the UI or a developer ships it in Adaptable State
AdapTableQL re-evaluates as the data changes — you do not write a separate “live” version

AdapTableQL in action

Fork Fork

This demo uses one Query Language in five Modules at the same time:
- A Grid Filter shows rows where Language is JavaScript
- A Format Column highlights rows where Github Stars > 50,000
- An Alert fires when Open Issues > 50
- An Export Report includes only rows with an MIT License
- A Calculated Column (Full Github) multiplies Stars × Watchers

Try It Out

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

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  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,
        },
      ],
    },
  },
};

Read-only

Loading demo…

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
Aggregation Expressions look across many rows — including Cumulative, Quantile and Window
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

Find Out More

Read the Guide to AdapTableQL Expressions 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 — 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
`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 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 (Standard (one row) or Aggregated).

They are treated as first-class functions: listed in the Expression Editor with documentation, validated like system Functions, and evaluated by the same engine.

Hint

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 for creating and inspecting Expressions:

Component	Description
Query Builder	Builds Boolean Expressions from dropdowns, starting point for standard expressions
Expression Editor	Hand-written editor for any Expression Type; used for complex Expressions
Formula Editor	Inspect and inline-edit a Calculated Column formula

Note

The Editors validate as you type; an invalid Expression cannot be run or saved

Building an Expression in the Query Builder

Fork Fork

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

Try It Out

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

Loading demo…

Hint

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

Where Next?

If you want to…	Go to
Discover the 3 Expression types and which Modules use them	Guide to AdapTableQL Expressions
Save an Expression and reuse it with `QUERY`	Named Queries
Learn how to write an Expression (syntax, columns, `AND` / `OR`, `WHERE`)	Guide to Writing Expressions
Configure the Expression UI Editors	Expression UI Components
Run an Expression from your own code	Evaluating Expressions
See every Function available in the library	Functions Library
Supplement AdapTableQL with your own Functions	Writing Custom Functions

FAQ

Do my users need to learn a new language? No. The Query Builder builds Expressions from dropdowns, and a handwritten Expression reads almost like plain English. Power users can go straight to the Expression Editor.

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.

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.

Can I evaluate Expressions from my own code? Yes — the Expression API evaluates any Expression on demand.

Can I add my own Functions — or hide some? Yes. Register Custom Functions, and use Function Scope to control which Modules can use them (or to hide System Functions).

How are Expressions stored? As plain, readable strings in Adaptable State. 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.

NextExpressions Guide