AdapTable Version:

Framework:

Standard Custom Expression Functions

Summary

Standard Custom Expression Functions are evaluated on a per-row basis
The return type is a single value

Standard Custom Expression Functions are Expression Functions which are:

provided by developers at design-time
operate on a single row a time during evaluation

Although, the return type can be any value, AdapTable divides them into 2 groups for ease of use:

Boolean Expression Functions - return true / false value
Scalare Expression Functions - return any value

How It Works

The process of defining, using and evaluation a Custom Expression Function is as follows:

A developer defines an Expression Function - it includes various properties including a handler function
Hint
- Many of the properties are 'convenience' ones which provide help and examples
- These are designed to aid the user when accessing the function in the Expression Editor
The Expression Function is then treated like any other Function and is included in the Expression Editor
A run-time User includes the Expresssion Function in an Expression (this can be done in Initial State also)
AdapTableQL invokes the handler function when it evaluates the Expression and returns the correct value

Developer Guide

Defining a Custom Expression Function

In this Guide we will create 2 Custom Expressions:

a Boolean Function - THIS_BUSINESS_YEAR - which will return true or false
a Scalar Function - USD_CONVERT - which returns a number

// Expression Options
expressionOptions: {
  customBooleanFunctions: {
     THIS_BUSINESS_YEAR: {
      handler(args: any[], context: ExpressionContext) {
        const startBusinessYear = new Date(2023, 4, 1);
        return new Date(args[0]) > startBusinessYear;
      },
      returnType: 'boolean',
      description: 'Returns true if Date is in current Business year',
      signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
      examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
    },
  },
  customScalarFunctions: {
    USD_CONVERT: {
      handler(args, context: ExpressionContext) {
        const inputValue: number = args[0];
        if (isNaN(inputValue)) {
          return undefined;
        }
        const oldCurrency = args[1];
        if (oldCurrency === 'USD') {
          return inputValue;
        }

        if (oldCurrency === 'EUR') {
          return inputValue * 1.15;
        } else if (oldCurrency === 'GBP') {
          return inputValue * 1.2;
        }
        return undefined;
      },
      returnType: 'number',
      description: 'Converts EUR & GBP Currencies to Dollar',
      signatures: ['USD_CONVERT(val: number, ccy: string)'], 
      examples: [
        'USD_CONVERT([value],[currency])',
        'USD_CONVERT([value], "GBP")',
      ],
    },
   },
}

Add functions to Expression Options

Add the function to relevant section in ExpressionOptions:

boolean functions: customBooleanFunctions
scalar functions: customScalarFunctions

Both types are a Record with 2 properties:

keys: custom function names
values: of type ExpressionFunction

Provide the name of the Function

By convention, the Function name is in CAPITAL LETTERS - to make it more readable when used in an Expression.

Supply the handler property

The handler property is the most important property, providing the actual implementation of the Function.

It receives args array (of type any[]) and context property (type ExpressionContext) and returns a Boolean.

Provide the Return Type

Set the returnType of the function This is particularly important for boolean Expressions

Provide a Description

The Description is designed to help a User accessing the Function in the Expression Editor

It should explain what the Function does

Provide Context Sensitive Help

There are 2 extra properties that can provide context sensitive help to users accessing the Custom Function:

signatures: Shows how the Function should be called
examples: Small examples of Function in action

Expression Function Object

Each Standard Custom Expression function is an instance of an ExpressionFunction object.

Deep Dive

Anatomy of an Expression Function

The full definition of the ExpressionFunction used when creating a Custom Expression Function is as follows:

Property	Description
category	Used to group Functions Expression Editor dropdown
description	What the AdaptableQL Function does
examples	Examples that demonstrate the AdaptableQL Function
handler	Actual AdaptableQL Function called by the Expression (mandatory prop)
hasEagerEvaluation	Whether Expression is evaluated without handling inner AST nodes
inputs	Data Types of Inputs required by Expression
isHiddenFromMenu	Removes entry from Expression Editor's Functions dropdown
returnType	Type returned by Function: boolean, number, string, date, any
signatures	How the AdaptableQL Function should be called

This object contains many properties, including handler which performs the actual evaluation.

Hint

It's advisable to provide the other properties as it greatly helps run-time users accessing the function in an Expression

Expression Function `handler`

As noted above, handler is the key (and only mandatory) property as is performs the actual evaluation.

It takes the form of a function which receives 2 parameters (args and context) and returns any value.

Deep Dive

Understanding the handler property

The handler property in an ExpressionFunction is of type ExpressionFunctionHandler and has the signature:

export type ExpressionFunctionHandler = (args: any[], context: ExpressionContext) => any;

As can be seen, it receives 2 parameters:

args - whichever arguments, if any, the handler requires in order to perform the evaluation
context - an ExpressionContext providing general information about the current node and where and how the Expression is being performed; it's full definition is:

Property	Description
dataChangedEvent	Changed cell data
evaluateCustomQueryVariable	Evaluate custom variables
filterFn	Optional filter function to be applied before evaluating the expression
functions	All Expression Functions available to AdaptableQL
getRowNodes	Get custom list rows
namedQueryCallStack	All Named Query evaluations: tracked in order to detect circular dependencies
node	Current AG Grid Row Node being evaluated
pivotResultColumn	Pivot Result Column when evaluating a Pivoted Column
whereClauseFunctions	Expression Functions available to AdaptableQL in (optional) WHERE clause
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`

Most of these properties will not be of interest when writing a custom expression function.

However the node property is often useful - it is the AG Grid Row Node where the evaluation is taking place.

AdapTableQL: Custom Scalar Functions

Fork Fork

This example creates 2 Custom Scalar Expression Functions - used in a table showing (random) purchases:
- a THIS_BUSINESS_YEAR Custom Boolean Expression Function - returns true if Date is in current Business Year (after 1 April)
- a USD_CONVERT Custom Scalar Expression Function - fictitiously converts USD and GBP prices to Dollars
We then reference each of these Expression Functions in other Modules:
- THIS_BUSINESS_YEAR in a Format Column which puts matching rows in Bold, Italics and a Blue Font
- USD_CONVERT in the Dollar Price Calculated Column
- Both Custom Expressions in the Old High Exchange Named Query

Expand to see the Definitions provided

The 2 Custom Expression Functions are defined as follows:

  expressionOptions: {
    customBooleanFunctions: {
    THIS_BUSINESS_YEAR: {
      handler(args: any[], context: ExpressionContext) {
        const startBusinessYear = new Date(2023, 4, 1);
        return new Date(args[0]) > startBusinessYear;
      },
      returnType: 'boolean',
      description: 'Returns true if Date is in current Business year',
      signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
      examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
      },
    },
    customScalarFunctions: {
      USD_CONVERT: {
        handler(args, context: ExpressionContext) {
          const inputValue: number = args[0];
          if (isNaN(inputValue)) {
            return undefined;
          }
          const oldCurrency = args[1];
          if (oldCurrency === 'USD') {
            return inputValue;
          }

          if (oldCurrency === 'EUR') {
            return inputValue * 1.15;
          } else if (oldCurrency === 'GBP') {
            return inputValue * 1.2;
          }
          return undefined;
        },
        description: 'Converts EUR & GBP Currencies to Dollar',
        signatures: ['USD_CONVERT(val: number, ccy: string)'], 
        examples: [
          'USD_CONVERT([value],[currency])',
          'USD_CONVERT([value], "GBP")',
        ],
      },
  },
},

And they are referenced elsewhere:

NamedQuery: {
  NamedQueries: [
    {
      Name: 'Old High Exchange',
      BooleanExpression: 'USD_CONVERT(1, [currency]) > 1.175 AND !THIS_BUSINESS_YEAR([purchased])',
    },
  ],
},
CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'dollar_price',
      FriendlyName: 'Dollar Price',
      Query: { ScalarExpression: 'USD_CONVERT([amount], [currency])'},
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
  ],
},
FormatColumn: {
  FormatColumns: [
     {
      Style: {
        FontWeight: 'Bold',
        FontStyle: 'Italic',
        ForeColor: 'LightBlue',
      },
      Scope: {
        All: true,
      },
      Rule: {
        BooleanExpression: 'THIS_BUSINESS_YEAR([purchased])',
      },
    },
  ],
},

Try It Out

Open the Expression Editor and see the 2 custom Expression Functions in the Functions Dropdown

import {AdaptableOptions, ExpressionContext} from '@adaptabletools/adaptable';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Custom Expression Functions',
  expressionOptions: {
    customBooleanFunctions: {
      THIS_BUSINESS_YEAR: {
        handler(args: any[], context: ExpressionContext) {
          const startBusinessYear = new Date(2023, 4, 1);
          return new Date(args[0]) > startBusinessYear;
        },
        returnType: 'boolean',
        description: 'Returns true if Date is in current Business year',
        signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
        examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
        category: 'Custom',
      },
    },
    customScalarFunctions: {
      USD_CONVERT: {
        handler(args, context: ExpressionContext) {
          const inputValue: number = args[0];
          if (isNaN(inputValue)) {
            return undefined;
          }
          const oldCurrency = args[1];
          if (oldCurrency === 'USD') {
            return inputValue;
          }

          if (oldCurrency === 'EUR') {
            return inputValue * 1.15;
          } else if (oldCurrency === 'GBP') {
            return inputValue * 1.2;
          }
          return undefined;
        },
        description: 'Converts EUR & GBP Currencies to Dollar',
        signatures: [
          'USD_CONVERT(currentValue: number, currentCurrency: string)',
        ],
        examples: [
          'USD_CONVERT([value],[currency])',
          'USD_CONVERT([value], "GBP")',
        ],
        category: 'Custom',
      },
    },
  },
  editOptions: {
    showSelectCellEditor: context => {
      return context.column.columnId === 'currency';
    },
    customEditColumnValues: context => {
      if (context.column.columnId === 'currency') {
        return [
          {label: 'GBP', value: 'GBP'},
          {label: 'EUR', value: 'EUR'},
          {label: 'USD', value: 'USD'},
          {label: 'CHF', value: 'GCHFBP'},
        ];
      }
      return context.defaultValues;
    },
  },

  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
      ModuleButtons: ['FormatColumn', 'CalculatedColumn', 'SettingsPanel'],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Old High Exchange',
          BooleanExpression:
            'USD_CONVERT(1, [currency]) > 1.175 AND !THIS_BUSINESS_YEAR([purchased])',
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'dollar_price',
          FriendlyName: 'Dollar Price',
          Query: {
            ScalarExpression: 'USD_CONVERT([amount], [currency])',
          },
          CalculatedColumnSettings: {
            DataType: 'number',
          },
        },
      ],
    },
    FormatColumn: {
      FormatColumns: [
        {
          Name: 'formatColumn-dollar_price',
          Scope: {
            ColumnIds: ['dollar_price'],
          },
          DisplayFormat: {
            Formatter: 'NumberFormatter',
            Options: {
              FractionDigits: 2,
              Prefix: '$',
            },
          },
          Style: {
            Alignment: 'Right',
          },
        },
        {
          Name: 'formatColumn-purchased',
          Scope: {
            ColumnIds: ['purchased'],
          },
          DisplayFormat: {
            Formatter: 'DateFormatter',
            Options: {
              Pattern: 'yyyy/MM/dd',
            },
          },
        },
        {
          Name: 'style-all',
          Style: {
            FontWeight: 'Bold',
            FontStyle: 'Italic',
            ForeColor: 'LightBlue',
          },
          Scope: {
            All: true,
          },
          Rule: {
            BooleanExpression: 'THIS_BUSINESS_YEAR([purchased])',
          },
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'item',
            'amount',
            'currency',
            'purchased',
            'dollar_price',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};

Defining Custom Expression Functions

As noted above Custom Expression functions can be of 2 return types:

Boolean - used in Grid Filter, Conditional Styles, Reports etc. and return a true / false value
Scalar - used in Calculated Columns and can have any return type

Boolean Expression Functions

The customBooleanFunctions property is used to define custom Boolean Expression Functions:

customBooleanFunctions

Default: null

Record<string, ExpressionFunction>

Custom Boolean Expression Functions available in AdapTableQL

Property which lists any Custom Boolean Expression Functions to be added to AdapTableQL.

The property returns either:

a Record of type string and Expression Function
a JavaScript function which returns the same

customBooleanFunctions?:
  | Record<string, ExpressionFunction>
  | ((context: GlobalExpressionFunctionsContext<BooleanFunctionName>) => Record<string, ExpressionFunction>);

Scalar Expression Functions

The customScalarFunctions property is used to define Scalar Expression Functions:

customScalarFunctions

Default: null

Record<string, ExpressionFunction>

Custom Scalar Expression Functions available in AdapTableQL

Property which lists any Custom Scalar Expression Functions to be added to AdapTableQL.

The property returns either:

a Record of type string and Expression Function
a JavaScript function which returns the same

  customScalarFunctions?:
    | Record<string, ExpressionFunction>
    | ((
        context: GlobalExpressionFunctionsContext<ScalarFunctionName>
      ) => Record<string, ExpressionFunction>);

FAQ

Does the Custom Expression Function's name have to be capitalised? No, its not mandatory but it is a convention that we encourage, as it makes the Expression more readable.

PreviousCustom Expression Functions NextCustom Aggregated Functions

Framework:

Standard Custom Expression Functions

Summary

How It Works

Hint

Developer Guide

Defining a Custom Expression Function

Expression Function Object

Deep Dive

Anatomy of an Expression Function

Hint

Expression Function handler

Deep Dive

Understanding the handler property

Expand to see the Definitions provided

Defining Custom Expression Functions

Boolean Expression Functions

customBooleanFunctions

Scalar Expression Functions

customScalarFunctions

FAQ

Expression Function `handler`