Tables: Apply formulas to columns

Use this action to apply a field formula to set the value of one or more columns for every row in a data table. This action always creates a new data table with the results to avoid unexpected overwriting.

To apply formulas to a CSV the same way, use the CSV: Apply formulas to columns action. CSVs process faster and support higher row limits.

This is an advanced action that requires Javascript. This article provides an overview of the Javascript in the row formula function, but doesn’t cover Javascript extensively.

How to configure this action

This action takes a formula from the row formula field and executes it for every row. For example, the table below has a Price and Quantity column, and two empty columns, Total and Formatted Total.

Row formulas use basic Javascript notation to calculate new values based on rows and columns, like adding two values together, then saving the result to a column.

A simplified example of the notation is Destination column = Column A + Column B, like Total Column = Price * Quantity. All columns used in the formula must exist in the table; new columns can’t be added with this action. Here are some example row formulas:

Using the last example,

    columns['Total'] = columns[2] * columns[3];columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00'

The Total and Formatted Total columns are set in one row formula, with the result of:

Add row numbers to make joining tables easier

Use the formula notation: Destination column = rowIndex to quickly assign row numbers to every row. Easily join tables by using a related column between them.

Using the syntax above, add a row number to the column Row Number with the following formula:

columns['Row Number'] = rowIndex

Apply formulas to all columns

Be default, this action applies a formula to every row in a column. It’s possible to also apply a formula to every row in a column, for every column. This will effectively apply a formula to all data in the table.

To do so, start with the following formula template:

var i = 1
while (i < 100) {
    columns[i] = "X";
    i++;
}

Then, change "X" to adjust what happens to each column. For example, to replace all quotation marks in all columns, you could use this formula:

var i = 1
while (i < 100) {
    columns[i] = columns[i].replace(/“/g, ’’);
    i++;
}

Fields for this action

• Data table

  • Select a table from a list of all tables available on your team. The list only includes tables you have permission to view.
    • You can also reference a table stored in a field. Change the left hand drop-down to Use table via field then select from any field that is part of the process. Learn more.
    • If necessary, you can enter the Table ID directly. Change the left hand drop-down to Use table by ID then enter the ID manually. Learn more.

• Row formula

  • Enter the row formula to apply to the data table column. Use a combination of columns, text, and fields.

  • Reference columns in a formula by their name columns['Column Name'] or order columns[1], with 1 being the first column.

  • To quickly add row numbers to every row in a table, see the Add row numbers to make joining tables easier below.

    💡   Tip: Use a JavaScript editor like https://repl.it/languages/javascript to write up the formula, then copy and paste the code over.

• Output table name

  • Enter the name of the field in which the new table should be saved.

What will this output?

The expected output for this action is a new data table with any row formulas applied. The name of the output field will be the Output table name defined during configuration.

Since row formulas will automatically fill all rows in a column, a new table is always created. This action will never edit the source data table.

Output fields for this action

• Output table name

  • The data table ID for the newly created data table.