Calculated Column Module

The Calculated Column Module enables the creation of special 'user-defined' columns which contain a custom Scalar Expression which is evaluated at design-time per row.

tip

Calculated Columns can either be provided at design-time (through Calculated Column Predefined Config) or at run-time through the UI (if Entitlements allow).

Unlike 'regular' columns, Calculated Columns do not display data from the underlying grid's Data Source.

Calculated Columns can either be static (i.e. show a single value) or dynamic (i.e. based on other columns in the row).

important

Once created a Calculated Column is treated like any other Column in the Grid

caution

Only the Calculated Column Name and Expression are stored in AdapTable State and not any cell data

There are numerous potential uses for Calculated Columns including showing Historical Data, and allowing you to do 'what if' or scenario analysis by copying some columns and see what happens if you change their values.

Calculated Column Expression

Calculated Columns include an Expression that users provide in the Expression Editor.

This is a Scalar Expression which is evaluated using AdapTableQL and can return any value.

tip

The Expression can contain multiple functions and refer to other columns in the grid as needed.

The Expression in the Calculated Column updates in real time as values in referenced columns change.

important

The Expression can itself return to other Calculated Columns if required

See Expression to learn morea about AdapTableQL, expression syntax, examples and the available functions.

And view some live examples in the Calculated Column Demo.

Calculated Column Settings

When creating a Calculated Column there are a number of properties you can provide so that the column will operate according to your requirements. These include:

  • Column Id - how the Calculated Column is referred to in other objects (e.g. Layouts, Conditional Styles)

  • Friendly Name - how the Calculated Column is referred to in the UI (and in the column's header)

  • Column Type - DataType of the Expression's return value: can be 'String', 'Number', 'Boolean' or 'Date'

    tip

    When creating a Calculated Column in the UI, AdapTable will infer this from the Scalar Expression and your grid data but you can override this if the assumption is incorrect

  • Width

    note

    If left empty, the width will be set by the underlying grid based on whatever other properties are set

  • Filterable

  • Resizable

  • Groupable

  • Sortable

  • Pivotable

  • Aggregatable

UI Elements

Calculated Column includes the following UI Elements:

  • Popup - Shows a list of existing Calculated Columns with Edit and Delete buttons. Plus an Add button to start the Calculated Column Wizard, that contains a series of steps facilitating the creation and editing of Calculated Columns.

  • Column Menu - Edit Calculated Column Menu Item opens the Calculated Column wizard (only visible if that column is a Calculated Column).

UI Entitlements

UI Entitlements behaviour is as expected for Full and Hidden Access Levels.

The ReadOnly Entitlement behaviour is that Calculated Columns can be added / removed from Layouts and will be applied by AdapTable when visible, but Users cannot manage or suspend them.

Calculated Column Predefined Config

Calculated Column Predefined Config contains a collection of Calculated Columns.

Calculated Column Object

Each Calculated Column is defined as follows:

PropertyDescription
CalculatedColumnSettingsAdditional optional properties for Column (e.g. filterable, resizable)
ColumnIdName of Calculated Column
FriendlyNameName to be used in Column Header; if blank ColumnId is used
QueryScalar Expression used by AdapTableQL to evaluate Column's value

Calculated Column Settings

Calculated Column Settings describes the created column and contains these properties:

PropertyDescriptionDefault
DataTypeExpression's return value DataType; inferred by AdapTable but settable by User
ShowToolTipShow underlying Expression as ToolTip when hovering over a cellfalse

Config Example

const demoConfig: PredefinedConfig = {
CalculatedColumn: {
CalculatedColumns: [
{
Query: {
ScalarExpression: '[ItemCost] / [ItemCount]',
},
ColumnId: 'AvgCost',
FriendlyName: 'Avg Item Cost',
},
{
Query: {
ScalarExpression: '([AvgCost] * [ItemCount])- [PackageCost]',
},
ColumnId: 'Profit',
FriendlyName: 'profit',
},
{
Query: {
ScalarExpression: '[ItemCost] > 100 ? "High" : [ItemCost] > 50 ? "Medium": "Low"',
},
ColumnId: 'Comment',
FriendlyName: 'Comment',
CalculatedColumnSettings: {
DataType: 'String',
Filterable: true,
Groupable: true,
Sortable: true,
},
},
{
Query: {
ScalarExpression: 'max([ItemCost], [OrderCost], ([PackageCost]*10))',
},
ColumnId: 'HighCost',
FriendlyName: 'Highest Cost',
},
{
Query: {
ScalarExpression: "[ShippedDate] > ADD_DAYS([OrderDate] , 21) ? 'Late' : 'On time'",
},
ColumnId: 'ShipDelay',
FriendlyName: 'Ship Delay',
},
],
},
} as PredefinedConfig;

Calculated Column API

This contains a number of Calculated Column related functions:

MethodDescription
addCalculatedColumn(calcColumn)Adds new Calculated Column
deleteCalculatedColumn(calcColumn)Removes Calculated Column from State
editCalculatedColumn(calcColumn)Edits given Calculated Column
editCalculatedColumnExpression(calcColumn, columnExpression)Replaces Expression in given Calculated Column with new value
editCalculatedColumns(calcColumns)Edits given Calculated Columns
getAllCalculatedColumn()Retrieves all Calculated Columns in Adaptable State
getCalculatedColumnById(id)Retrieves Calculated column by id
getCalculatedColumnForColumnId(columnId)Gets Calculated Column, if any, for given ColumnId
getCalculatedColumnModuleReferences(calculatedColumn)Returns all the references in the AdaptableState of the given CalculatedColumn
getCalculatedColumnsReferencingColumnId(columnId)Returns all Calculated Columns whose Expression contains the ColumnId
getCalculatedColumnState()Retrieves Calculated Column section from Adaptable State
getReferencedColumnIdsForCalculatedColumn(calculatedColumn)Gets any ColumnIds referenced in a Calculated Column
getReferencedColumnIdsForCalculatedColumnId(calculatedColumnId)Gets any ColumnIds referenced in a Calculated Column
showCalculatedColumnPopup()Opens Calculated Column popup screen

FAQ

Which mathematical operators can we use to build the Expression?

There are a huge number of AdapTableQL Functions available when writing an Expression and more are being added all the time.

Can we add our own Expression Function and reference it when creating the Calculated Column?

Yes, you can provide Custom Expression Functions to AdapTableQL which will then be available for your users to add to their own Expressions.

Can I include more than one operator in an Expression?

Yes, you can include as many operators as you wish in an Expression.

Can I use the Calculated Column in other Queries?

Yes, once the Calculated Column is created, then its treated the same as any other column and can be referenced other Modules (e.g. Conditional Style or Format Column).

Can I edit the value a Calculated Column directly?

No, a Calculated Column is readonly. You can edit the Expression but you cannot edit one of the cells in the column itself.

Can I create a Calculated Column that returns a string?

Yes, your Calculated Column contains a Scalar Expression so it can can return a number, string, date or boolean.

AdapTable works out the datatype of the Calculated Column automatically, so that the created Column is treated like any other Column in queries and Expressions.

Can I change the name of a Calculated Column?

You can change the FriendlyName of a Calculated Column but, once created, you cannot change the Id (as this could be used in other objects like Layouts).

caution

AdapTable will try to prevent you from deleting a Calculated Column which is referenced elsewhere

More Information