Calculated Column
The Calculated Column Function 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.
The Expression can contain any number of functions and references to other columns in the grid as needed.
rmation
Up to Version 7 of AdapTable the Expression used an extermal library (Math.js) but this dependency has been removed and Expressions are now created and evaluated using an internal parser.
The Expression in the Calculated Column updates in real time as values in the columns to which it refers change.
For more information on the Expression including syntax, examples and a list of the available functions see Adaptable Expression.
And see some example Expressions 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 Type
tip
When creating a Calculated Column in the UI, AdapTable will infer this from the Expression and your 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 ColumnMenu 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:
| Property | Description |
|---|---|
| CalculatedColumnSettings | Addtional optional properties for Column (e.g. filterable, resizable) |
| ColumnExpression | Expression which AdapTable parser uses to evaluate Column' display value |
| ColumnId | Name of Calculated Column |
| FriendlyName | Name to be used in Column Header; if blank ColumnId is used |
Calculated Column Settings
Calculated Column Settings describes the created column and contains these properties:
| Property | Description | Default |
|---|---|---|
| Aggregatable | Whether Column can be used in an aggregation when grouping | true |
| DataType | Expression's return value DataType; inferred by AdapTable but setable by User | |
| Filterable | Whether Column is filterable | true |
| Groupable | Whether Column can be grouped | true |
| Pivotable | Whether Column can be used when grid is in pivot mode | true |
| Resizable | Whether Column can be resized (by dragging column header edges) | true |
| ShowToolTip | Show underlying Expression as ToolTip when hovering over a cell | false |
| Sortable | Whether Column is sortable | true |
| Width | Preferred width (in pixels) for Column; if unset is calculated dynamically by underlying Grid |
Calculated Column Config Example
Calculated Column API
Calculated Column API contains a number of Calculated Column related functions:
| Method | Description |
|---|---|
| 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 |
| getAllCalculatedColumn() | Retrieves all Calculated Columns in Adaptable State |
| 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 an Expression 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.
Its quite cumbersome to have to write the Expression by hand. Is there a GUI option?
Yes, this was introduced in Version 7.
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 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.