Specific formulas for data extraction in table type fields

1. FORMULA GET_CELL_VALUE({field}, row, column, lang)

This formula returns the content of a cell in the table type field specified by field, and based on the coordinates indicated by the row and column parameters.

The parameters field, row, and column are mandatory. If the parameter indicated as field was not a table type field, a syntax error will occur in the formula.

The parameters  row and column accept a text type value or a numeric type value.

If a numeric type value is specified (not in quotes), Sales Layer will try to obtain the result cell value of the coordinate specified by row and column, starting from 0 for both rows and columns.

If a text type value is specified in these parameters, Sales Layer will try to obtain the value of the cell coordinate, as a result of the search: for the row parameter, in the content of the first column of the table, whereas for the column parameter in the content of the first row of the table.

If the formula is unable to find a valid coordinate, it will return an empty value.

The following sample table shows some examples for better understanding:

GET_CELL_VALUE({features}, 2, 1)    //Resultado: 230
GET_CELL_VALUE({features}, “IP Protection”,”Available in Web”)    //Resultado: Yes
GET_CELL_VALUE({features}, “Voltage”, 1) //Resultado: 230
GET_CELL_VALUE({features}, “Voltage”, “No existe”) //Resultado: 

The lang parameter is optional. If specified and the field is a multi-language table field, it will try to retrieve the coordinate value specified in the field content in the indicated language.

GET_CELL_VALUE({features}, “Voltage”, 1, “en”) //Resultado: 230

* About the dynamics of the lang parameter:

This parameter is optional. If the table type field from which you are trying to obtain the cell information is configured as a multi-language field (and the lang parameter is omitted from the formula)  the expulsion of data is determined by the configuration of the connector in which the formula is being used. In other words, it will expel as many connected fields as the languages that are configured in the connector.

Let's imagine a connector that uses a table-type field like the one in the example and which is configured in Sales Layer as a multi-language field. We connect it as a column output value called features in which we use the formula GET_CELL_VALUE. If we omit the value for the language field and the connector is configured to eject data in 3 languages (for instance "es", "en" and "fr"), the result of the output connector for the table and formula example would be:

GET_CELL_VALUE({features}, 2, 1)  

Connector output columns:

features_en  //Resultado : 230

features_es //El valor de coordenadas de la tabla en idioma Español

features_fr //El valor de coordenadas de la tabla en idioma Francés

However, if we specify a valid value for the language parameter, we will only get a column ejected in the connector with the specified language, regardless of the languages defined in the connector.

GET_CELL_VALUE({features}, 2, 1, “en”)

Output column fo the connector:

features  //Resultado : 230

Tip: We recommend omitting this parameter when executing the formula on a table-type field that is not configured as multi-language.

2. FORMULA PRINT_TABLE_FIELD(field, format, options, lang)

This data extraction formula can be used in output connectors such as Excel, CSV, and Generic ... By applying this formula you will get a normalization or formatting of the complete content of a table-type field, taking into account the parameters supplied in format and options.

The possible standard output formats, indicated as the value of the format parameter are: JSON, XML, and HTML.

The options and lang parameters are optional.

* The dynamics of the lang parameter are similar to the ones of the GET_CELL_VALUE formula, as described above. Except in specific cases, we recommend to omit the value of this parameter and leave it at the expense of the connector's own language configuration where the formula is used.

The options parameter accepts the following possible values, which can be indicated as a list of elements, allowing the formula to apply more parameters at the same time.

The values entered in the options parameter only have an effect on the JSON and XML formats and have no impact on the HTML format.

FIRST_ROW_AS_COLUMN_TITLES: If this parameter is given, the first row of the table field will be taken into account as column titles to compose the appropriate output in the affected formats.

FIRST_COLUMN_AS_ROW_TITLES: Similar to the previous one but it takes into account the values of the first column of the table field, such as row titles when generating the output in the selected format.

REMOVE_COLUMS_WITH_EMPTY_COLUMN_TITLES: It only works if the parameter FIRST_ROW_AS_COLUMN_TITLES is used. In that case, those columns for which there is no defined title in the first row will be completely removed from the output (the columns, whose value in the first row is empty).

REMOVE_ROWS_WITH_EMPTY_ROW_TITLES: Same as the previous one but it affects those rows, whose value in the first column is empty.

Below are some examples to better understand the result of this formula:

PRINT_TABLE_FIELD({Features},”HTML”)

​​​​​​​<table>
   <thead>
      <tr>
         <th>Feature name</th>
         <th>Value</th>
         <th></th>
         <th>Modified</th>
      </tr>
   </thead>
   <tbody>
      <tr>
         <td>Voltage (V)</td>
         <td>120</td>
         <td>Review by John</td>
         <td>14/11/2018</td>
      </tr>
      <tr>
         <td>IP Protection</td>
         <td>IP20</td>
         <td>Review by Marcy</td>
         <td>13/11/2018</td>
      </tr>
   </tbody>
</table>

​​​​​​​PRINT_TABLE_FIELD({Features},”JSON”)

[
   [
      "Feature name",
      "Value",
      null,
      "Modified"
   ],
   [
      "Voltage (V)",
      "120",
      "Review by John",
      "14\/11\/2018"
   ],
   [
      "IP Protection",
      "IP20",
      "Review by Marcy",
      "13\/11\/2018"
   ]
]

PRINT_TABLE_FIELD({THIS}, "JSON", [FIRST_ROW_AS_COLUMN_TITLES, REMOVE_COLUMS_WITH_EMPTY_COLUMN_TITLES])

​​​​​​​{
   "row_1":{
      "Feature name":"Voltage (V)",
      "Value":"120",
      "Modified":"14\/11\/2018"
   },
   "row_2":{
      "Feature name":"IP Protection",
      "Value":"IP20",
      "Modified":"13\/11\/2018"
   }
}

PRINT_TABLE_FIELD({THIS}, "JSON", [FIRST_ROW_AS_COLUMN_TITLES, FIRST_COLUMN_AS_ROW_TITLES])

​​​​​​​{
   "Voltage (V)":{
      "Value":"120",
      "column_2":"Review by John",
      "Modified":"14\/11\/2018"
   },
   "IP Protection":{
      "Value":"IP20",
      "column_2":"Review by Marcy",
      "Modified":"13\/11\/2018"
   }
}

PRINT_TABLE_FIELD({THIS}, "XML", [FIRST_ROW_AS_COLUMN_TITLES, FIRST_COLUMN_AS_ROW_TITLES])

<?xml version="1.0"?>
<prod_features_en>
  <Voltage (V)>
    <Value>120</Value>
    <column_2>Review by John</column_2>
    <Modified>14/11/2018</Modified>
  </Voltage (V)>
  <IP Protection>
    <Value>IP20</Value>
    <column_2>Review by Marcy</column_2>
    <Modified>13/11/2018</Modified>
  </IP Protection>
</prod_features_en>