Expression Editor

View as Markdown

Summary

  • 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.

Why use the 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 to show users how each Calculated Column value was derived

Hint

  • AdapTable also provides a Query Builder for Boolean Expressions
  • Additionally there is a Formula Editor 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 TypeWhat the Editor shows
Standard (incl. Boolean)Row-level Functions (IF, ROUND, CONTAINS, TODAY, IS_BLANK, …)
AggregatedAggregation Functions (SUM, AVG, COUNT, GROUP_BY, WHERE, …)
ObservableObservable Functions and clauses (CHANGE_COUNT, HIGHEST, WITHIN, …)
CumulativeCUMULATIVE and related Aggregation chips (SUM, AVG, GROUP_BY, …)
QuantileQUANT, QUARTILE, PERCENTILE, GROUP_BY
WindowLAG, LEAD, FIRST, LAST, RANK, OVER

Note

  • The Query Builder is only for Boolean Standard Expressions
  • Aggregated, Observable, Cumulative, Quantile and Window Expressions can only use the Expression Editor
AdapTableQL Expression Editor
Fork
  • This demo opens the Grid Filter Settings Panel on the Expression Editor tab
  • The current Grid Filter is shown in the Editor - it uses the Popular Projects Named Query, which itself uses the MIN_STARS Query Variable
  • 2 Query Variables are provided (MIN_STARS and FOCUS_LANGUAGE) so the Lookups panel is populated
  • There are also 2 Calculated Columns 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)}
Try It Out
  • 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
Loading demo…

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)
  • A Columns panel with search, optional sample / test values, and any defined Fields
  • A Lookups panel for Named Queries (QUERY) and Query Variables (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
  • 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

Hint

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.:

WrittenQuick Fix
SUM([PnL])Rewrite as SUM{[PnL]}
ROW_AGE{} > 30Did 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.

Note

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").

Note

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

Hint

Set showDocumentationLinks to false in User Interface Options 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.

Caution

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.

Hint

Set performExpressionValidation to false in Expression Options to allow invalid Expressions to be run or saved. That is mainly useful when evaluating on your server.

Functions Dropdown

Expression Functions 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.

Note

  • This list includes any Custom Expression Functions 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.

Hint

  • 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 with expressionOptions.expressionEditorButtons.

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

  • Every Queryable 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

Note

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.

Hint

  • 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.

Hint

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

Note

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

Default: 'collapsed'
Initial Columns / Fields panel layout in the Expression Editor

displayColumnFriendlyNamesForExpressions

Default: true
Boolean
Show Column Headers instead of Column Ids in Expression overviews

Editor Buttons

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

expressionEditorButtons

Default: undefined
function
Customise the function / operator chips above the Expression Editor
Custom Expression Editor Buttons
Fork
  • 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
Try It Out
  • Note the toolbar chips under the Functions menus — they are not the default icon row
  • Click a chip to insert it into the Expression
Loading demo…

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.

Hint

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.

Find Out More

See Customising Themes — Expression Editor for the full variable list, with light / dark defaults and an override example