Guide to Writing Expressions

An AdapTableQL Expression is a human-readable string which AdapTable evaluates against the data in the grid.

This page shows how to write one - it is both a quick tour of what Expressions can do and a reference for developers who provide Expressions in Initial Adaptable State.

Reads Like the Question

Each Expression reads very close to the business question it answers:

Anatomy of an Expression

Expressions can be complex, but they are built from a small set of elements:

This boolean Expression uses 1–4:

 MIN([BloombergBid], [MarkitBid]) > 50 OR [Currency] = 'USD'

// 1 — Columns: `BloombergBid`, `MarkitBid`, `Currency`
// 2 — Functions / operators: `MIN`, `>`
// 3 — Logical: `OR`
// 4 — Input values: `50`, `'USD'`

This Aggregation Expression adds WHERE so all 5 appear:

 SUM{[PnL]} > '5M' WHERE [Currency] = 'USD' AND [Book] = 'Rates'

// 1 — Columns: `PnL`, `Currency`, `Book`
// 2 — Functions / operators: `SUM`, `>`
// 3 — Logical: `AND`
// 4 — Input values: `'5M'`, `'USD'`, `'Rates'`
// 5 — `WHERE`

The type pages use these same colours for their examples.

1. Columns

A Column is referenced by its columnId in square brackets. AdapTableQL reads that cell's value when the Expression evaluates.

// Reference Price Column (in a value-producing Expression)
[Price] * 100;

// Reference Currency Column (in a Boolean Expression)
[Currency] = 'USD';

There is no limit on how many Columns an Expression can include:

// Multiply 3 columns together
PRODUCT([OrderCost], [ItemCost], [PackageCost]);

And AdapTable allows you to use Columns in both left and right side of a comparison Expression:

// Compare 2 columns
[OrderCost] > [ItemCost];

The stored Expression always uses the Column Id, not the Header — e.g. [orderId], not Order Id.

FIELD

A Field is a value in the row data that is not a Column.

Unlike with Column, there is no square-bracket shortcut — always use FIELD:

FIELD('Price') * 100;

$SCOPE

$SCOPE stands in for the current Column when the Module has Column Scope (Format Column, Alert, Flashing Cell, Column Filter, Plus Minus, and Named Queries referenced from those Modules).

Treat it conceptually like a Column (which is what it repeatedly gets translated to at evaluation time):

IS_POSITIVE($SCOPE);

It is not valid in a Grid Filter, as there is no current Column; a Named Query can use it, but only when referenced from a Module with Column Scope.

2. Expression Functions

Expression Functions are the core of every Expression.

Function names - and keywords such as AND, OR and WHERE - are not case sensitive, but by convention they are written in capitals (as are Custom Functions).

Many Functions take arguments, which are placed inside brackets.

Arguments are often Columns:

// Standard — average of 3 cells in this row (parentheses)
AVG([bloombergBid], [markitBid], [indicativeBid]);

// Aggregation — average of the Price column across rows (curly braces)
AVG{[Price]};

They can also be literals, or other Functions (the inner Function must return the right data type):

// The date 5 days from now
ADD_DAYS(CURRENT_DAY(), 5);

// Is TradeDate in the next 5 days
ADD_DAYS(CURRENT_DAY(), 5) < [TradeDate];

Other typical Functions:

COALESCE([bloombergPrice], [indicativePrice], [markitPrice]);

CONCAT([currency], '-', [country]);

MAX([ItemCost], [OrderCost], [InvoicedCost], [PackageCost] * 10);

Operator shortcuts

Some of the most common Functions, particularly those which perform comparisons, also have operator shortcuts:

So these pairs are the same Expression:

GT([Price], 100);
// is the same as
[Price] > 100;

Functions and operators can be mixed:

MAX([Price], [Cost]) > 100;

Keyword Operators

Some Functions can also be written as a word between their two values, which reads more naturally:

[Currency] IN ('USD', 'EUR') AND [Counterparty] NOT_CONTAINS 'Test'

3. Combining clauses

Boolean Expressions can contain multiple clauses, joined with AND or OR:

 [Price] > 100 AND [Currency] = 'USD'

 [Price] > 100 OR [Currency] = 'USD'

There is no limit on the number of clauses.

Use parentheses when mixing AND and OR — they are sometimes required, and always easier to read:

 ([Price] > 100) AND (([Currency] = 'USD') OR ([ItemCost] < [OrderCost]))

Order of Evaluation

Without parentheses, operators are applied in this order (highest first):

So AND is applied before OR:

 [Price] > 100 OR [Currency] = 'EUR' AND [Status] = 'Open'
// is the same as
 [Price] > 100 OR ([Currency] = 'EUR' AND [Status] = 'Open')

Choosing Between Values

CASE returns a different value depending on which condition is true - ideal for Calculated Columns:

 CASE WHEN [Price] > 100 THEN 'High' WHEN [Price] > 10 THEN 'Medium' ELSE 'Low' END

It can also match a single value against a list of options:

 CASE [Currency] WHEN 'USD' THEN 'Dollar' WHEN 'EUR' THEN 'Euro' ELSE 'Other' END

4. Input values

Input values are literals you type — they are not read from the grid:

[Price] > 100;

[Currency] = 'USD';

Each type of value is written as follows:

5. WHERE

WHERE adds a Standard (Boolean) Expression to an Aggregation or Observable Expression.

Only rows that match the clause take part in the aggregation or the observation.

It can go in 2 places:

• After the Expression - it applies to every Aggregation in the Expression
• Inside the braces - it applies only to that one Aggregation (or Observable Function)

 SUM{[PnL]} > '5M' WHERE [Country] IN ('US', 'UK')

// 2 / 1 / 4 — the main Aggregation Expression
// 5 — `WHERE`
// 1 / 4 — the additional (standard Boolean) clause (same colours as everywhere else)

The clause can be as rich as any other Boolean Expression:

 SUM{[PnL]} > '5M' WHERE [Currency] = 'USD' AND [RequiredDate] < ADD_DAYS(CURRENT_DAY(), 30)

Placed inside the braces, each Aggregation can have its own filter:

 SUM{[Notional] WHERE [Side] = 'Buy'} - SUM{[Notional] WHERE [Side] = 'Sell'}

Common Mistakes

The Expression Editor flags these as you type (often with a one-click fix), but they are worth knowing when writing Expressions by hand:

Return Value

Each Module decides whether the Expression must return boolean or a value of any type: