Expressions

An Expression is used in multiple places in AdapTable where data needs to be searched, provided, watched or evaluated.

Each Expression is evaluated using AdapTableQL - a powerful, custom-built Query Language developed by the Adaptable Tools team.

note

Expressions can be hand-written or created in the Expression Editor - an advanced UI tool designed for this purpose

Expression Types

There are 4 primary types of Expressions that AdapTableQL can evaluate

Expression TypeWhen Used
BooleanWhen requiring just those rows which match a true/false condition
ScalarWhen AdapTable needs to provide a single value (of any data type)
ObservableWhen AdapTable needs to watch data changes over time
AggregationWhen the Expression requires to be run over a particular set of data

Modules With Expressions

Expressions are widely used across AdapTable, including in the following Modules:

ModuleReturnsUsage
AlertsBooleanTriggers an Alert when data change matches a Rule
AlertsObservableTriggers an Alert based on observed criteria
AlertsAggregateTriggers an Alert using aggregation functions
Calculated ColumnScalarExpression is evaluated for each cell in the column
Conditional StyleBooleanSets whether or not to show the Style
ExportBooleanUser Reports can return only those rows returned by the Query
Plus MinusBooleanManages evaluation of Custom Nudge Values
QueryBooleanReturns rows which match the true / false condition

AdapTableRX

Version 9 of Adaptable introduced AdapTableRX (short for Reactive Extensions) which use the RX paradigm.

These introduce a Reactive element to AdapTableQL providing the ability to watch data and react to changes (or lack of them).

Expression Syntax

Expressions can be extremely complex if required, but essentially each consists of 4 elements:

important

An Expression can include as many functions, operators, columns and values as are required

So if we wanted to run a Query to return only rows where, whichever value was the smaller in the 'BloombergBid' and 'MarkitBid' columns, was less than 50 we would do:

MIN([BloombergBid], [MarkitBid]) < 50

AdapTableQL Functions

An Expression will typically contain one of more AdapTableQL Functions that can be understood and evaluated by AdapTableQL.

caution

There are some AdapTableQL Functions that Observable and Aggregation Expressions must contain in order to be valid

AdapTable ships with a very large number of AdapTableQL Functions and more are being added all the time.

note

A full list is below; if an AdapTableQL Function is missing, please email support@adaptabletools.com

tip

See Managing Expression Functions for full details on how to create custom AdapTableQL Functions or remove some of those shipped by AdapTable

Functions are capitalised and some require one or more arguments for evaluation.

For example to return the average from 3 numbers you can do:

AVG(5, 12, 21)

Function arguments can commonly be a current grid cell value; this is done by using the 'COL' keyword:

AVG([bloombergBid], [markitBid], [indicativeBid])

Function arguments can themselves be Functions (so long as it returns the correct Data Type).

So to run a (scalar) expression which will return a date 5 days from now you can use the ADD_DAYS function which takes 2 arguments (a date and a number) and then use the TODAY function as the first argument:

ADD_DAYS(TODAY(), 5)

Operators

The Expression can contain a number of different Operators.

For instance if you want to add 2 column values together use the '+' operator:

[ItemCount] + [PackageCost]
note

Operators are actually specialised functions, so this example can be rewritten using the 'ADD' function:

ADD([ItemCount], [PackageCost])

Columns

Most Expressions in AdapTable are likely to reference Columns in the Grid.

note

Some Observable and Aggregation Expression functions require a Column in order to be valid

Columns are typically referenced by placing the columnId (the field name) in square brackets:

[Bid] * 2
note

Columns are also specialised functions, so they can be referenced by using the 'COL' function:

Col("Bid") * 2

There is no limit on the number of Columns that can be referenced, e.g.:

[InvoicedCost] - ( [OrderCost] + [PackageCost])
caution

Expressions use the column's field name, i.e. the identifier for the column used by the underlying grid - e.g. [orderId] rather than the Caption visible in the grid (e.g. 'Order Id'). This guarantees uniqueness.

tip

We provide the Column's Caption in the Expression Editor to help you identify the column more easily together with an option to see the field value instead

Expression Examples

There are limitless Expressions you can create but here a few examples to show you what is possible.

For ease of use we have divided them by Expression return type.

Boolean Expressions

This Expression can only return a true or false value.

note

The Boolean is the most common type of Expression used in AdapTable

For instance the Query Module will evaluate each row and only display those which pass, while Conditional Style will only style the rows / columns where the Expression returns true.

Example Boolean Expressions

With a single Operator:

[Employee] = ("Robert King")

Using 2 Operators: IN and AND:

[Employee] IN ("Robert King", "Andrew Fuller") AND [OrderCost] > 1000

2 Clauses and an OR Operator:

[OrderChange] > 10 OR [PackageCost] < 20

Using both the AND and OR Operators:

[ItemCost] > 30 AND ([OrderChange] > 10 OR [PackageCost] < 20)

Using parantheses:

[OrderChange] - ([PackageCost] + [OrderCost]) != [InvoicedCost]

Scalar Expressions

Scalar Expressions return a single value of any data type.

note

These are currently used only in the Calculated Column Module

Example Scalar Expressions

To multiply many columns together:

MUL([OrderCost] , [ItemCost], [PackageCost])

though this is more readable using the function short syntax as:

[OrderCost] * [ItemCost] * [PackageCost]

To create an Expression which uses ternary (if) logic (and outputs a string):

[ItemCost] > 100 ? "High" : [ItemCost] > 50 ? "Medium": "Low"

To return the highest of 4 Columns (very useful for having to meet regulatory requirements like MiFID):

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

To use Date Manipulation:

[ShippedDate] > ADD_DAYS([OrderDate] , 21) ? 'Delayed' : 'On time'

or use multiple date functions:

DIFF_DAYS(TODAY(), ADD_DAYS([OrderDate],5) ) > [ChangeLastOrder]

To return the first non-null column value in a list:

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

To manipulate strings (concatenating 2 strings after converting them to lower case):

CONCAT( LOWER([employee_first_name]), LOWER([employee_last_name]) )

Observable Expressions

Observable Expressions use advanced Rx techniques to watch for changes (or lack of changes) in data that match a particular pattern.

When the changes happen, AdapTable will trigger an Alert.

note

Currently only the Alert Module supports Observable Expressions, but future versions of AdapTable will extend this to other Modules

There are 2 functions available in an Observable Query:

  • ROW_CHANGE: listens to changes in each row (or those that match the WHERE clause)
  • GRID_CHANGE: listens to changes in the whole grid (or those that match the WHERE clause)

Both these functions take 2 parameters:

  • A Change function: What needs to happen to trigger the Observable.

    There are 4 available change functions, each of which takes a Column Name as its argument:

    • COUNT

      note

      This also requires a numeric value, specifying how many changes will trigger the Expression

    • MIN

    • MAX

    • NONE

  • Timeframe: Defines the period in which the changes will be observed. The timeframe parameter receives a string which will contain the duration and a letter to represent the type. There are 3 available values:

    • Seconds - e.g '30s'
    • Minutes - e.g. '5m'
    • Hour - e.g. '24h'
tip

The Observable Query can additionally have a WHERE clause which limits the rows which are observed

Example Observable Expressions

Tell me when....

The Item Count value in a Row changes 3 times within a 5 minute timeframe:

ROW_CHANGE( COUNT( [ItemCount], 3) , TIMEFRAME('5m') )

An Order Cost cell contains its highest value within the last hour:

ROW_CHANGE( MAX( [OrderCost] ), TIMEFRAME('1h') )

An Order Cost cell contains its highest value in the last hour - but only for rows where Cust Ref is 'TRADH':

ROW_CHANGE( MAX( [OrderCost] ), TIMEFRAME('1h') ) WHERE [CustomerReference] = 'TRADH'

The Price column has not ticked - in any row - for the last 30 seconds:

GRID_CHANGE( NONE( [Price] ), TIMEFRAME('30s') )

Aggregation Expressions

Aggregation Expressions enable Expressions to be run, as the name suggests, against aggregated data.

tip

This is ideal for Limits Management and related scenarios where you want to check a sum of multiple cell value is not exceeded

An Aggregated Expression is made up of a few elements - which must be provided in the specified order - and it must return true / false:

important

Aggregation Expressions are a specialised type of Boolean Expression and must evaluate to true / false.

  • The SUM Expression function

    note

    Adaptable Rx which introduced Aggregation Expressions contains just SUM, but further aggregation functions will be added in future releases in response to user requests

  • Column name - this is the only parameter for the SUM function

  • An Operator

    caution

    At present only the =, !=, >, <, >=, <= operators are allowed

  • A Number Value

    tip

    Instead of providing a number, you can supply a string containing a number together with string abbreviation (of K: thousands, M: millions or B: billions), e.g. '5K', '10M', '2B'

  • An optional WHERE clause - this will set the rows used in the Query

Example Aggregation Expressions

Is total value of the 'PnL' column in all columns greater than 5 Million?

SUM([PnL]) > 50000000

The same query using the abbreviated number syntax is:

SUM([PnL]) > '5M'

The same query but adding a WHERE clause only to sum those rows where the Currency is Dollar

SUM([PnL]) > '5M' WHERE [Currency] = "USD"

The same query but adding a WHERE clause with an AND operator:

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

QUERY AdapTableQL Function

One very useful AdapTableQL Function is QUERY.

This receives a (boolean) Named Query which AdapTableQL evaluates as if it was a hard-coded value.

In this example we create 2 Named Queries and then reference them in a Conditional Style and a User Report:

const demoConfig: PredefinedConfig = {
Query: {
NamedQueries: [
{
Name: 'Non UP Shipping',
BooleanExpression: '[ShipVia] != "United Package" ',
},
{
Name: 'Big Changed Orders',
BooleanExpression: '[ChangeLastOrder] > 10 AND [PackageCost] > 10',
},
],
},
Export: {
CurrentReport: 'Big Orders',
Reports: [
{
Name: 'Big Orders',
ReportColumnScope: 'AllColumns',
ReportRowScope: 'ExpressionRows',
Query: {
BooleanExpression: 'QUERY("Big Changed Orders") AND [ItemCount] > 3',
},
},
],
},
ConditionalStyle: {
ConditionalStyles: [
{
Scope: {
All: true,
},
Style: {
BackColor: '#FED8B1',
},
Rule: {
BooleanExpression: " QUERY('Non UP Shipping')",
},
},
],
},
} as PredefinedConfig;

WHERE Clause

Observable and Aggregation Expressions both contain an Optional WHERE clause.

This is itself a Boolean AdapTableQL Function and is used to narrow down the scope of the 'main' part of the Expression.

note

When using the Expression Editor clicking the 'WHERE' checkbox will cause a 2nd text area to display where the clause can be written

tip

One area where this has been used with great success is for Limits Management

// Fire Alert if the total of the PnL column exceeds 5M
// but only for rows where the currency is Dollar
const demoConfig: PredefinedConfig = {
Alert: {
AlertDefinitions: [
{
Scope: {All: true},
MessageType: 'Info',
Rule:{
AggregationExpression: 'SUM([PnL]) > 50000000 WHERE [Currency] = "USD"'
}
},
],
},
} as PredefinedConfig;

Managing Expressions

AdapTable allows developers to remove (per Module) if required, which shipped AdapTableQL Functions are available in AdapTableQL, and to provide Custom AdapTableQL Functions.

important

This is done via the moduleExpressionFunctions property of Query Language Options.

Other properties allow you to configure which columns are queryable and to turn off Expression Validation.

caution

When manipulating Expression Functions, ensure to import AdaptableBooleanExpressionFunctions AdaptableScalarExpressionFunctions AdaptableObservableExpressionFunctions AdaptableScalarExpressionFunctions (as required) from the same location as Adaptable

import Adaptable, {
AdaptableBooleanExpressionFunctions,
AdaptableScalarExpressionFunctions,
AdaptableObservableExpressionFunctions,
AdaptableAggregationExpressionFunctions} from '../../../../agGrid';

Custom AdapTableQL Functions

Developers can add their own Functions which will be included in the Expression Editor and invoked by AdapTableQL when the Expression is being evaluated.

Expression Function Object

An instance of an ExpressionFunction object needs to be provided. This defined as follows:

PropertyDescription
descriptionWhat the AdapTableQL Function does
examplesExamples that use the AdapTableQL Function
handlerActual AdapTableQL Function called by the Expression (mandatory prop)
hasEagerEvaluationWhether Expression is evaluated eagerly (without handling inner AST nodes)
isHiddenFromMenuRemoves entry from Expression Editor's Functions dropdown
isPredicateWhether the AdapTableQL Function returns true, if so can be used as Query
signaturesHow the AdapTableQL Function should be called

There are a few things to note here:

  • handler is the only mandatory property (see more below)

    note

    Most other properties are designed to aid the user when accessing the function in the Expression Editor by providing help and examples

  • if the expression is a boolean (i.e. returns true/false) then the isPredicate property should be set to true

    important

    This will allow the Expression to be used as a Query which only works with predicates

Expression Function Handler

The handler property in ExpressionFunction is of type ExpressionFunctionHandler, defined as follows:

export type ExpressionFunctionHandler = (
args: any[],
context: ExpressionContext
) => any;

It takes 2 arguments and returns any value. The 2 arguments are:

  • args - whichever arguments, if any, the handler requires in order to evaluate.

  • context - of type ExpressionContext and includes the current Row Node and the Adaptable Api

This example shows 2 Custom Expression Functions (both of which have all properties set for full UI support):

  • LATEST_PRICE - uses the Context to evaluate whether to call an internal service
  • IN_BUSINESS_YEAR - of type isPredicate so can be used in Boolean Expressions
warning

If manipulating Expression Functions make sure to import from AdaptableExpressionFunctions from same location as Adaptable is imported

import Adaptable, {
AdaptableBooleanExpressionFunctions,
AdaptableScalarExpressionFunctions,
AdaptableObservableExpressionFunctions,
AdaptableAgggregationExpressionFunctions,
} from '../../../../agGrid';
queryLanguageOptions = {
defaultBooleanFunctions: {
...AdaptableBooleanExpressionFunctions,
IN_BUSINESS_YEAR: {
handler(args: any[], context: ExpressionContext) {
console.log('layout', context?.api?.layoutApi.getCurrentLayoutName());
const dateToCheck: Date = args[0]; // normally do type checking here!
const businessYear: Date = new Date(2020, 5, 1);
return dateToCheck > businessYear;
},
isPredicate: true,
description: 'Returns true if Date is in current business year',
signatures: ['IN_BUSINESS_YEAR(dateToCheck: Date)'],
examples: ['IN_BUSINESS_YEAR([tradeDate])'],
},
},
defaultScalarFunctions: {
...AdaptableScalarExpressionFunctions,
LATEST_PRICE: {
handler(args, context) {
const tradeStatus = context.node.data['tradeStatus'];
return tradeStatus == 'active'
? priceService.getLatestPrice(args[0])
: context.node.data['closingPrice'];
},
description: 'Returns latest price for an Instrument from a Server',
signatures: ['LATEST_PRICE(instrument: string)'],
examples: ['LATEST_PRICE([ticker])'],
},
},
};

Removing AdapTableQL Functions

By default all AdapTableQL Functions are available in the Expression Editor and available to AdapTableQL.

However sometimes you might wish to reduce the functions which the user can access (perhaps if running Server Searching).

AdapTableQL Functions can be removed in 2 ways (both via Query Language Options):

  • completely: They will be removed for all Modules - done via the default[Type]Functions property

    note

    If not provided this value defaults to the defaultBooleanFunctions, defaultScalarFunctions, defaultObservableFunctions and defaultAggregationFunctions properties.

  • per Module: They are unavailable for a given, named, Module (via moduleExpressionFunctions property)

caution

Make sure to import AdaptableExpressionFunctions from same location as Adaptable is imported

queryLanguageOptions = {
// Omit 3 Scalar Functions (for all Modules)
defaultScalarFunctions: {
...omit(AdaptableScalarExpressionFunctions, ['ADD', 'MUL', 'DIFF_YEARS']),
},
// Omit 'ROW_CHANGE' from Alert Module (observable functions)
// and omit 'LOWER' and 'UPPER' from Calculated Column (scalar functions)
moduleExpressionFunctions: {
Alert: {
observableFunctions: {
...omit(AdaptableObservableExpressionFunctions, ['ROW_CHANGE']),
},
},
CalculatedColumn: {
scalarFunctions: {
...omit(AdaptableScalarExpressionFunctions, ['LOWER', 'UPPER']),
},
},
},
};
tip

Additionally, it is possible to specify explicitly which columns are queryable via the queryableColumns property of Query Language Options

Queryable Columns

The queryableColumns property in Query Language Options stipulates which Columns can be used in AdapTableQL - useful if wanting to reduce query complexity.

important

This is often used in conjunction with Server Searching

tip

Leave unset to include all columns, provide empty array to provide none

queryLanguageOptions = {
// Only allow 4 columns to be queryable
queryableColumns: ['bid', 'ask', 'price', 'currency'],
};

Validating Expressions

By default all Expressions are validated by AdapTableQL before they are run.

This can be changed by setting performExpressionValidation in Query Langauge Options to false.

note

This is helpful if running Server Searching or managing Expressions independently

queryLanguageOptions = {
// Turn off validation in AdapTableQL
performExpressionValidation: false
};

Case Sensitivity

By default text comparisons in Expressions are case insensitive.

Case sensitivity can be applied by setting caseSensitiveTextComparisons to false in General Options.

caution

This will affect Predicates as well as Expressions

generalOptions = {
caseSensitiveTextComparisons: false
};

FAQ

Why are there no Round or Trunc functions as these are very useful

AdapTable tries to keep the functions pure so they return the full value.

If you want to trunc or round numeric figures for display purposes, you can use the Format Column Module which contains both these options.

Appendix: AdapTableQL Functions

These tables list all the AdapTableQL Functions shipped by Adaptable.

We have divided them according to the function's return type.

note

You can use Scalar and Boolean Functions in ALL 4 types of Expressions listed above (i.e. Boolean, Scalar, Observable and Aggregation).

For instance its likely you will use ScalarExpressionFunctions even in an Expression that returns a Boolean

Scalar Functions

FunctionExampleReturns
ADD (or +)[col1] + 5 or ADD([col1], 5)Sum of inputted numbers
SUB (or -)[col1] - 5 or SUB([col1], 5)Subtraction of 2nd number from 1st number
MUL (or *)[col1] * 5 or MUL([col1], 5)Product of inputted numbers
DIV (or /)[col1] / 5 or DIV([col1], 5)Division of inputted numbers
MOD (or %)[col1] % 5 or MOD([col1], 5)Modulo of 2 numbers
POW (or ^)[col1] ^ 5 or POW([col1], 5)Pow of 2 numbers
MINMIN([col1], 5)The smallest of inputted numbers
MAXMAX([col1], 5)The highest of inputted numbers
AVGAVG([col1], 5)The average of inputed numbers
DATEDATE('20210101')A new Date using input value
NOW[col1] > NOW()The current Date
TODAY[col1] > TODAY()The current Day
DAYDAY([col1]) = DAY(TODAY())The Day (from a Date)
WEEKWEEK([col1]) = WEEK(TODAY())The Week (from a Date)
MONTHMONTH([col1]) = MONTH(TODAY())The Month (from a Date)
YEARYEAR([col1]) = YEAR(TODAY())The Year (from a Date)
ADD_DAYS[col1] < ADD_DAYS(TODAY(), 5)Date based on input data and days to add
ADD_WEEKS[col1] < ADD_WEEKS(TODAY(), 5)Date based on input data and weeks to add
ADD_MONTHS[col1] < ADD_MONTHS(TODAY(), 5)Date based on input data and months to add
ADD_YEARS[col1] < ADD_YEARS(TODAY(), 5)Date based on input data and years to add
DIFF_DAYSDIFF_DAYS([col], TODAY() )Difference in Days between 2 Dates
DIFF_WEEKSDIFF_WEEKS([col], TODAY() )Difference in Weeks between 2 Dates
DIFF_MONTHSDIFF_MONTHS([col], TODAY() ))Difference in Months between 2 Dates
DIFF_YEARSDIFF_YEARS([col], TODAY() )Difference in Years between 2 Dates
SUB_STRINGSUB_STRING([col1],1,5)New string extracted from existing string
REPLACEREPLACE([col1],'GBP','EUR')String with matching characters replaced
COALESCECOALESCE([col1],[col2],[col3])First input value which is not null
LENLEN([col1])Number of characters in a string
UPPERUPPER([col1])Input string to Upper Case
LOWERLOWER([col1])Input string to Lower Case
CONCATCONCAT([col1],[col2],[col3])Concatenation of input strings

Boolean Functions

FunctionExampleReturns True If
EQ (or =)[col1] =5 or EQ([col1], 5)All inputs are equal
NEQ (or !=)[col1] !=5 or NEQ([col1], 5)All inputs are NOT equal
GT (or >)[col1] > 5 or GT([col1], 5)The1st input > 2nd input
LT (or <)[col1] < 5 or LT([col1], 5)The1st input < 2nd input
GTE (or >=)[col1] >= 5 or GTE([col1], 5)1st input >= 2nd input
LTE (or <=)[col1] <= 5 or LTE([col1], 5)1st input <=> 2nd input
AND[col1] > 5 AND [col2] > 10Both statements are true
OR[col1] > 5 OR [col2] > 10Either statement is true
NOT![col1] > 5Negation of a statement is true
BETWEENBETWEEN(5, [col1], [col2])1st input between 2nd and 3rd inputs
IN[col1] IN (5, 10, 17)Any input value is in inputted column
IS_BLANKIS_BLANK([col1])Input value is empty
CONTAINSCONTAINS([col1], 's' )1st input contains 2nd input
STARTS_WITHSTARTS_WITH([col1], 's' )1st input starts with 2nd input
ENDS_WITHENDS_WITH([col1], 's' )1st input ends with 2nd input
ANY_CONTAINSANY_CONTAINS('abc')Any column contains input
QUERYQUERY('my named query')The Named Query returns true

Observable Functions

FunctionExampleWatches for Changes
ROW_CHANGEROW_CHANGE(MAX([Price]), TIMEFRAME('30s'))Each row in given timeframe
GRID_CHANGEGRID_CHANGE(MIN([Price]), TIMEFRAME('1h'))Whole Grid in given timeframe

Aggregation Functions

FunctionExampleReturns
SUMSUM([col1]) > 5Sum of the (Numeric) column (filtered by WHERE clause)

More Information

The following demos contain Expressions: