Adaptable Query

The Adaptable Query is used in multiple places where grid data needs to be searched, provided, watched or evaluated in some way.

At its heart each Query contains an Expression which is evaluated using the Adaptable Parser - a powerful, custom-built tool developed by the Adaptable Tools team.

note

Queries can be written by hand or via the Expression Editor - an advanced UI tool designed for this purpose

Expression Types

There are 4 primary types of Expressions that the Parser 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

Queries 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

Expression Syntax

Expressions can be extremely complex if required, but esseentially 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

Functions

An Expression will typically contain one of more functions that can be understood and evaluated by the Parser.

caution

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

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

note

Full lists are provided in the Appendix; if a function is missing, please email support@adaptabletools.com

tip

If you want to reduce the number of functions available (e.g. you are running the Expression yourself remotely) this can be done - see Removing Expression functions for more details.

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( [Price] ), TIMEFRAME('30s') )

Aggregation Expressions

Aggregation Expressions enable Queries 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 parser 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 Function

One very useful parser function is QUERY.

This receives a (boolean) Named Query which the parser 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 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 Expression 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;

Removing Expression Functions

The expressionFunctions property of Search Options allows developers to set which of the shipped Adaptable Expression Functions should be available in the Expression Editor and valid in the Parser.

note

This is typically only relevant if the Expression is evaluated by the user remotely on the server and not by AdapTable - see Server Functionality Guide for more information

warning

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

import Adaptable, { AdaptableExpressionFunctions } from '../../../../agGrid';
const adaptableOptions: AdaptableOptions = {
// Remove 3 unsupported functions, so unavailable in Editor or valid in Query
searchOptions: {
expressionFunctions: {
...omit(AdaptableExpressionFunctions, ['COALESCE', 'DIFF_YEARS', 'IS_BLANK']),
},
}
}
tip

Additionally, it is possible to specify explicitly which columns are queryable via the QueryableColumns property of Query State

Custom Expression Functions

AdapTable allows developers to add their own ExpressionFunctions which will be included in the Expression UI and invoked by the Parser when the Expression is being evaluated.

This is done through the expressionFunctions property of Search Options.

Expression Function Object

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

PropertyDescription
descriptionWhat the function does
examplesExamples that use the function
handlerActual function called by the Expression (mandatory prop)
hasEagerEvaluationIf TRUE, the expression is evaluated eagerly, without handling the inner AST nodes
isHiddenFromMenuRemoves entry from Expression Editor Function dropdown
isPredicateWhether function returns true - it so, then can be used as Query
signaturesHow 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

Custom Expression Example

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 Queries
warning

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

import Adaptable, { AdaptableExpressionFunctions } from '../../../../agGrid';
const adaptableOptions: AdaptableOptions = {
searchOptions: {
expressionFunctions: {
...AdaptableExpressionFunctions,
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])'],
},
IN_BUSINESS_YEAR: {
handler(args) {
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])'],
},
},
},
}

Appendix: Expression Functions

These tables list all the functions shipped by Adaptable for use in the Parser.

We have divided them according to the return type of the Function.

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 scalar Functions 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 In
ROW_CHANGEROW_CHANGE(MAX([Price]), TIMEFRAME('1h'))Each row in specified timeframe
GRID_CHANGEGRID_CHANGE([Price] ), TIMEFRAME('30s')) Whole Grid in specified timeframe

Aggregation Functions

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

More Information

The following demos contain Expressions: