Standard Expressions

A Standard Expression is evaluated against each row in isolation and returns a single value.

The return value can be (conceptually) divided in 2 groups:

• scalar (i.e. any data type)
• boolean (ie. true / false) - returns true for each row which passes all conditions in the Expression

Standard Expressions are used in these Modules:

Elements in Standard Expressions

Expressions can be very complex but essentially they are made of up 4 distinct elements:

1. Columns (or Fields)
2. Expression Functions (and Operators)
3. AND / OR Logical Operators
4. Input Values

In this page we will analyse each of these 4 Elements in turn, marking each type of Element with the relevant number when providing code example.

Let's start with a small Example which shows all 4 elements of an Expression.

Imagine we want a Grid Filter so that AdapTable only displays rows where either of 2 clauses is true:

• whichever value was the smaller in the BloombergBid and MarkitBid columns, was greater than 50
• the Currency column has a value of Dollar

That could be written with the following (boolean) Expression:

 MIN([BloombergBid], [MarkitBid]) > 50 OR [Currency] = 'USD'

Now lets at look of each of these 4 Elements in turn.

1. Columns

This refers to any Adaptable Column whose value is required in the Expression.

When evaluating the Expression, AdapTableQL automatically retrieves the value for the column for each row.

Columns are typically referenced by providing the columnId in square brackets:

 // Reference Price Column (in Scalar Expression)
 [Price] * 100

 // Reference Currency Column (in Boolean Expression)
 [Currency] = 'USD'

There is no limit to the amount of Columns that can be included in an Expression:

 // Multiply 3 columns together
 MUL([OrderCost] , [ItemCost], [PackageCost])

Columns can be used in Scalar Expressions:

 // Order Change (minus combined total of PackageCost and OrderCost and Invoiced Cost)
 [OrderChange] - ([PackageCost] + [OrderCost] + [InvoicedCost])

Or in Boolean Expressions:

 // Order Change (minus combined total of PackageCost and OrderCost) differs to Invoiced Cost
 [OrderChange] - ([PackageCost] + [OrderCost]) != [InvoicedCost]

Using COL Function

Columns are actually specialised Expression Functions made more readable for user convenience.

Therefore they can alternatively be referenced using the COL function with the Column name in parentheses:

 // Reference Price Column using COL keyword
 COL("Price") * 100

 // Reference Currency Column using COL keyword
 COL("Currency") = 'USD'

Using Column Friendly Names

By default, wherever an Expression is referenced in AdapTable but is not being directly edited - e.g. when displayed in the Settings Panel - the Column's Header is always used.

// Always use the ColumnId property value when providing details of the Expression
const adaptableOptions: AdaptableOptions = {
  expressionOptions:{
    displayColumnFriendlyNamesForExpressions: false
  }
}

Fields

It is possible, if required, to reference a Field instead of a Column.

A Field is a row data item which is present in the data source, but which is not also a Column.

 // use FIELD keyword to reference Price item in data source, even though its not a Column
 FIELD('Price') * 100

2. Expression Functions

At the core of each Expression are Expression Functions.

These are useful functions that are shipped with AdapTableQL which cover a multitude of use cases.

Almost every Expression includes an Expression Function - though sometimes, as we will see below - they can be look like simple Operators.

There is no limit on the number of Expression Functions allowed in an Expression.

For example this basic Boolean Expression contains 2 Expression Functions:

(It also contains 2 Columns, the OR Logical Operator, the > Operator and 2 Input Values)

• STARTS_WITH Boolean Expression Function
• MIN Scalar Expression Function

 STARTS_WITH([col1], 'ABC') OR MIN([col2], [col3]) > 100

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

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

 AVG(5, 12, 21)

Typically Function arguments are dynamic, receiving a current grid cell value (via a Column) as seen above:

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

There are many String manipulation functions available:

 // Return the first non-null column value in a list
 COALESCE([bloombergPrice], [indicativePrice], [markitPrice])

 // Concatenate employee's name after changing case
 CONCAT( LOWER([employee_first_name]), UPPER([employee_last_name]) )

And numeric functions:

 // Return highest of 4 columns
 MAX ([ItemCost], [OrderCost], [InvoicedCost], ([PackageCost]*10))

And date functions:

 // Use Day diffing and adding functions
 DIFF_DAYS(CURRENT_DAY(), ADD_DAYS([OrderDate],5) ) > [ChangeLastOrder]

 // Combine Date functions with Ternary Logic
 [ShippedDate] > ADD_DAYS([OrderDate] , 21) ? 'Delayed' : 'On time'

Expression Functions can be combined with Logical Operators:

 // Country contains 'United' OR Currency is Dollar
 CONTAINS([Country], 'United') OR [Currency] = 'USD'

Expression Function arguments can themselves be Expression Functions

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)
• use the CURRENT_DAY function as the 1st argument
• use an input (or Column) value as the 2nd argument

 // Return the Date, 5 days from now
 ADD_DAYS(CURRENT_DAY(), 5)

And to turn this into a Boolean Expression:

 // Is TradeDate in next 5 days
 ADD_DAYS(CURRENT_DAY(), 5) < [TradeDate]

Operator Shortcuts

AdapTable provides a number of of operator shortcuts for the most commonly used Expression Functions.

These can conceptually be divided into 2 groups:

• Boolean operators - typically used to compare the 2 parts of an Expression's clause

  The most commonly boolean operators used are: =, !=, >, >=, < and <=.

  // Use Price Column with GreaterThan Operator
  [Price] > 100

  // Use Currency Column with Equals Operator
  [Currency] = 'USD'

• Scalar operators - typically used to perform maths on 2 (or more) elements in a clause

  The most commonly used scalar operators are: +, -, *, /, and ^.

  // PackageCost plus OrderCost
  [PackageCost] + [OrderCost]

  // OrderChange minus PackageCost 
  [OrderChange] - [PackageCost]

Because operators are convenient wrappers around Expression Functions, the 4 Expressions above can be rewritten using Functions in place of the operator:

 GT([Price], 100)

 EQ([Currency], 'USD')

 ADD([PackageCost], [OrderCost])

 SUB([OrderChange], [PackageCost])

Expression Functions can therefore be combined with Operators if required.

 // Return any row where the Greater of Price and Cost is over 100
 MAX([Price], [Cost]) > 100

Advanced Expression Functions

AdapTable provides some Expression Functions which are designed to be used in more advanced use cases:

• QUERY - enables Named Queries to be referenced in an Expression:

   // Reference the "Big Orders" Named Query
   QUERY("Big Orders")

• VAR - supports custom values in Expressions:

   // Reference the VAR named 'VAT' and pass in the Country column as an argument
   VAR("VAT",[country]) > 1

• IF - enables ternary logic to be used in Expressions:

  // Return 'Big' if more than 100 Comments, otherwise 'Small'
   [Comments] > 100 ? 'Big' : 'Small'

• CASE - faciliates more complex logic in Expressions:

  // If the Day column contains "Saturday" then return "weekend", otherwise return "workday"
    CASE [day] WHEN 'Saturday' THEN 'weekend' ELSE 'workday' END
  
  

3. AND / OR Logical Operators

All the examples so far have contained one clause to be evaluated by AdapTableQL.

However multiple clauses can be provided - most typically in a Boolean Expression - via logical operators.

There are 2 logical operators available when connecting clauses in Expressions:

• The AND logical operator - requires that both clauses in the Expression are true:

  // Check Price is over 100 AND the Currency is Dollar
   [Price] > 100 AND [Currency] = 'USD'

• The OR logical operator - requires that either clause in the Expression is true:

  // Check Price is over 100 OR the Currency is Dollar
   [Price] > 100 OR [Currency] = 'USD'

Using Parentheses

There is no limit on the number of clauses in an Expression, nor the number of AND or OR functions.

Where there are multiple clauses it is often advisable to provide parentheses.

 // Check Price is over 100 AND either Currency is Dollar or Package Cost is less than Order Cost
 ([Price] > 100) AND (([Currency] = 'USD') OR ([ItemCost] < [OrderCost]))

4. Input Values

Input Values are hard-coded values provided by the User (rather than being dynamically derived by AdapTableQL from a Grid cell when the Expression evaluates).

 // Use Price Column with an Input Value
 [Price] > 100

 // Use Currency Column with an Input Value
 [Currency] = 'USD'

There is no requirement to use a Input value - two Columns can be compared instead:

 // Compare 2 columns (with no Input Value)
 [tradeDate] > [settlementDate]

There is no limit on the number of Input Values which can be used in an Expression:

 // Check Price is over 100 OR the Currency is Dollar
 [Price] > 100 AND ([Currency] = 'USD' OR [PackageCost] < 125)