A block is a contiguous, rectangular area in an Excel worksheet that is filled with data from the facts database and, when allowed, written back to it. Blocks are the central mechanism of Invantive Control for Excel: everything downloaded or uploaded flows through a block. A block spans one of four dimensions: - **cell**: a single value; - **column**: values repeat downwards along rows; - **row**: values repeat sideways along columns; - **worksheet**: values repeat along both rows and columns. The repeat behaviour is controlled per block with "repeat rows along" and "repeat columns along" (`blk_repeat_rows_along`, `blk_repeat_columns_along`). A block can have a fixed position or be anchored to another block, so that blocks reposition correctly when a preceding block grows or shrinks during [[Invantive Control for Excel/Concepts/Synchronization|synchronisation]]. ## Identification and Layout - **Code** (`blk_code`): unique identifier of the block within the model. - **Labels** (`blk_label_singular`, `blk_label_plural`): display names used in the user interface, for instance on the "Add Row" and "Remove Row" buttons. - **Layout range** (`blk_layout_range`): the worksheet area the block occupies. - **Named ranges**: Invantive Control automatically maintains named ranges for the data area and border of each block. Use these named ranges - typically combined with the Excel `INDEX` function - instead of fixed cell references, since cell positions shift when block contents change during synchronisation. - **Borders**: a block can reserve border rows/columns around the data area (`blk_border_width_*`) for headers and totals. ## Data Definition The data of a block is defined with [[Invantive UniversalSQL/Invantive UniversalSQL|Invantive UniversalSQL]]: - the source table or view of the connected platform (`blk_select_business_object`), or a free-format SQL statement; - per field a select expression, data type, label, visibility and whether the field synchronises back (`fid_sync_back_flag`); a field can also be an expression field whose cells are filled with an Excel formula containing [[Invantive Control for Excel/Concepts/Column Expressions|column expressions]]; - a filter clause and where clause (`blk_select_filter_clause`, `blk_select_where_clause`), typically referring to [[Invantive Control for Excel/Concepts/Parameter|parameters]]; - a primary key expression (`blk_primary_key_expression`) that uniquely identifies each row, required for writeback. ## Writeback Control Per block the modeler controls which changes the model user may make: - allow insert (`blk_allow_insert`): new rows may be added; - allow update (`blk_allow_update`): existing values may be changed; - allow delete (`blk_allow_delete`): rows may be removed. Changes inside a block are registered as [[Invantive Control for Excel/Concepts/Pending Change|pending changes]] until uploaded. ## Block Events Blocks support extension points around synchronisation, plus instead-of triggers that replace the default DML generation: | Event | Moment | | --- | --- | | `blk_on_before_download` / `blk_on_after_download` | around the download of this block | | `blk_on_before_upload` / `blk_on_after_upload` | around the upload of this block | | `blk_instead_of_insert` | replaces the generated insert statement | | `blk_instead_of_update` | replaces the generated update statement | | `blk_instead_of_delete` | replaces the generated delete statement | Instead-of triggers are typically used when the target platform requires an API-specific statement instead of a plain `insert`/`update`/`delete`, or when one logical change must fan out to multiple tables. ## Synchronisation Order and Statistics Blocks are downloaded and uploaded in a modeler-defined order (`blk_download_order`, `blk_upload_order`), so dependent blocks can rely on data loaded earlier. Per block, statistics are maintained such as number of rows downloaded/uploaded, number of successful downloads/uploads and the dates of first and last synchronisation. All of these can be queried with the `I_BLK_*` formulas, see [[Invantive Control for Excel/Formulas/Formulas|Formulas]].