# Standard Custom Expression Functions

Canonical page: https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-standard

- Standard Custom Expression Functions evaluate one row at a time
- Boolean and value-producing functions share the same registration map
- Each function is a flat object: evaluation props plus editor / availability metadata

Standard Custom Expression Functions are [Expression Functions](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions/index.md) which are:

- provided by developers at design-time
- evaluated against a **single row**

Standard Custom Expression Functions are registered in `expressionOptions.customStandardFunctions`.

The function can return any value, e.g. `boolean`, `number`, `string`, `date`, `object`, `any` etc.

## How It Works

1. A developer registers a function on `customStandardFunctions` — at minimum a `handler` and a `returnType`
2. AdapTableQL treats it like any other Standard Function and lists it in the [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md)
3. Functions that return a Boolean and take 1 or 2 arguments will also be available in the [Query Builder](https://www.adaptabletools.com/docs/ui-query-builder/index.md)
4. A run-time user (or Initial State) includes the function in an Expression
5. AdapTableQL invokes the `handler` when it evaluates that Expression and returns the result

Editor-related properties (`description`, `signatures`, `examples`, `category`) are optional but strongly recommended

### Defining a Custom Expression Function

This Guide creates 2 Custom Functions:

- `THIS_BUSINESS_YEAR` — returns a boolean (true or false)
- `USD_CONVERT` — returns a number

Add each function to `expressionOptions.customStandardFunctions`.

It is a Record (or a callback that returns one) with:

- keys: custom function names
- values: can be either a full [`AdaptableCustomExpressionFunction`](https://www.adaptabletools.com/docs/reference/adaptablecustomexpressionfunction.md), or a partial that `extends` another function

By convention the Function name is in CAPITAL LETTERS — it is more readable when used in an Expression.

`handler` is the actual function implementation.

It receives an `args` array and an [`ExpressionContext`](https://www.adaptabletools.com/docs/reference/expressioncontext.md), and returns the function result (boolean, number, or any other value).

`returnType` is **required** on custom Standard functions.

Use a value from `EXPRESSION_RETURN_TYPES` (`'boolean' | 'number' | 'string' | 'date' | 'object' | 'null' | 'any'`)

`operands` is the type / input number matrix.

This drives expression validation.

For booleans, its responsible for if and how it should appear in the Query Builder and Column Filter (not just the Expression Editor).

- `THIS_BUSINESS_YEAR` takes one date (the column)
- `USD_CONVERT` takes a number and a string

These 4 properties are used to provide context support in the [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md):

- `description` — what the Function does
- `signatures` — how it should be called
- `examples` — small Expressions that use it
- `category` — which Functions menu it appears in (e.g. `'date'`, `'numeric'`)

- Omit or use `'custom'` for the Custom menu
- Any other string creates a menu with that name

```ts [[1,3, "customStandardFunctions"], [2,4, "THIS_BUSINESS_YEAR"], [2,16, "USD_CONVERT"], [3,5, "handler"], [3,17, "handler"], [4,9, "returnType"], [4,33, "returnType"], [5,10, "operands"], [5,34, "operands"], [6,11, "description"], [6,12, "signatures"],[6,13, "examples"],[6,14, "category"], [6,35, "description"],[6,36, "signatures"],[6,37, "examples"], [6,41, "category"]]
// Expression Options
expressionOptions: {
  customStandardFunctions: {
    THIS_BUSINESS_YEAR: {
      handler(args: any[], context: ExpressionContext) {
        const startBusinessYear = new Date(2023, 4, 1);
        return new Date(args[0]) > startBusinessYear;
      },
      returnType: 'boolean',
      operands: [['date']],
      description: 'Returns true if Date is in current Business year',
      signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
      examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
      category: 'custom',
    },
    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',
      operands: [['number', 'text']],
      description: 'Converts EUR & GBP Currencies to Dollar',
      signatures: ['USD_CONVERT(val: number, ccy: string)'],
      examples: [
        'USD_CONVERT([value],[currency])',
        'USD_CONVERT([value], "GBP")',
      ],
      category: 'custom',
    },
  },
}
```

## Expression Function Object

Each Standard Custom Function is an [`AdaptableCustomExpressionFunction`](https://www.adaptabletools.com/docs/reference/adaptablecustomexpressionfunction.md): one **flat** object that combines AQL evaluation properties with AdapTable presentation / availability properties.

Ensure the custom function uses `AdaptableCustomExpressionFunction` or `AdaptableCustomExpressionFunctionEntry`

### Authoring properties

| Area                  | Properties                                                                                           | Role                                                                                                                                                         |
| --------------------- | ---------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| Evaluation (required) | `handler`, `returnType`                                                                              | Implementation and result type                                                                                                                               |
| Argument model        | `operands`, `enforceOperandTypes`, `appliesTo`                                                       | Operand types and number, optional static checks, whether a boolean attaches to a column or the row                                                          |
| Editor docs           | `description`, `signatures`, `examples`, `category`                                                  | Expression Editor help and Functions-menu grouping                                                                                                           |
| Presentation          | `label`, `labelsBySubjectType`, `icon`, `iconsBySubjectType`, `shortcuts`, `rhs`, `isHiddenFromMenu` | Operator picker, Quick Filter keystrokes, Query Builder slot editors                                                                                         |
| Availability          | `isAvailable`                                                                                        | Limit which Modules can use the function — see [Custom Expression Function Scope](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-scope/index.md) |
| Override              | `extends`                                                                                            | Inherit metadata from a system (or other custom) function and override only what you need                                                                    |

Toolbar chips are separate: add them with `expressionOptions.expressionEditorButtons`.

- Provide the editor-facing properties even when they are optional
- They are what run-time users see when they pick the function.

### Query Builder and Column Filter

A custom boolean function will also appear in Query Builder and Column Filter if:

- `returnType` is `'boolean'`
- the number of `operands` is **1** (column only) or **2** (column + value)

- Optional `label` / `icon` are used in the operator picker
- `rhs` refines the value editor for the non-subject operand

### Expression Function `handler`

`handler` is the implementation and is of type [`ExpressionFunctionHandler`](https://www.adaptabletools.com/docs/reference/expressionfunctionhandler.md).

Together with `returnType` it is the only required property.

```ts
(args: any[], context: ExpressionContext) => any;
```

- `args` — the function’s arguments, already evaluated (column values, literals, nested results)
- `context` — public [`ExpressionContext`](https://www.adaptabletools.com/docs/reference/expressioncontext.md) (`AdaptableExpressionContext`)

### What to use on context

Use the row accessors, not AG Grid:

- `getRowData()` — the current row’s data
- `getColumnValue(columnId)` — a column value on the current row
- `getRawColumnValue?.(columnId)` — underlying cell value before host normalisation
- `getFieldValue(field)` — a [Field](https://www.adaptabletools.com/docs/adaptable-ql-expression-advanced-row-data/index.md) that is not a Grid column
- `adaptableApi` / `adaptableContext` — from [`BaseContext`](https://www.adaptabletools.com/docs/reference/basecontext.md)

Also present when the calling Module supplies them:

- `columnScope` — the `$SCOPE` column (Column Filter, Badge, …)
- `argNodes` — raw AST for each argument; use `getExpressionArgColumnId` to recover a column id (typical when `extends`-ing a system function)

### Inheriting a system function

A Custom function can inherit from an existing System Function.

In this case, the function is partial and uses `extends` ([`ExpressionFunctionPartialWithExtends`](https://www.adaptabletools.com/docs/reference/expressionfunctionpartialwithextends.md)).

- Unspecified fields are copied from the named system (or already-registered custom) function
- This is useful when overriding a function's handler, while keeping its label, icon and `operands`

```ts {3}
customStandardFunctions: {
  IS_BLANK: {
    extends: 'IS_BLANK',
    handler(args, context) {
      const value = args[0];
      return value == null || value === '';
    },
  },
}
```

**Example: AdapTableQL: Custom Standard Functions**

Using Custom Standard Expression Functions

- This example creates 2 Custom Standard Expression Functions — used in a table showing (random) purchases:
  - `THIS_BUSINESS_YEAR` — boolean (`returnType: 'boolean'`, one date operand) — true if the Date is after 1 April 2023
  - `USD_CONVERT` — value-producing — fictitiously converts EUR and GBP amounts to Dollars
- They are referenced in other Modules:
  - `THIS_BUSINESS_YEAR` in a [Format Column](https://www.adaptabletools.com/docs/handbook-column-formatting/index.md) which puts matching rows in Bold, Italics and a Blue Font
  - `USD_CONVERT` in the `Dollar Price` [Calculated Column](https://www.adaptabletools.com/docs/handbook-calculated-column/index.md)
  - Both functions are referenced in the `Old High Exchange` [Named Query](https://www.adaptabletools.com/docs/handbook-named-queries/index.md)
### Expand to see the Definitions provided

The 2 Custom Expression Functions are defined as follows:
```ts
expressionOptions: {
  customStandardFunctions: {
    THIS_BUSINESS_YEAR: {
      handler(args: any[], context: ExpressionContext) {
        const startBusinessYear = new Date(2023, 4, 1);
        return new Date(args[0]) > startBusinessYear;
      },
      returnType: 'boolean',
      operands: [['date']],
      description: 'Returns true if Date is in current Business year',
      signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
      examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
      category: 'custom',
    },
    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',
      operands: [['number', 'text']],
      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',
    },
  },
}
```
And they are referenced elsewhere:
```ts
NamedQuery: {
  NamedQueries: [
    {
      Name: 'Old High Exchange',
      Expression: 'USD_CONVERT(1, [currency]) > 1.175 AND !THIS_BUSINESS_YEAR([purchased])',
    },
  ],
},
CalculatedColumn: {
  CalculatedColumns: [
    {
      ColumnId: 'dollar_price',
      FriendlyName: 'Dollar Price',
      Expression: 'USD_CONVERT([amount], [currency])',
      CalculatedColumnSettings: {
        DataType: 'number',
      },
    },
  ],
},
FormatColumn: {
  FormatColumns: [
     {
      Style: {
        FontWeight: 'Bold',
        FontStyle: 'Italic',
        ForeColor: 'LightBlue',
      },
      Scope: {
        All: true,
      },
      Rule: 'THIS_BUSINESS_YEAR([purchased])',
    },
  ],
},
```

- Open the [Expression Editor](https://www.adaptabletools.com/docs/ui-expression-editor/index.md) and see the 2 custom Functions in the Custom menu
- Open Column Filter on `purchased` — `THIS_BUSINESS_YEAR` is offered as an operator (boolean + one date operand)
- `USD_CONVERT` stays Expression-Editor-only (it is not a boolean)

Demo project (Vanilla); the files without a marker are shown below:

```text
src/
  adaptableOptions.ts
  agGridModules.ts     [template]
  columnDefs.ts
  gridOptions.ts       [template]
  index.ts             [template]
  onAdaptableReady.ts  [template]
  rowData.ts
  styles.css           [template]
index.html             [template]
package.json           [template]
tsconfig.json          [template]
```

Files marked `[template]` are the same in every demo; see the [demo template](https://www.adaptabletools.com/llms/demo-template.md#vanilla).

**`src/adaptableOptions.ts`**

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

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Custom Expression Functions',
  expressionOptions: {
    customStandardFunctions: {
      THIS_BUSINESS_YEAR: {
        handler(args: any[], context: ExpressionContext) {
          const startBusinessYear = new Date(2023, 4, 1);
          return new Date(args[0]) > startBusinessYear;
        },
        returnType: 'boolean',
        operands: [['date']],
        description: 'Returns true if Date is in current Business year',
        signatures: ['THIS_BUSINESS_YEAR(dateToCheck: Date)'],
        examples: ['THIS_BUSINESS_YEAR([tradeDate])'],
        category: 'custom',
      },
      USD_CONVERT: {
        handler(args: any[], 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',
        operands: [['number', 'text']],
        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'],
    },
    NamedQuery: {
      NamedQueries: [
        {
          Name: 'Old High Exchange',
          Expression:
            'USD_CONVERT(1, [currency]) > 1.175 AND !THIS_BUSINESS_YEAR([purchased])',
        },
      ],
    },
    Theme: {CurrentTheme: 'dark'},
    CalculatedColumn: {
      CalculatedColumns: [
        {
          ColumnId: 'dollar_price',
          FriendlyName: 'Dollar Price',
          Expression: '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: 'THIS_BUSINESS_YEAR([purchased])',
        },
      ],
    },
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          Name: 'Standard Layout',
          TableColumns: [
            'item',
            'amount',
            'currency',
            'purchased',
            'dollar_price',
          ],
          AutoSizeColumns: true,
        },
      ],
    },
  },
};
```

**`src/columnDefs.ts`**

```ts
import {ColDef} from 'ag-grid-enterprise';

export const columnDefs: ColDef[] = [
  {
    headerName: 'id',
    field: 'Id',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },
  {
    headerName: 'Item',
    field: 'item',
    filter: true,
    editable: false,
    sortable: true,
    cellDataType: 'text',
  },
  {
    headerName: 'Amount',
    field: 'amount',
    filter: true,
    editable: true,
    sortable: true,
    cellDataType: 'number',
  },
  {
    headerName: 'Currency',
    field: 'currency',
    filter: true,
    editable: true,
    sortable: true,
    cellDataType: 'text',
  },
  {
    headerName: 'Purchased',
    field: 'purchased',
    filter: true,
    editable: true,
    sortable: true,
    cellDataType: 'date',
  },
];
```

**`src/rowData.ts`**

```ts
export const rowData = [
  {
    id: 1,
    item: 'Washing Machine',
    amount: 500.35,
    currency: 'EUR',
    purchased: new Date(2023, 5, 11),
  },
  {
    id: 2,
    item: 'Football',
    amount: 2.7,
    currency: 'GBP',
    purchased: new Date(2022, 7, 19),
  },
  {
    id: 3,
    item: 'Fridge',
    amount: 385.75,
    currency: 'USD',
    purchased: new Date(2021, 4, 5),
  },
  {
    id: 4,
    item: 'Food',
    amount: 195,
    currency: 'CHF',
    purchased: new Date(2021, 3, 15),
  },
  {
    id: 5,
    item: 'Holiday',
    amount: 1187.5,
    currency: 'GBP',
    purchased: new Date(2019, 8, 7),
  },
  {
    id: 6,
    item: 'Misc',
    amount: 56.23,
    currency: 'EUR',
    purchased: new Date(2023, 4, 5),
  },
  {
    id: 7,
    item: 'Laptop',
    amount: 2500,
    currency: 'EUR',
    purchased: new Date(2022, 8, 3),
  },
  {
    id: 8,
    item: 'Sport Tickets',
    amount: 904,
    currency: 'EUR',
    purchased: new Date(2019, 2, 24),
  },
  {
    id: 9,
    item: 'Clothes',
    amount: 480.25,
    currency: 'USD',
    purchased: new Date(2023, 5, 29),
  },
  {
    id: 10,
    item: 'Concert Ticket',
    amount: 235.45,
    currency: 'USD',
    purchased: new Date(2023, 7, 15),
  },
  {
    id: 11,
    item: 'Rent',
    amount: 652.75,
    currency: 'GBP',
    purchased: new Date(2021, 4, 23),
  },
];
```

## Defining Custom Expression Functions

Custom functions are registered in `expressionOptions.customStandardFunctions`.

To limit which Modules can use a function, set `isAvailable` — see [Custom Expression Function Scope](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-scope/index.md)

### `customStandardFunctions`

Custom Standard Expression Functions available in AdapTableQL

Lists Custom Standard Expression Functions to add to AdapTableQL.

The property is either:

- a Record of function name → [`AdaptableCustomExpressionFunctionEntry`](https://www.adaptabletools.com/docs/reference/adaptablecustomexpressionfunctionentry.md)
- a JavaScript function which returns the same

```ts
  customStandardFunctions?:
    | Record<string, AdaptableCustomExpressionFunctionEntry>
    | ((
        context: GlobalExpressionFunctionsContext<ScalarFunctionName>
      ) => Record<string, AdaptableCustomExpressionFunctionEntry>);
```

The callback receives [`GlobalExpressionFunctionsContext`](https://www.adaptabletools.com/docs/reference/globalexpressionfunctionscontext.md), including `systemExpressionFunctions` if you want to reuse system metadata without `extends`.

### FAQ

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

**Will my boolean function appear in Query Builder and Column Filter?**
Yes, but only if you set `returnType: 'boolean'` and have only 1 or 2 `operands`; otherwise it is Expression-Editor-only.

**Can I limit the function to some Modules?**
Yes — set `isAvailable` on the function (or use `expressionOptions.isExpressionFunctionAvailable`).
See [Custom Expression Function Scope](https://www.adaptabletools.com/docs/adaptable-ql-expression-functions-custom-scope/index.md) for full details.
