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.
Expressions can be hand-written or created in the Expression Editor - an advanced UI tool designed for this purpose
There are 4 primary types of Expressions that AdapTableQL can evaluate
|Expression Type||When Used|
|Boolean||When requiring just those rows which match a true/false condition|
|Scalar||When AdapTable needs to provide a single value (of any data type)|
|Observable||When AdapTable needs to watch data changes over time|
|Aggregation||When 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:
|Alerts||Boolean||Triggers an Alert when data change matches a Rule|
|Alerts||Observable||Triggers an Alert based on observed criteria|
|Alerts||Aggregate||Triggers an Alert using aggregation functions|
|Calculated Column||Scalar||Expression is evaluated for each cell in the column|
|Conditional Style||Boolean||Sets whether or not to show the Style|
|Export||Boolean||User Reports can return only those rows returned by the Query|
|Plus Minus||Boolean||Manages evaluation of Custom Nudge Values|
|Query||Boolean||Returns rows which match the true / false condition|
These introduce a Reactive element to AdapTableQL providing the ability to watch data and react to changes (or lack of them).
Expressions can be extremely complex if required, but essentially each consists of 4 elements:
- Functions - e.g. MIN
- Operators - e.g. < (less than)
- Columns - e.g. 'BloombergBid', 'MarkitBid'
- Hard coded values e.g. 50
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:
An Expression will typically contain one of more AdapTableQL Functions that can be understood and evaluated by AdapTableQL.
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.
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:
Function arguments can commonly be a current grid cell value; this is done by using the 'COL' keyword:
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:
The Expression can contain a number of different Operators.
For instance if you want to add 2 column values together use the '+' operator:
Operators are actually specialised functions, so this example can be rewritten using the 'ADD' function:
Most Expressions in AdapTable are likely to reference Columns in the Grid.
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:
Columns are also specialised functions, so they can be referenced by using the 'COL' function:
There is no limit on the number of Columns that can be referenced, e.g.:
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.
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
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.
This Expression can only return a true or false value.
The Boolean is the most common type of Expression used in AdapTable
Example Boolean Expressions
With a single Operator:
Using 2 Operators: IN and AND:
2 Clauses and an OR Operator:
Using both the AND and OR Operators:
Scalar Expressions return a single value of any data type.
These are currently used only in the Calculated Column Module
Example Scalar Expressions
To multiply many columns together:
though this is more readable using the function short syntax as:
To create an Expression which uses ternary (if) logic (and outputs a string):
To return the highest of 4 Columns (very useful for having to meet regulatory requirements like MiFID):
To use Date Manipulation:
or use multiple date functions:
To return the first non-null column value in a list:
To manipulate strings (concatenating 2 strings after converting them to lower case):
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.
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:
This also requires a numeric value, specifying how many changes will trigger the Expression
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'
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:
An Order Cost cell contains its highest value within the last hour:
An Order Cost cell contains its highest value in the last hour - but only for rows where Cust Ref is 'TRADH':
The Price column has not ticked - in any row - for the last 30 seconds:
Aggregation Expressions enable Expressions to be run, as the name suggests, against aggregated data.
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:
Aggregation Expressions are a specialised type of Boolean Expression and must evaluate to true / false.
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
At present only the =, !=, >, <, >=, <= operators are allowed
A Number Value
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?
The same query using the abbreviated number syntax is:
The same query but adding a WHERE clause only to sum those rows where the Currency is Dollar
The same query but adding a WHERE clause with an AND operator:
QUERY AdapTableQL Function
One very useful AdapTableQL Function is
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:
This is itself a Boolean AdapTableQL Function and is used to narrow down the scope of the 'main' part of the Expression.
When using the Expression Editor clicking the 'WHERE' checkbox will cause a 2nd text area to display where the clause can be written
One area where this has been used with great success is for Limits Management
This is done via the
moduleExpressionFunctions property of Query Language Options.
When manipulating Expression Functions, ensure to import
AdaptableScalarExpressionFunctions (as required) from the same location as Adaptable
Custom AdapTableQL Functions
Expression Function Object
An instance of an ExpressionFunction object needs to be provided. This defined as follows:
|description||What the AdapTableQL Function does|
|examples||Examples that use the AdapTableQL Function|
|handler||Actual AdapTableQL Function called by the Expression (mandatory prop)|
|hasEagerEvaluation||Whether Expression is evaluated eagerly (without handling inner AST nodes)|
|isHiddenFromMenu||Removes entry from Expression Editor's Functions dropdown|
|isPredicate||Whether the AdapTableQL Function returns true, if so can be used as Query|
|signatures||How the AdapTableQL Function should be called|
There are a few things to note here:
handleris the only mandatory property (see more below)
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
isPredicateproperty should be set to true
This will allow the Expression to be used as a Query which only works with predicates
Expression Function Handler
It takes 2 arguments and returns any value. The 2 arguments are:
args - whichever arguments, if any, the handler requires in order to evaluate.
This example shows 2 Custom Expression Functions (both of which have all properties set for full UI support):
- LATEST_PRICE - uses the
Contextto evaluate whether to call an internal service
- IN_BUSINESS_YEAR - of type
isPredicateso can be used in Boolean Expressions
If manipulating Expression Functions make sure to import from AdaptableExpressionFunctions from same location as Adaptable is imported
Removing AdapTableQL Functions
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
If not provided this value defaults to the
per Module: They are unavailable for a given, named, Module (via
Make sure to import AdaptableExpressionFunctions from same location as Adaptable is imported
Additionally, it is possible to specify explicitly which columns are queryable via the
queryableColumns property of Query Language Options
queryableColumns property in Query Language Options stipulates which Columns can be used in AdapTableQL - useful if wanting to reduce query complexity.
This is often used in conjunction with Server Searching
Leave unset to include all columns, provide empty array to provide none
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.
This is helpful if running Server Searching or managing Expressions independently
By default text comparisons in Expressions are case insensitive.
Case sensitivity can be applied by setting
caseSensitiveTextComparisons to false in General Options.
This will affect Predicates as well as Expressions
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.
|ADD (or +)||Sum of inputted numbers|
|SUB (or -)||Subtraction of 2nd number from 1st number|
|MUL (or *)||Product of inputted numbers|
|DIV (or /)||Division of inputted numbers|
|MOD (or %)||Modulo of 2 numbers|
|POW (or ^)||Pow of 2 numbers|
|MIN||The smallest of inputted numbers|
|MAX||The highest of inputted numbers|
|AVG||The average of inputed numbers|
|DATE||A new Date using input value|
|NOW||The current Date|
|TODAY||The current Day|
|DAY||The Day (from a Date)|
|WEEK||The Week (from a Date)|
|MONTH||The Month (from a Date)|
|YEAR||The Year (from a Date)|
|ADD_DAYS||Date based on input data and days to add|
|ADD_WEEKS||Date based on input data and weeks to add|
|ADD_MONTHS||Date based on input data and months to add|
|ADD_YEARS||Date based on input data and years to add|
|DIFF_DAYS||Difference in Days between 2 Dates|
|DIFF_WEEKS||Difference in Weeks between 2 Dates|
|DIFF_MONTHS||Difference in Months between 2 Dates|
|DIFF_YEARS||Difference in Years between 2 Dates|
|SUB_STRING||New string extracted from existing string|
|REPLACE||String with matching characters replaced|
|COALESCE||First input value which is not null|
|LEN||Number of characters in a string|
|UPPER||Input string to Upper Case|
|LOWER||Input string to Lower Case|
|CONCAT||Concatenation of input strings|
|Function||Example||Returns True If|
|EQ (or =)||All inputs are equal|
|NEQ (or !=)||All inputs are NOT equal|
|GT (or >)||The1st input > 2nd input|
|LT (or <)||The1st input < 2nd input|
|GTE (or >=)||1st input >= 2nd input|
|LTE (or <=)||1st input <=> 2nd input|
|AND||Both statements are true|
|OR||Either statement is true|
|NOT||Negation of a statement is true|
|BETWEEN||1st input between 2nd and 3rd inputs|
|IN||Any input value is in inputted column|
|IS_BLANK||Input value is empty|
|CONTAINS||1st input contains 2nd input|
|STARTS_WITH||1st input starts with 2nd input|
|ENDS_WITH||1st input ends with 2nd input|
|ANY_CONTAINS||Any column contains input|
|QUERY||The Named Query returns true|
|Function||Example||Watches for Changes|
|ROW_CHANGE||Each row in given timeframe|
|GRID_CHANGE||Whole Grid in given timeframe|
|SUM||Sum of the (Numeric) column (filtered by WHERE clause)|
The following demos contain Expressions: