A column expression is a placeholder of the form `$C{...}` that refers to a cell or cell range of a [[Invantive Control for Excel/Concepts/Block|block]], independent of where that block currently is on the worksheet. Column expressions solve a fundamental problem: during [[Invantive Control for Excel/Concepts/Synchronization|synchronisation]] a block rolls up and expands again, so hard-coded cell references such as `=B12` break or point to the wrong row. A column expression is translated at fill time into the correct reference for each row. Column expressions can be used in two places: - in the **Excel expression of a field** (an expression field of a block, defined in the [[Invantive Control for Excel/User Interface/Model Editor|Model Editor]]): each `$C{...}` is replaced by an Excel cell or range reference when the block is filled; - in **SQL statements executed in a block context**, such as the list source of "Choose Field Value": each `$C{...}` is replaced by a bind variable carrying the current value of the referenced cell, so the value is passed safely to the [[Invantive UniversalSQL/Invantive UniversalSQL|Invantive UniversalSQL]] statement. ## Syntax ```text $C{ method, block, sheet, column, row } $C{ method, block, sheet, column1, row1, column2, row2 } ``` The five-element form refers to a single cell; the seven-element form refers to the range from the first to the second cell. Whitespace after the commas is allowed. | Element | Allowed values | | --- | --- | | method | `D` (database orientation) or `E` (Excel orientation) | | block | `.` = the current block, or `"CODE"` = the block with that code | | sheet | `.` = current sheet, `^` = first sheet, ` = last sheet, or `"Name"` = the sheet with that name | | column | `.` = current column, `^` = first column of the block, ` = last column of the block, or `"FIELD"` = the column of the field with that name | | row | `.` = current row, `^` = first row of the block, ` = last row of the block | Every element except the method can be followed by an offset `+n` or `-n`, for example `.+2` (two columns to the right of the current column) or `$-1` (one before the last row). "Current" (`.`) means: the position of the cell for which the expression is being evaluated. For an expression field this is the cell being filled, so `.` automatically follows the row while the block fills downwards. ## Database versus Excel Orientation The first element determines how "column" and "row" are interpreted: - **`D` (database)**: "column" means a database column (field) of the block and "row" means a database row (record). Invantive Control translates this to worksheet coordinates using the repeat settings of the block. This is the recommended method: the expression keeps working when the modeler turns the block, for example from records-as-rows to records-as-columns. - **`E` (Excel)**: "column" and "row" are literal worksheet columns and rows of the block area, regardless of how the block is turned. Use this only when you deliberately want a fixed worksheet direction. ## Examples All examples come from expression fields in real models. The surrounding text is a normal Excel formula; only the `$C{...}` parts are replaced. Pass the key value of the current row to an Invantive formula. `^` is the first column of the block, which holds the item code; `.` is the current row: ```text =I_EOL_ITM_DESCRIPTION(eoldivision, $C{D,.,.,^,.}) =I_EOL_ITM_SALES_PRICE_VALUE(eoldivision, $C{D,.,.,^,.}) ``` Calculate with neighbouring columns of the same row. `.+2` and `.+1` are the second and first column to the right of the expression column: ```text =$C{D,.,.,.+2,.} - $C{D,.,.,.+1,.} ``` Multiply values from other columns of the current row: ```text =$C{D,.,.,.-1,.} * $C{D,.,.,.-5,.} ``` Sum a series of columns, here the twelve columns preceding the expression column. Two single-cell expressions joined with `:` form an Excel range; the seven-element form gives the same result: ```text =SUM($C{D,.,.,.-12,.}:$C{D,.,.,.-1,.}) =SUM($C{D,.,.,.-12,.,.-1,.}) ``` Divide by a constant: ```text =$C{D,.,.,.-1,.} / 12 ``` Refer to a column by field name instead of by position, so the expression survives reordering of fields: ```text =$C{D,.,.,"QUANTITY",.} * $C{D,.,.,"UNIT_PRICE",.} ``` Refer to another block, for instance a totals cell under a block with code `SALES` on the sheet named "Data": ```text =SUM($C{D,"SALES","Data",^,^}:$C{D,"SALES","Data",$,$}) ``` In a SQL statement, for example the list source of "Choose Field Value", the value of the first column of the current row is passed as a bind variable: ```sql select itm.code , itm.description from exactonlinerest..items itm where itm.itemgroupcode = $C{D,.,.,^,.} ``` ## Remarks - The block element accepts only `.` or a quoted code; `^` and ` are not valid for blocks. - The row element does not accept a quoted name; names identify fields (columns), not records. - The sheet count for ` excludes the hidden Invantive system worksheets. - In expression fields, prefer `D` orientation and field names over fixed offsets where readability matters; both survive a re-layout of the block, but field names also survive insertion of new fields. - Expression fields themselves are calculated in Excel and are by default not uploaded; a modeler can designate a separate writeback field for the result.