Calculated Column

The Calculated Column Module enables the creation of special 'user-defined' columns which contain a custom '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 the AdapTable Parser 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

For more information on the Expression syntax, examples and a list of the available functions see Queries.

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).

Entitlements

Calculated Column Entitlement Rules:

  • Full: Everything is available to the User

  • Hidden: Everything is hidden from the User

  • ReadOnly: User can see Calculated Columns defined in Predefined Config but not add, edit or delete them.

Calculated Column State

Calculated Column Predefined Config contains a collection of Calculated Columns.

Calculated Column Object

Each Calculated Column is defined as follows:

PropertyDescription
CalculatedColumnSettingsAddtional 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 AdapTable parser to evaluate Column's value

Calculated Column Settings

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

PropertyDescriptionDefault
AggregatableWhether Column can be used in an aggregation when groupingtrue
DataTypeExpression's return value DataType; inferred by AdapTable but setable by User
FilterableWhether Column is filterabletrue
GroupableWhether Column can be groupedtrue
PivotableWhether Column can be used when grid is in pivot modetrue
ResizableWhether Column can be resized (by dragging column header edges)true
ShowToolTipShow underlying Expression as ToolTip when hovering over a cellfalse
SortableWhether Column is sortabletrue
WidthPreferred width (in pixels) for Column; if unset, calculated dynamically by underlying Grid

Calculated Column 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

Calculated Column API 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
getCalculatedColumnModuleReferences(calculatedColumn)Returns all the references in the AdaptableState of the given CalculatedColumn
getCalculatedColumnState()Retrieves Calculated Column section from Adaptable State
showCalculatedColumnPopup()Opens Calculated Column popup screen

FAQ

Which mathematical operators can we use to build the Expression?

There are a huge number of functions currently available when writing a Query and more are being added all the time.

Can we add our own function and then refrence it in the Expression?

Not at the moment, but that funcionality will be coming soon.

Can I include more than one operator in an Expression?

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

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 in Queries and other Functions (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 can return a number, string, date or boolean. AdapTable will work 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 you cannot change the Id (as this could be used in other objects like Layouts.

warning

AdapTable will prevent you from deleting a Calculated Column that is referenced elsewhere.

More Information