Dynamic Tables in Calculations

The dt object is a query interface for dynamic tables, available in every task calculation (on the Calculations tab and in shared scripts of the Calculations type). Unlike the older lib.dynamicTableGetCell/dynamicTableUpdateCell (see Calculations API Reference: lib library), it works in a "query builder" style: you call dt.from(...), add where... conditions, and only then call get(), update() or remove(). This article is a reference for TAS 5.19.12.

// Najde řádek dodavatele podle IČO a přečte jeho e-mail
const rows = await dt.from('Suppliers', ['COL_2', 'COL_4']).whereCol('COL_1', vars['SupplierIco'].getValue()).get();
vars['SupplierEmail'].setValue(rows[0] ? rows[0].COL_4 : null);

Creating a query

MethodReturnsDescription
from(dt, columns?)Promise<this>Sets the table dt (name or ID) to work with and clears previous filters. columns (an array, e.g. ['COL_1', 'COL_3']) limits the columns loaded – without it, all columns are loaded, which is slower.
select(columns)Promise<this>An alternative to specifying columns in from() – it must be called right after from() and cannot be combined with columns in from().

Columns are specified as COL_1, COL_2... (the column's sequence number), or directly as a number – whereCol(1, ...) and whereCol('COL_1', ...) work the same way.

Filtering (where)

MethodDescription
whereCol(column, value, operator='=')Filters by a column value. operator: =, <, >, <=, >=, like, not like, <>.
orWhereCol(column, value, operator='=')The same, but joined to the previous condition with OR.
whereIndex(index, operator='=') / orWhereIndex(index, operator='=')Filters by row index (DTV_INDEX).
whereNullCol(column) / whereNotNullCol(column)Rows where the column is/is not null (does not apply to an empty string '').
orderBy(column, direction='asc', sortType='text')Sorts the result. column must be COL_{n}, DTV_INDEX or DTV_ORDER_INDEX; sortType is text or number.

Multiple whereCol/whereIndex calls are combined (with AND, unless you use orWhereCol/orWhereIndex).

const rows = await dt.from('Suppliers')
    .whereCol('COL_3', 'CZ', 'like')
    .whereNotNullCol('COL_4')
    .orderBy('COL_1', 'asc')
    .get();

Reading data

MethodReturnsDescription
get()Promise<object[]>Returns rows matching the configured filter. Without a where condition, it loads the whole table (and logs a warning).
getFirst()Promise<object | null>The first row of the result.
getLast()Promise<object | null>The last row of the result.
count()Promise<number>The number of matching rows.
getCell(dt, index, col)Promise<string | null>Shortcut: finds a row by index and returns the value of a single column, without having to call from/where separately.
getCols()Promise<object>A bidirectional mapping of the table's column names and IDs ({1: 'COL_1', COL_1: 1, ...}).
getId()Promise<number | string>The ID of the table set via from().
getSourceFiles(returnOnlyLastRecord)Promise<object[]>The history of files from which the table was filled by import; true returns only the last record.
const unitPrice = await dt.getCell('Price list', vars['ItemCode'].getValue(), 'COL_3');

Writing and deleting

MethodReturnsDescription
update(column, value)Promise<number>Updates a column of the selected rows (according to where). column can also be an object {COL_1: '...', COL_2: '...'} to update multiple columns at once – in that case value is ignored. Returns the number of updated columns.
addRow(index, data)Promise<number>Creates a new row with the given index and data {COL_1: '...', ...}.
addEmptyRow(index)Promise<number>Deprecated, use addRow. Creates an empty row.
remove(removeLimit=10)Promise<number>Deletes the rows matching the filter and returns their count. Without a filter, it deletes the whole table. If the number of rows to delete exceeds removeLimit, it throws an exception instead of deleting – a safeguard against mistakes.
// Zdraží všechny položky dodavatele o 5 %
const rows = await dt.from('Price list').whereCol('COL_2', vars['SupplierId'].getValue()).get();
for (const row of rows) {
    await dt.from('Price list').whereIndex(row.DTV_INDEX).update('COL_3', Number(row.COL_3) * 1.05);
}

// Smaže položky ceníku starší než loňský rok (s pojistkou max. 50 řádků)
await dt.from('Price list').whereCol('COL_5', '2025-01-01', '<').remove(50);
remove() without a filter deletes the whole table
dt.remove() without a preceding whereCol/whereIndex deletes all rows. Always narrow the selection with a filter first, and set removeLimit slightly above the expected number of rows to delete, so that an error in the filter results in an exception instead of a mass deletion.
Table permissions
dt.from() internally checks that the current process is allowed to call the dynamic table (this is configured on the table in administration). If not, it throws the exception Dynamic table '...' cannot be called from this Process.

Related: Dynamic Tables (administration), Calculations API Reference: lib library, Dynamic Rows: script.

Syca (opens in a new tab)

Powered by HelpDocs (opens in a new tab)