Specific formulas for insertion and/or modification of data in table-type field

1. FORMULA INSERT_IN_TABLE (mode, row 1, column 1, value 1, row 2, column 2, value 2, ...)

This formula is used for inserting data into table-type fields, either through its use in import connectors (generic import, etc.) or through its use in bulk modification of elements that are accessible through the Sales Layer cloud menu.

It always applies to the currently defined field (the one connected to an input connector, or the one selected in the bulk modification), so it is not necessary to indicate the field to which it applies as a parameter.

The formula has three different modes of action: insert, update, and delete. The mode of action must be indicated in the mode parameter and it is mandatory to use one of these three.

Depending on the selected mode in the formula, the data will affect in one way or another the content that may already exist at the time in the affected table field.

The row x, column x, and value x parameters are used to insert data at the coordinates defined by row and column, both by localized names and titles in the first row or first column, as seen above, as well as by their value as the numerical index.

You can define as many triples of parameters row x, column x, and value x as you want. If the count of these parameters is not a multiple of three, the formula will return a syntax error.

In some modes of operation of the formula it is possible to leave the values of row and column blank in quotation marks (“”). In that way, new row or column insertions can be made in the selected table field. This is useful for connecting a new data structure as explained below.

The three modes of operation with which this formula will work are explained below, the effect of which takes into account the content value currently stored in the table type field on which the data manipulation operation must be performed.

1.1. INSERT MODE

INSERT Mode refers to the value of the content that may previously exist in the processed table. By a triple of row, column, and value, the operation will result in different ways. If the name indicated in the row and column parameter is found in the first column and first row, the cell that matches the dimension will be updated with the supplied value. If any of them are not found, the corresponding dimension (row, column, or both) will be created at the end of the table and the provided value will be inserted there. If the row or column value is omitted (“” empty quotation marks), a new row or column will be created at the end of the table.

Index values can be supplied in rows or columns (starting with 0). Sales Layer will attempt to update the value of the cells that coincides with that dimension (to indicate indexes, the row and column parameters do not have to be in quotation marks). Only data can be updated when using numerical indexes to access a cell; never insert new rows or columns.

An example is shown below in the following tables:

(This would be the current content of the table type field: Features).

Result when applying the formula:

INSERT_IN_TABLE(“insert”, “IP Protection”, “Value”, “IP66”, 0, 2, “Review By”, “Frequency”, “Value”, 50, “”, “Web Available”, “”)

The different colors highlight the effect of each of the triples [row, column, value] applied as a parameter to the formula in the insert mode on the original content of the Features field.

As a particular case (marked in red) you can see how to add a new column to the table, without values.

You can see another interesting feature of the insert mode (marked in orange). This is used to add brand-new rows or new columns, as in this example.

1.2. UPDATE MODE

The UPDATE Mode works similarly to the Insert Mode, except that in Update mode new rows or new columns will never be created on top of the previous content of the table field when it is impossible to find a row/column correspondence in the current data.

In the tables below, we can see the result of applying the same formula in update mode on the same previous value of the Features field:

Result when applying the formula:

INSERT_IN_TABLE(“update”, “IP Protection”, “Value”, “IP66”, 0, 2, “Review By”, “Frequency”, “Value”, 50, “”, “Web Available”, “”)

As you can see, only update actions take place on existing rows or columns; update actions are not applied.

1.3. DELETE MODE

DELETE mode works exactly the same as the Insert Mode, except that it gives a deletion of the previous content in the specific table field. Therefore the actions of the triplets [row, column, value] start on an empty content. In other words, prior to the insertion of data, there is a “reset” of the contents of the table field.

This mode is useful for supplying all the values of the table field in the current operation and deleting the previous content.

Warning: If a template has been configured for the table field, this will be taken into account when starting to fill in its values.

Below we can see the same example when we processed this function in the Delete Mode, for a field-like feature, for which we did NOT configure any template when we created the field and with a basic template defined at field level.

Result when applying the formula (without a defined template at the field level): 

INSERT_IN_TABLE(“delete”, “IP Protection”, “Value”, “IP66”, 0, 2, “Review By”, “Frequency”, “Value”, 50, “”, “Web Available”, “”)

Result when the features field (in the process language) has a defined template as the following:

Result:

As shown above, both the Insert and Delete Mode are used for making changes at the insertion level of new rows and columns. When a template is defined at the configuration level of a table type field, these templates will be taken into account by the INSERT_IN_TABLE formula, when the specific table field is empty (without content). This situation always occurs in delete mode, obviously.