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.
Queries can be written by hand or via the Expression Editor - an advanced UI tool designed for this purpose
There are 4 primary types of Expressions that the Parser 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
Queries 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|
Expressions can be extremely complex if required, but esseentially 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 functions that can be understood and evaluated by the Parser.
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.
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:
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 Queries 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:
One very useful parser function is
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:
This is itself a Boolean Expression 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
Removing Expression Functions
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
If manipulating Expression Functions make sure to import from AdaptableExpressionFunctions from same location as Adaptable
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:
|description||What the function does|
|examples||Examples that use the function|
|handler||Actual function called by the Expression (mandatory prop)|
|hasEagerEvaluation||If TRUE, the expression is evaluated eagerly, without handling the inner AST nodes|
|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:
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.
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
Contextto evaluate whether to call an internal service
- IN_BUSINESS_YEAR - of type
isPredicateso can be used in Queries
If manipulating Expression Functions make sure to import from AdaptableExpressionFunctions from same location as Adaptable
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.
|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 In|
|ROW_CHANGE||Each row in specified timeframe|
|GRID_CHANGE||Whole Grid in specified timeframe|
|SUM||Sum of the (Numeric) column (filtered by WHERE clause)|
The following demos contain Expressions: