Expression Editor
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 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 |
Note
- The Query Builder is only for Boolean Standard Expressions
- Aggregated, Observable, Cumulative, Quantile and Window Expressions can only use the Expression Editor
- 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 ProjectsNamed Query, which itself uses theMIN_STARSQuery Variable - 2 Query Variables are provided (
MIN_STARSandFOCUS_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])} * 100Stars 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
Popin the Editor - autocomplete suggests thePopular ProjectsNamed Query - Switch to the Query Builder tab — both editors are available; Expression Editor is just the default
- Edit the
Language Share %andStars High (1h)Calculated Columns - the Functions dropdowns and toolbar chips change to match each Expression Type
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(andTRIGGER(it suggests typical values (e.g.5m,1h,'edit') Ctrl+Spaceopens 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.:
| 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.
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
categorystring 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
WHEREclause 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.
Sidebar
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,
$SCOPEis pinned at the top - A Lookups tab listing Named Queries and Query Variables when
QUERYand/orVARare 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.defaultColumnViewto'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'displayColumnFriendlyNamesForExpressions
Default: trueBooleanEditor Buttons
The buttons displayed in the Expression Editor can also be configured.
expressionEditorButtons
Default: undefinedfunction- 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
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