Format Column Module

A Format Column is a column that is given specific Style and / or Display Format properties that are always (i.e. unconditionally) rendered.

important

Format Column differs from the Conditional Styles Module because the latter only applies the style when a particular condition is met.

However Format Column will always style the column irrespective of the data contained in the cell.

Format Column Properties

The Format Column Module enables a number of changes to be made to a column. These include:

Style

Users set the Column's style by selecting the fore, back and border colours and various font properties.

note

The style in a Format Column has lowest last level of style-application preference, so any Flashing Alerts, Quick Search highlights or Conditional Styles will be applied ahead of it.

tip

Gradient or Percent Bar styles can be applied to numeric columns, and a Checkbox to Boolean ones

Scope

Scope defines where the style is applied. Options are:

  • one (or more) Columns

  • one (or more) DataTypes (e.g. String, Number, Date etc)

  • an entire Row (Scope of 'All')

Column Styles

Format Column offers complex styles applied in place of the more basic Style.

warning

This is only applicable if the Scope is a single, numeric column

Gradient

The Gradient Numeric Style enables a Column to be styled dynamically so that each cell is coloured according to its value in relation to a minimum and maximum value.

The minimum and values to create the gradient for each cell can come from Ranges or a Column Comparison.

Ranges

A Range defines a minimum and maxium value and colour, so that the cell is coloured using the cell value against the range's boundaries.

There is no limit on how many ranges can be created.

tip

Set ReverseGradient to true in Ranges where you want the cell to be darker the lower the cell value

Column Comparison

As an alternative to using Ranges, the Gradient Numeric Style can be created by comparing each Cell Value to that of another column in the row.

note

This is useful where you might want to show fill the Gradient using the cell Value as a % fill of another column's value.

Percent Bar

The Percent Bar Numeric Style enables numeric columns to be viewed as a 'bar' where each cell is filled - and coloured - as a percentage of its value against the maximum value available for that column.

In other words, the width of the bar is proportional to the value in the column.

As with the Gradient style, a Percent Bar must include either Ranges or a Column Comparison.

Ranges

A Range defines a minimum and maxium value and colour, so that when the cell value falls inside the range, the bar displays the appropriate colour.

This is useful when wanting a traffic light effect - e.g. 0-30: Red; 31-70: Orange; 71-100: Green

There is no limit on how many ranges can be created.

Column Comparison

As an alternative to using Ranges, the Percent Bar Numeric Style can be created by comparing each Cell Value to that of another column in the row.

note

This is useful where you might want to show an Amount as a propertion of a Fill.

Percent Bar Properties

3 other properties are available when creating a Percent Bar Numeric Style:

  • Cell back colour (default is gray)

  • Show cell value as actual value, percentage value or not at all

  • Show a Tooltip providing cell value or percentage value or not at all

note

Percent Bars are like 'normal' columns and can be edited (if the column is editable) and filtered (if the column is filterable) like any other column.

important

These 2 styles were introducted in Adaptable 9 and replace the deprecated Percent Bar and Gradient Column modules

tip

By default Format Columns do not style when the row is grouped, as it rarely makes sense to do so.

To change this behaviour set IncludeGroupedRows to true when defining the Format Column.

Checkbox Column

Checkbox Columns are boolean columns in which AdapTable displays a checkbox.

The checkbox is automatically checked if the cell value is true.

note

If the Column is ReadOnly the checkbox is disabled but the check mark remains visible

All that is required is simply to provide a list of Ids of boolean Columns which should display a checkbox.

const demoConfig: PredefinedConfig = {
FormatColumn: {
FormatColumns: [
{
Scope: {
ColumnIds: ['IsValid', 'IsCompleted'],
},
ColumnStyle: {
CheckBoxStyle: true,
},
},
],
},
} as PredefinedConfig;
tip

AdapTable fires the CheckboxColumnClickedEvent whenever a checkbock is checked / unchecked

Display Format

The Display Format sets how the value in the column will be formatted.

warning

Setting a Display Format does NOT change the underlying cell value.

Different Display Formats are available based on the data type of the Column.

Numeric Columns

Users can use the Number Formatter to set values for:

  • Prefix
  • Suffix
  • Fraction Separator
  • Integer Separator
  • Fraction Digits
  • Integer Digits
  • Multiplier
  • Truncated Digits
  • Negative Parentheses

Numeric Presets

AdapTable provides some preset numeric formatters for common use cases. These include:

  • Percentage
  • K (Thousand)
  • M (Million)
  • Dollar
  • Sterling

String Columns

Users can use the String Formatter to:

  • trim text
  • set to upper case
  • set lower case.

Date Columns

Users can use the Date Formatter to set their own date pattern (or select one of the presets).

note

The full list of available DateTime patterns is here

Date Presets

Similar to numeric columns, AdapTable provides some presets for commonly used Date Formats:

  • MM/dd/yyyy
  • dd-MM-yyyy
  • MMMM do yyyy, h:mm:ss a
  • EEEE
  • MMM do yyyy
  • yyyyMMdd
  • HH:mm:ss

Settings

Cell Alignment

The Format Column can be set so that contents in the cells will align to the left, right or center.

Header Name

Early versions of AdapTable allowed the Column's Header (aka caption) to be set to a new value in Format Column.

This was deprecated in Version 10 in favour of setting the ColumnHeadersMap property in a Layout which offers an improved and more granular way of doing the same thing.

caution

Existing Format Column Config that includes HeaderName will still be applied, but the field is only visible in the Format Column popup if the property has been already set

UI Elements

Format Column includes the following UI Elements:

  • Popup - Shows a list of existing Format Columns with Edit and Delete buttons. Plus an Add button to start the Format Column Wizard which facilitates the creation and editing of Format Columns. Each row in the popup is fully editable.

  • Column Menu - Numeric columns have a Create Format Column Menu Item which starts the Format Column wizard; for columns already displaying as a Format Column it says Edit Format Column.

ReadOnly Entitlement

The ReadOnly Entitlement behaviour is that Format Columns will be applied but Users cannot manage or suspend them

Predefined Config

The Format Column section of Adaptable State contains just one collection:

PropertyDescription
FormatColumnsCollection of Format Columns

Format Column Object

A FormatColumn object is defined as follows:

PropertyDescriptionDefault
CellAlignmentAligns cells 'Left' or 'Right' or 'Center'
ColumnStyleApplies Gradient, Percent Bar or CheckBox style to a Column
DisplayFormatDisplay Format to apply to Column can be Numeric, String or Date
IncludeGroupedRowsWhether to Format the Column in Grouped Rowsfalse
ScopeWhere Format will be applied - whole Row, some Columns or all Columns of given DataType
StyleStyle to apply

Adaptable Format Object

The DisplayFormat property is of type AdaptableFormat which contains 3 options:

Number Formatter

This applies on Numeric columns.

It contains the Number Formatter Options object which includes a series of formatting options:

PropertyDescription
FractionDigitsNumber of digits to show in fractions
FractionSeparatorSeparator to use in fractions
IntegerDigitsNumber of digits to show for integers
IntegerSeparatorSeparator to use in integers
MultiplierMultiplier to use on the number
ParenthesesShows negative numbers in parentheses
PrefixPrefix to use before the number
SuffixSuffix to use after the number
TruncateTruncates a number

Date Formatter

This applies on Date columns.

It contains the Date Formatter Options object which has a single Pattern property:

PropertyDescription
PatternPattern to use for Date Format

String Formatter

This applies on String columns.

It contains the String Formatter Options object which enables strings to be trimmed and have case set:

PropertyDescription
CaseSets text to Upper or Lower case
TrimTrims text (both start and end)

Column Style Object

The ColumnStyle property is of type ColumnStyle which contains 3 options:

Check Box Style

The Check Box Style is used to decorate Boolean columns to show a Checkbox and simply contains a boolean value.

Gradient Style

The Gradient Style displays numeric cells with a gradient colour and is defined as follows:

PropertyDescription
CellRangesRanges (e.g. to allow a traffic light effect)
ColumnComparisonCompares cell values to another Column

Percent Bar Style

The Percent Bar Style shows a bar in a numeric column based on the value as a percentage and is defined as follows:

PropertyDescriptionDefault
BackColorBack colour - only used for 'Percent Bar' Numeric style; leave unset if none requiredGray
CellRangesRanges (e.g. to allow a traffic light effect)
CellTextWhether Cell shows Cell Value, Percent Value, both or none
ColumnComparisonCompares cell values to another Column
ToolTipTextWhether Tooltip shows Cell Value, Percent Value, both or none

Cell Ranges

Both Gradient Column and Percent Bar contain a CellRanges property of type CellColorRange defined as:

PropertyDescription
ColorCell colour to use for values that fall inside Range
MaxEnd number of Range
MinStart number of Range
ReverseGradientReverses the Gradient so the lower the cell value the darker the colour

Column Comparison

Gradient Column and Percent Bar also both contain a ColumnComparison property of type ColumnComparison defined as:

PropertyDescription
ColorColour to use for the Percent bar
MaxValueEnd value - either numeric or Column name
MinValueStart value - either numeric or Column name

Example

const demoConfig: PredefinedConfig = {
FormatColumn: {
FormatColumns: [
// Set a Style and Cell Alignment for OrderId
{
Scope: {
ColumnIds: ['currency'],
},
Style: {
BackColor: '#d4fb79',
ForeColor: '#8b0000',
},
CellAlignment: 'Center',
},
// Set a Time-based Display Format for LastUpdatedTime
{
Scope: {
ColumnIds: ['LastUpdatedTime'],
},
DisplayFormat: {
Formatter: 'DateFormatter',
Options: {
Pattern: 'HH:mm:ss',
},
},
},
// Set both a Style and a (Date-based) Display Format for OrderDate
{
Scope: {
ColumnIds: ['OrderDate'],
},
Style: {
FontWeight: 'Bold',
FontSize: 'XSmall',
FontStyle: 'Italic',
},
DisplayFormat: {
Formatter: 'DateFormatter',
Options: {
Pattern: 'yyyyMMdd',
},
},
},
// Set Display Format of negative parentheses for ChangeLastOrder
// and change the Header (caption) to 'Order Change'
{
Scope: {
ColumnIds: ['ChangeLastOrder'],
},
HeaderName: 'Order Change',
DisplayFormat: {
Formatter: 'NumberFormatter',
Options: {
Parentheses: true,
},
},
},
// Set a Display Format of £ and 2 dp for InvoicedCost
{
Scope: {
ColumnIds: ['InvoicedCost'],
},
DisplayFormat: {
Formatter: 'NumberFormatter',
Options: {
FractionDigits: 2,
Prefix: '£',
},
},
},
// Set Display Format of $ with 'AUD' suffix and integer space separator for OrderCost
{
Scope: {
ColumnIds: ['OrderCost'],
},
DisplayFormat: {
Formatter: 'NumberFormatter',
Options: {
FractionDigits: 2,
IntegerSeparator: ' ',
Prefix: '$',
Suffix: '(AUD)',
},
},
},
// Set the Cell Aligment of the 'Bid' Column to the right
{
Scope: {
ColumnIds: ['bid'],
},
CellAlignment: 'Right',
},
// Set text in the 'Country' Column to be upper case
{
Scope: {
ColumnIds: ['country'],
},
DisplayFormat: {
Formatter: 'StringFormatter',
Options: {
Case: 'Upper',
},
},
CellAlignment: 'Center',
},
// Set Amount to use a Gradient Numeric Style using 3 Ranges
// with the bottom (red) range displaying a reverse Gradient
{
Scope: {
ColumnIds: ['Amount'],
},
ColumnStyle: {
GradientStyle: {
CellRanges: [
{
Min: 0,
Max: 50,
Color: 'Red',
ReverseGradient: true,
},
{
Min: 50,
Max: 100,
Color: 'orange',
},
{
Min: 100,
Max: 150,
Color: 'DarkGreen',
},
],
},
},
CellAlignment: 'Center',
},
// Set Fill to use a Percent Bar Numeric Style comparing the cell to Amount
// Show both cell text and percent value (on gray background) and a tooltip
{
Scope: {
ColumnIds: ['Fill'],
},
ColumnStyle: {
PercentBarStyle: {
ColumnComparison:{
MinValue: 0,
MaxValue: 'Amount',
Color: 'Brown'
},
CellText: ['CellValue', 'PercentageValue'],
ToolTipText: ['PercentageValue'],
BackColor: 'Gray'
},
},
},
],
},
} as PredefinedConfig;

In this example we have created 7 Format Columns:

  • Order Id has Visual Style of lime green with a red fore colour and a CellAlignment of 'Center'
  • LastUpdatedTime has Display Format with pattern of 'HH:mm:ss' to show just time
  • Order Date has a Visual Style of bold and italicised and with a smaller font and a Display Format with pattern of 'yyyyMMdd'
  • ChangeLastOrder has Display Format to show negative numbers in parantheses and new Header text
  • InvoicedCost has Display Format to show '£' sign and 2 decimal places
  • OrderCost has Display Format to separate integer thousands with a space, a '$' prefix and '(AUD)' suffix
  • Bid has a CellAlignment of 'Center'
  • Country has Display Format to show text in upper case
  • Amount has a Gradient Numeric Style with 3 Ranges

Format Column API

MethodDescription
addFormatColumn(formatColumn)Adds a Format Column to Format Column State
addFormatColumns(formatColumns)Adds Format Columns to Format Column State
applyFormatColumnDisplayFormats()Applies display format for all Format Columns in AdapTable State
deleteAllFormatColumn()Deletes all Format Columns in Adaptable State
deleteFormatColumn(formatColumn)Deletes an existing Format Column
editFormatColumn(formatColumn)Edits the Format Column
fireCheckboxColumnClickedEvent(columnId, rowData, primaryKeyValue, isChecked)Publishes the CheckboxColumnClickedEvent - when the checkbox in an Checkbox Column is clicked
getAllActiveFormatColumn()Retrieves all active (not-suspended) Format Columns in Adaptable State
getAllFormatColumn()Retrieves all Format Columns in Adaptable State
getAllFormatColumnWithCellAlignment()Retrieves all Format Columns in Adaptable State with CellAlignment property set
getAllFormatColumnWithDisplayFormat()Retrieves all Format Columns in Adaptable State with DisplayFormat property set
getAllFormatColumnWithStyle()Retrieves all Format Columns in Adaptable State with the Style property set
getAllSuspendedFormatColumn()Retrieves all suspended Format Columns in Adaptable State
getCheckBoxStyleFormatColumn(column)Returns first Format Column that contains checkbox style
getColumnComparisonForFormatColumn(formatColumn)Retrieves the ColumnComparison property, if there, from a Format Column (Column Style)
getColumnIdsFromColumnComparison(columnComparision)Returns any ColumnIds referenced in a Column Comparison
getFormatColumnById(id)Retrieves Format Column by Id
getFormatColumnForColumn(column)Gets Format Column, if any, for given Column
getFormatColumnForColumnId(columnId)Gets Format Column, if any, for given ColumnId
getFormatColumnState()Retrieves Format Column section from Adaptable State
getFormatColumnWithDisplayFormatForColumn(column)Will get the most appropriate one i.e. first named Column, then DataType, then All
getFormatColumnWithSingleColumnScope(columnId)Get Format Column which has Scope of Single Column, if any, for given ColumnId
getFormatColumnWithStyleClassNameForColumn(column)Gets Format Column if any for given Column which includes Style element with ClassName
getFormatColumnWithStyleForColumn(column)Gets Format Column if any for given Column which includes Style element
getNumericStyleMaxValue(numericStyle, rowNode, cellValue)Gets the Maximum Value to display for a Numeric Style
getNumericStyleMinValue(numericStyle, rowNode, cellValue)Gets the Minimum Value to display for a Numeric Style
hasStyleFormatColumns()Returns true if Format State includes Format Columns with a Style property
isCheckBoxStyleFormatColumn(column)Whether given column is a (boolean) checkbox column
showFormatColumnPopup()Opens the Format Column popup screen
suspendFormatColumn(formatColumn)Suspends an Format column
unSuspendFormatColumn(formatColumn)Suspends an Format column

FAQ

How does this Module differ from the Conditional Styles Module?

The Conditional Styles Module applies the style only when a particular condition is met.

Format Column always styles the column or displays a given format.

If a Conditional Style condition is met, which style will get applied?

The Conditional Style will take preference over a Format Column Style. The order of preference for styles is:

Can I apply more than one Format to a column?

No. A column can only have one Format Column

Can we show a currency in the Display Format?

Yes. Create a prefix (or suffix) as appropriate

Can we set negative numbers to appear in parentheses?

Yes. Check the Parentheses checkbox in the Display Format stage of the wizard.

Can we show percentages?

Yes, this is a common requirement which is typically done by setting the Multiplier property to 100 and the Suffix to '%'

Can divide numbers e.g. show 50,000 as 50K?

Yes, use the multiplier as a divider (so in this case it would be 0.001) and then add a suffice of 'K'

tip

Because this is quite a common display format it is also available as a Numeric Preset

Can we see just the time portion of a Date?

Yes. Set the Display Format to show the time portion only (e.g. see the 'HH:mm:ss' preset which displays the time using a 24 hour clock)

If we show no fractions do you round the number?

Yes, AdapTable will round the number automatically e.g. setting 11.87 to 0 fractions will display '12'.

Can I use Format Column to display a different caption for the Column?

Yes, but this is deprecated; the recommended solution is using the ColumnHeadersMap property in a Layout.

Gradient Style FAQ

Can I merge Gradient and Percent Bar styles for the same column?

No, they are 2 distinct ways of displaying column data and each has a separate implementation - although both share common properties. You need to choose one or the other.

Percent Bar Style FAQ

Can we see the underlying value in the cell?

Yes - you can do this either at Design Time or at Run Time.

Can I see see a Cell Value compared to another Column?

Yes, you can use a Column Comparison.

More Information