Referencing Row Data in Expressions

Summary

AdaptableQL can reference data that is in the data source but is not a Column
This is done via the FIELD expression function
Fields are defined in Expression Options

Expressions typically directly reference data which is stored in an Adaptable Column.

However Expressions can also operate on row data which is not contained in a Column.

Note

The data for the field needs to be in the underlying data source but its not represented by an AG Grid Column

This is done via 2 stage process:

define the Fields in the fields property of Expression Options (so they are available in the UI)
reference the Fields in Expressions by using the FIELD keyword

Defining Fields

Fields are defined by being listed in the fields property of Expression Options.

This means the Fields are now available in the AdapTableQL UI and can be used similarly to Columns:

The Query Builder allows users to select Fields when creating Conditions - similar to Columns
The Expression Editor lists all defined Fields which are draggable into the Editor

Hint

Expressions containing Fields that have not been defined in Expression Options are still perfectly valid
However the Expression cannot be edited in Query Builder

The Fields can be provided either as a list or via a function which returns a list.

fields

Default: null

AdaptableField[]

Non-column row data to be used in AdapTableQL Expressions

AdaptableField[]

This property returns a list of row data fields which can be used in AdapTableQL Expressions even though they are not AG Grid Columns.

fields?: AdaptableField[] | ((context: AdaptableFieldContext) => AdaptableField[]);

The AdaptableField object is defined as follows:

Property	Description
dataType	Data Type of field, used to validate and show correctly in UI
label	Label for field (optional - defaults to name)
name	Name of field in data source, e.g. 'rowId', 'parentObject.childObject.value'

Hint

The label property is optional - if not provided the name property is used

Providing a List

The easiest way to provide fields is via a list:

// Provide 2 fields - one of which is nested
expressionOptions:{
  fields: [
  {
    label: 'Forks',
    name: 'forks_count',
    dataType: 'number',
  },
  {
    label: 'Watchers Multiplier',
    name: 'multipliers.watchers_multiplier',
    dataType: 'number',
  },
]};

Using a Function

Alternatively you can provide a function which will return the list.

The function receives AdaptableFieldContext object which simply wraps BaseContext and is defined as follows:

Property	Description
adaptableApi	Adaptable Api object
adaptableContext	Custom application Context provided in `AdaptableOptions.adaptableContext`
adaptableId	Id of current AdapTable instance
adaptableStateKey	Current Adaptable State Key
clientTimestamp	Time on user's computer
userName	Name of Current User

Referencing Fields

Fields are referenced in Expressions using the FIELD keyword.

This has a single parameter which is the name of the data field:

 FIELD('forks_count')

Note

When using Fields you have to use the FIELD keyword explicitly - the square brackets shortcut is not available

Nested Row Data

If the row data is nested inside another object, you need to use the full reference using the '.' operator.

For instance if a multipliers object contained a watchers_multiplier property, it should be referenced:

 FIELD('multipliers.watchers_multiplier')

Expressions using Row Data

Fork Fork

This example shows how to create AdapTableQL Expressions that reference row data, rather than Columns
We added 3 additional fields to our typical row data: forks_count, stars_multiplier & watchers_multiplier (the latter 2 inside a nested multipliers object)
We then defined those fields in the fields property of Expression Options (so they are available in the UI)
We wrote Expressions that reference this row data using the FIELD property (even though neither are Columns):
- 2 Calculated Columns - Full Stars and Full Watchers that multiply Github Stars and Github Watchers with their respective multiplier values
- A Grid Filter with the simple Expression: FIELD('forks_count') > 5000
We also applied a Custom Sort to Github Stars (that multiplies by stars_multiplier) - which is why the Column doesn't sort numerically

import {AdaptableOptions} from '@adaptabletools/adaptable';
import {IRowNode} from 'ag-grid-enterprise';
import {WebFramework} from './rowData';

export const adaptableOptions: AdaptableOptions<WebFramework> = {
  primaryKey: 'id',
  adaptableId: 'Referencing Row Data',
  expressionOptions: {
    fields: [
      {
        label: 'Forks',
        name: 'forks_count',
        dataType: 'number',
      },
      {
        label: 'Stars Multiplier',
        name: 'multipliers.stars_multiplier',
        dataType: 'number',
      },
      {
        label: 'Watchers Multiplier',
        name: 'multipliers.watchers_multiplier',
        dataType: 'number',
      },
    ],
  },
  customSortOptions: {
    customSortComparers: [
      {
        name: 'Comparer-github_stars',
        scope: {
          ColumnIds: ['github_stars'],
        },
        comparer: (
          valueA: any,
          valueB: any,
          nodeA: IRowNode | undefined,
          nodeB: IRowNode | undefined
        ) => {
          const nodeAnodeAStarsMultiplier =
            nodeA?.data['multipliers.stars_multiplier'] * valueA;
          const nodeBStarsMultiplier =
            nodeB?.data['multipliers.stars_multiplier'] * valueB;
          if (nodeAnodeAStarsMultiplier > nodeBStarsMultiplier) {
            return -1;
          }
          if (nodeAnodeAStarsMultiplier < nodeBStarsMultiplier) {
            return 1;
          }
          return 0;
        },
      },
    ],
  },
  initialState: {
    Dashboard: {
      PinnedToolbars: ['GridFilter'],
      ModuleButtons: ['CalculatedColumn', 'SettingsPanel'],
    },
    Theme: {CurrentTheme: 'dark'},
    Layout: {
      CurrentLayout: 'Standard Layout',
      Layouts: [
        {
          TableColumns: [
            'name',
            'language',
            'github_stars',
            'full_stars',
            'github_watchers',
            'full_watchers',
            'license',
            'open_issues_count',
            'created_at',
            'has_wiki',
            'updated_at',
            'pushed_at',
            'closed_issues_count',
            'open_pr_count',
            'closed_pr_count',
            'has_projects',
            'has_pages',
            'week_issue_change',
          ],
          ColumnSorts: [
            {
              ColumnId: 'github_stars',
              SortOrder: 'Asc',
            },
          ],
          Name: 'Standard Layout',
          GridFilter: {
            Expression: "FIELD('forks_count') > 5000",
          },
          AutoSizeColumns: true,
        },
      ],
    },
    CalculatedColumn: {
      CalculatedColumns: [
        {
          FriendlyName: 'Full Stars',
          ColumnId: 'full_stars',
          Query: {
            ScalarExpression:
              "[github_stars] * FIELD('multipliers.stars_multiplier')",
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Filterable: true,
          },
        },
        {
          FriendlyName: 'Full Watchers',
          ColumnId: 'full_watchers',
          Query: {
            ScalarExpression:
              "[github_watchers] * FIELD('multipliers.watchers_multiplier')",
          },
          CalculatedColumnSettings: {
            DataType: 'number',
            Resizable: true,
            Filterable: true,
          },
        },
      ],
    },
  },
};

PreviousIF & CASE Logic Functions NextReducing Expression Complexity

Framework: