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.
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).
Once created a Calculated Column is treated like any other Column in the Grid
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 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.
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'
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
If left empty, the width will be set by the underlying grid based on whatever other properties are set
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).
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:
|CalculatedColumnSettings||Addtional optional properties for Column (e.g. filterable, resizable)|
|ColumnId||Name of Calculated Column|
|FriendlyName||Name to be used in Column Header; if blank |
|Query||Scalar Expression used by AdapTable parser to evaluate Column's value|
Calculated Column Settings
Calculated Column Settings describes the created column and contains these properties:
|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, calculated dynamically by underlying Grid|
Calculated Column Config Example
Calculated Column API
Calculated Column API contains a number of Calculated Column related functions:
|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|
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?
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.
AdapTable will prevent you from deleting a Calculated Column that is referenced elsewhere.