Format Column

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

Style

Users can set the style for the Column by selecting the fore (i.e. font) and back colours and any font properties as appropriate.

note

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

Display Format

The Display Format sets how the value in the column will be formatted, so it matches the precise requirements of users.

caution

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

Display Formats can be set for:

  • Numeric Columns: users can use the Number Formatter to set values for Prefix, Suffix, Fraction Separator, Integer Separator, Fraction Digits, Integer Digits, Multiplier and Negative Parentheses.

  • String Columns: users can use the String Formatter to trim text, or set to upper or 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

Cell Alignment

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

Scope

This 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')

See Scope for more information.

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.

Entitlements

Format Column Entitlement Rules:

  • Full: Everything is available to the User

  • Hidden: Everything is hidden from the User

  • ReadOnly: User can see Format Column styles defined in Predefined Config but not add, edit or delete them.

Format Column Predefined Config

The Format Column section of Predefined Config enables the provisiion of multiple formats at design-time.

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 a Display Format of negative parentheses for ChangeLastOrder
{
Scope: {
ColumnIds: ['ChangeLastOrder'],
},
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',
},
],
},
} 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 a 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 a Display Format to show negative numbers in parantheses
  • InvoicedCost has a Display Format to show '£' sign and 2 decimal places
  • OrderCost has a Display Format to separate integer thousands with a space, a '$' prefix and '(AUD)' suffix
  • Bid has a CellAlignment of 'Center'
  • Country has a Display Format to show text in upper case

FAQ

How does this function differ from the Conditional Styles function?

The Conditional Styles function applies the style on when a particular condition is met. Format Column will always style the column; it is best used when a user wants a particular column to have a distinctive background or font.

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:

  • Flashing Cell

  • Quick Search

  • Conditional Style

  • Updated Row

  • Format Column

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

More Information