Table Type Field: Formula usage

There are four formulas for working with the table-type field, intended for two main uses:

Entering and extracting content from table fields with channels;
Performing actions in bulk.

Two of them are used for data output (GET_CELL_VALUE, PRINT_TABLE_FIELD), one for data input (INSERT_IN_TABLE), and one for eliminating rows or columns (REMOVE_IN_TABLE). You can find more examples of them both in the connector itself, and also in the formula documentation.

GET_CELL_VALUE: this returns the value of a cell. If we search for a cell using a number (without quotation marks), it will extract the value from that cell number. If you place a value with quotations, then it will search by name. In addition, it also allows you to add the ‘lang’ parameter to return any language if configured.

GET_CELL_VALUE({table_field}, column, row, “language”)

{table_field}: Table-type field from which data will be retrieved.
column: Column within the table-type field from which the cell will be extracted.
row: Row within the table-type field from which the cell will be extracted.
"language": In case the table-type field is multilingual, you can extract the selected language.

So, with the following table:

And using the following formula, we would obtain the following result:

With PRINT_TABLE_FIELD, we can extract the contents of the table type field in 3 possible formats:

JSON
XML
HTML

This formula has 4 additional options that are described in the formula document and are used mainly when working with JSON and XML.

PRINT_TABLE_FIELD({field}, “format”, [options], “lang”)

{field}: Table field from which we want to extract data.
format: Format in which we want to export the data from the table field, it can be JSON, XML, or HTML.
options: An array of options to consider when exporting the data. This array of options may contain more than one parameter. Options can include: FIRST_ROW_AS_COLUMN_TITLES, FIRST_COLUMN_AS_ROW_TITLES, REMOVE_COLUMNS_WITH_EMPTY_COLUMN_TITLES, REMOVE_ROWS_WITH_EMPTY_ROW_TITLES.
lang: Language of the content in the field that we want to export.

For example, we will print the same table-type example in XML:

And using the following formula, we would obtain the following result:

INSERT_IN_TABLE: this allows users to insert multiple values together. It has three options:

Insert: this creates the value if it does not exist and if it does, it updates it.
Update: this can only update, not create values.

INSERT_IN_TABLE(“mode”, row 1, column 1, “value 1”, row 2, column 2, “value 2”, …, row n, column n, “value n”)

"mode": The way data is treated, which can be: insert or update.
"row n": The row number where the cell to be modified is located.
"column n": The column number where the cell to be modified is located.

For example, imagine that we had the following table:

With the following formula, we are going to create 3 insertion groups: the first one will update the value of a cell with the given row and column coordinates, the second part will create a new row with a value for the “Reference” column, and the third one creates a New empty column. For the latter, we put empty quotes in the row title, and for the value, we simply put some text in the column name.

The result w be:

REMOVE_IN_TABLE allows us to remove rows or columns from a table.

REMOVE_IN_TABLE(“element”, “string”/[“list”])

“element”: refers to "rows" to mark rows or "columns" to mark columns.
“string”: we can indicate a single row or column in particular: "row1", "column2".
[list]: we can indicate several items in a list: ["row1", "row2", ..., "rown"].

Using the following table field and formula:

We achieve the following results:

Remember, you can find more detailed information in our section related to the use of formulas.

Related Links