AdapTable Expression

Version 7 of AdapTable introduced the Adaptable Expression.

This is essentially a function - with potentially multiple inputs - and which returns a single value.

Expressions are used in multiple places in AdapTable but the 2 main areas of use are:

Calculated Columns - essentially wrap an Expression which is evaluated for each cell in the column and can return any kind of value.
Query - when it is run the Expression is evaluated against every row in the underlying grid's data set and only those which match the Expression are displayed.

tip

For this reason, the return type of Query-related Expressions is restricted to a boolean (i.e. true / false).

Adaptable Parser

The Expression is evaluated using a powerful, custom parser developed by the Adaptable Tools team.

This is designed to be very fast and efficient using caches and other techniques to maintain performance.

warning

Unlike the Expression ussed in previous versions of Calculated Column, the parser accepts 'pure' functions and does not deal with value formatting.

Generally there is no need for developers to understand how the parser works, merely to ensure that it is provided with valid queries.

One exception to this is if Server Searching is being performed, for which AdapTable makes the AST it uses for a query available via the Query API.

Expression Editor

Expressions can be most easily created in AdapTable using the Expression Editor.

This has a number of useful features including:

Drag n Drop of Columns into the Editor
Lists all available functions as buttons or in a dropdown so they can be immediately applied in the Editor
Displays the return value of the Expression (using data from the first row of the Grid - though this can be edited by the user to try out different scenarios)
Context sensitive Support / Help for each available function

important

An Expression typically includes one or more operators or functions and will likely reference other columns

note

AdapTable ships with the most common functions and operators that can be used to create an Expression.

Future releases will include more functions, as well as enabling developers to provide their own.

important

An Expression can include as many operators as required: there is no limit on the number of operators or the number of other columns that can be referenced.

Expression Syntax

Columns can be referenced in an Expression in 2 ways (using the 'Bid' column as an example):

Col("Bid")
[Bid]

caution

you have to 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').

tip

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

Once you do that you can then refer to the column simply by referencing it:

[ItemCost] * 2

To reference 2 columns:

[ItemCost] / [ItemCount]

To reference 3 columns:

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

Functions in the Parser

AdapTable ships with a very large number of functions that can be understood by the Parser.

note

We provide a full list in the Appendix below, but more functions are being added all the time. However, if you dont see a function that you require please email support@adaptabletools.com

We provide a 'shortcut' for many of most commonly used functions.

For example instead of using the 'ADD' function as follows:

ADD([ItemCount], [PackageCost])

we provide the '+' sign (also available in the Expression Editor as a button):

[ItemCount] + [PackageCost]

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 into those that return any value (as used by Calculated Column) and those that can only return a Boolean (as used by Query).

Any Return Value 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 in a row (great for things 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])

Boolean Expressions

The Query function has to evaluate each row to see if it passes so it can only use true / false Expressions.

For instance:

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

[OrderChange] > 10 AND [PackageCost] > 10

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

Removing Expression Functions

The expressionFunctions property of Query 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
  queryOptions: {
    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 Query Options.

Expression Function Object

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

Property	Description
description	What the function does
examples	Examples that use the function
handler	Actual function called by the Expression (mandatory prop)
isHiddenFromMenu	Removes entry from Expression Editor Function dropdown
isPredicate	Whether function returns true - it so, then can be used as Query
signatures	How 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 = {
  queryOptions: {
        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

This table lists all the Adaptable Expression functions shipped by Adaptable.

tip

Predicate functions (i.e. return a boolean and can be used as the main part of a Query) are italicised

Function	Example	Returns
ADD (or +)	`[col1] + 5` or `ADD([col1], 5)`	Sum of 2 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 2 numbers
DIV (or /)	`[col1] / 5` or `DIV([col1], 5)`	Division of 2 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
EQ (or =)	`[col1] =5` or `EQ([col1], 5)`	True if the 2 inputs are equal
NEQ (or !=)	`[col1] !=5` or `NEQ([col1], 5)`	True if the 2 inputs are NOT equal
GT (or >)	`[col1] > 5` or `GT([col1], 5)`	True if the 1st input > 2nd input
LT (or <)	`[col1] < 5` or `LT([col1], 5)`	True if the 1st input < 2nd input
GTE (or >=)	`[col1] >= 5` or `GTE([col1], 5)`	True if 1st input >= 2nd input
LTE (or <=)	`[col1] <= 5` or `LTE([col1], 5)`	True if 1st input <=> 2nd input
AND	`[col1] > 5 AND [col2] > 10`	True if both statements are true
OR	`[col1] > 5 OR [col2] > 10`	True if either statement is true
NOT	`![col1] > 5`	The negation of a statement
MIN	`MIN([col1], 5)`	The smallest of inputted numbers
MAX	`MAX([col1], 5)`	The highest of inputted numbers
AVG	`AVG([col1], 5)`	The average of inputed numbers
BETWEEN	`BETWEEN(5, [col1], [col2])`	True if 1st input between 2nd and 3rd inputs
IN	`[col1] IN (5, 10, 17)`	True if any input value is in inputted column
DATE	`DATE('20210101')`	A new Date using input value
NOW	`[col1] > NOW()`	The current Date
TODAY	`[col1] > TODAY()`	The current Day
DAY	`DAY([col1]) = DAY(TODAY())`	The Day (from a Date)
WEEK	`WEEK([col1]) = WEEK(TODAY())`	The Week (from a Date)
MONTH	`MONTH([col1]) = MONTH(TODAY())`	The Month (from a Date)
YEAR	`YEAR([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_DAYS	`DIFF_DAYS([col], TODAY() )`	Difference in Days between 2 Dates
DIFF_WEEKS	`DIFF_WEEKS([col], TODAY() )`	Difference in Weeks between 2 Dates
DIFF_MONTHS	`DIFF_MONTHS([col], TODAY() ))`	Difference in Months between 2 Dates
DIFF_YEARS	`DIFF_YEARS([col], TODAY() )`	Difference in Years between 2 Dates
IS_BLANK	`IS_BLANK([col1])`	True is input value is empty
CONTAINS	`CONTAINS([col1], 's' )`	True if 1st input contains 2nd input
STARTS_WITH	`STARTS_WITH([col1], 's' )`	True if 1st input starts with 2nd input
ENDS_WITH	`ENDS_WITH([col1], 's' )`	True if 1st input ends with 2nd input
SUB_STRING	`SUB_STRING([col1],1,5)`	New string extracted from existing string
REPLACE	`REPLACE([col1],'GBP','EUR')`	String with matching characters replaced
COALESCE	`COALESCE([col1],[col2],[col3])`	First input value which is not null
ANY_CONTAINS	`ANY_CONTAINS('abc')`	True if any column contains input

Further Information

The following demos Use Expressions:

tip

Adaptable Parser#

warning

Expression Editor#

important

note

important

Expression Syntax#

caution

tip

Functions in the Parser#

note

Expression Examples#

Any Return Value Expressions#

Boolean Expressions#

Removing Expression Functions#

note

warning

tip

Custom Expression Functions#

Expression Function Object#

note

important

Expression Function Handler#

Custom Expression Example#

warning

Appendix: Expression Functions#

tip

Further Information#

Adaptable Parser

Expression Editor

Expression Syntax

Functions in the Parser

Expression Examples

Any Return Value Expressions

Boolean Expressions

Removing Expression Functions

Custom Expression Functions

Expression Function Object

Expression Function Handler

Custom Expression Example

Appendix: Expression Functions

Further Information