Use the CellCaptionFormat property to change the type for a particular cell. Use the CellCaption property to specify the cell's content. For instance, if the CellCaptionFormat property is exComputedField, the Caption property indicates the formula to compute the cell's content.
The Def(exCellCaptionFormat) property is changed to exComputedField, each time the ComputeField property is changed to a not empty value. If the ComputedField property is set to an empty string, the Def(exCellCaptionFormat) property is set to exText. Call the Refresh method to force refreshing the control.
The expression may be a combination of variables, constants, strings, dates and operators. A string is delimited by ", ` or ' characters, and inside they can have the starting character preceded by \ character, ie "\"This is a quote\"". A date is delimited by # character, ie #1/31/2001 10:00# means the January 31th, 2001, 10:00 AM.
The expression supports cell's identifiers as follows:
- %0, %1, %2, ...{any} specifies the value of the cell in the column with the index 0, 1 2, ... The CellCaption property defines the cell's value. For example, "%0 format ``" formats the value in the cell at index 0 using the current regional settings, while "int(%1)" converts the value in the cell at index 1 to an integer.
- %C0, %C1, %C2, ...{string} specifies the caption of the cell, or the string the cell displays in the column with the index 0, 1 2, ... The CellCaption property gets the cell's formatted caption. The cell's displayed string may differ from its actual value. For example, if a cell displays HTML content, %0 returns the HTML format including the tags, while %C0 returns the cell's content as a plain string without HTML tags. For instance, "upper(%C1)" converts the caption of the cell at index 1 to uppercase, while "%C0 left 2" returns the leftmost two characters of the caption in the cell at index 0.
- %CD0, %CD1, %CD2, ...{any} specifies the cell's extra data in the column with the index 0, 1 2, ... The CellData property associates any extra/user data to a cell. For example, "%CD0 = your user data" specifies all cells in the column with index 0 whose CellData property is equal to your user data.
- %CS0, %CS1, %CS2, ...{number} specifies the cell's state in the column with the index 0, 1 2, ... The CellState property defines the state of a cell, indicating whether it is checked or unchecked. For example, "%CS0" identifies all checked items in the column with index 0, while "not %CS1" identifies all unchecked items in the column with index 1.
- %CC0, %CC1, %CC2, ... {number} retrieve the number of child items (this keyword consistently returns identical results for all cells since it pertains to the item that hosts each cell). The ChildCount property returns the number of child items. For example, "%CC0" identifies all parent items, while "%CC0 = 0" identifies all leaf items.
- %CX0, %CX1, %CX2, ... {boolean} returns true if the item hosting the cell is expanded, or false if it is collapsed (this keyword consistently returns identical results for all cells since it pertains to the item that hosts each cell). The ExpandItem property specifically indicates whether the item is expanded or collapsed. For example, "%CX0" refers to all expanded items, while "not %CX0" identifies all collapsed items
The predefined operators for auto-numbering are:
- number index 'format', indicates the index of the item. The first
added item has the index 0, the second added item has the index 1, and so
on. The index of the item remains the same even if the order of the items
is changed by sorting. For instance, 1 index '' gets the index of the item
starting from 1 while 100 index '' gets the index of the item starting
from 100. The number indicates the starting index, while the format is a
set of characters to be used for specifying the index. If the format is
missing, the index of the item is formatted as numbers. For instance: 1
index 'A-Z' gets the index as A, B, C... Z, BA, BB, ... BZ, CA, ... . The
1 index 'abc' gives the index as: a,b,c,ba,bb,bc,ca,cb,cc,.... You can use
other number formatting function to format the returned value. For
instance "1 index '' format '0||2|:'" gets the numbers grouped
by 2 digits and separated by : character.
In the following screen shot the FormatColumn("Col 1") = "1 index ''"
In the following screen shot the FormatColumn("Col 1") = "1 index 'A-Z'"
- number apos 'format' indicates the absolute position of the item.
The first displayed item has the absolute position 0 ( scrolling position
on top ), the next visible item is 1, and so on. The number
indicates the starting position, while the format is a set of characters
to be used for specifying the position. For instance, 1 apos '' gets the
absolute position of the item starting from 1, while 100 apos '' gets the
position of the item starting from 100. If the format is missing, the
absolute position of the item is formatted as numbers.
In the following screen shot the FormatColumn("Col 1") = "1 apos ''"
In the following screen shot the FormatColumn("Col 1") = "1 apos 'A-Z'"
- number pos 'format' indicates the relative position of the item.
The relative position is the position of the visible child item in the
parent children collection. The number indicates the starting position,
while the format is a set of characters to be used for specifying the
position. For instance, 1 pos '' gets the relative position of the item
starting from 1, while 100 pos '' gets the relative position of the item
starting from 100. If the format is missing, the relative position of the
item is formatted as numbers. The difference between pos and opos can
be seen while filtering the items in the control. For instance, if no
filter is applied to the control, the pos and opos gets the same result.
Instead, if the filter is applied, the opos gets the position of the item
in the list of unfiltered items, while the pos gets the position of the
item in the filtered list.
In the following screen shot the FormatColumn("Col 2") = "'<b>' + 1 pos '' + '</b> ' + value"
In the following screen shot the FormatColumn("Col 2") = "'<b>' + 1 pos 'A-Z' + '</b> ' + value"
- number opos 'format' indicates the relative old position of the item. The relative old position is the position of the child item in the parent children collection. The number indicates the starting position, while the format is a set of characters to be used for specifying the position.For instance, 1 pos '' gets the relative position of the item starting from 1, while 100 pos '' gets the relative position of the item starting from 100. If the format is missing, the relative position of the item is formatted as numbers. The difference between pos and opos can be seen while filtering the items in the control. For instance, if no filter is applied to the control, the pos and opos gets the same result. Instead, if the filter is applied, the opos gets the position of the item in the list of unfiltered items, while the pos gets the position of the item in the filtered list.
- number rpos 'format' indicates the relative recursive position of
the item. The recursive position indicates the position of the parent
items too. The relative position is the position of the visible child item
in the parent children collection. The number indicates the starting
position, while the format is of the following type "delimiter|format|format|...".
If the format is missing, the delimiter is . character, and the positions
are formatted as numbers. The format is applied consecutively to each
parent item, from root to item itself.
In the following screen shot the FormatColumn("Col 1") = "1 rpos ''"
In the following screen shot the FormatColumn("Col 1") = "1 rpos ':|A-Z'"
In the following screen shot the FormatColumn("Col 1") = "1 rpos '.|A-Z|'"
In the following screen shot the FormatColumn("Col 1") = "1 apos ''" and FormatColumn("Col 2") = "'<b><font Tahoma;10>' + 1 rpos '.|A-Z|' + '</font></b> ' + value"
-
number rindex 'format', number rapos 'format' and number ropos 'format' are working similar with number rpos 'format', excepts that they gives the index, absolute position, or the old child position.
This property/method supports predefined constants and operators/functions as described here.
Samples:
- "1", the cell displays 1
- "%0 + %1", the cell displays the sum between cells in the first and second columns.
- "%0 + %1 - %2", the cell displays the sum between cells in the first and second columns minus the third column.
- "(%0 + %1)*0.19", the cell displays the sum between cells in the first and second columns multiplied with 0.19.
- "(%0 + %1 + %2)/3", the cell displays the arithmetic average for the first three columns.
- "%0 + %1 < %2 + %3", displays 1 if the sum between cells in the first two columns is less than the sum of third and forth columns.
- "proper(%0)'" formats the cells by capitalizing first letter in each word
- "currency(%1)'" displays the second column as currency using the format in the control panel for money
- "len(%0) ? currency(dbl(%0)) : ''" displays the currency only for not empty/blank cells.
- "int(date(%1)-date(%2)) + 'D ' + round(24*(date(%1)-date(%2) - floor(date(%1)-date(%2)))) + 'H''" displays interval between two dates in days and hours, as xD yH
- "2:=((1:=int(0:= date(%1)-date(%0))) = 0 ? '' : str(=:1) + ' day(s)') + ( 3:=round(24*(=:0-floor(=:0))) ? (len(=:2) ? ' and ' : '') + =:3 + ' hour(s)' : '' )" displays the interval between two dates, as x day(s) [and y hour(s)], where the x indictaes the number of days, and y the number of hours. The hour part is missing, if 0 hours is displayed, or nothing is displayed if dates are identical.