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鈥檛 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.

Fruit Price Quantity Total Formatted Total
Apple 10 5
Banana 40 8
Pears, Bananas 20 2

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鈥檛 be added with this action. Here are some example row formulas:

Example row formula Row formula effect Result
columns['total'] = columns[2] * columns[3] Set the Total column to Price (col2) * Quantity (col3) 50
columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00' Set the Formatted Total column to Price (col2) * Quantity (col3) and prepend $ and append .00 $50.00
columns['total'] = columns['total'].replace(/,/ig, "") Remove all commas from the Total column. Pears Bananas
columns['total'] = columns[2] * columns[3];columns['Formatted Total'] = '$' + columns[2] * columns[3] + '.00 Update the Total and Formatted Total columns in one row formula 50, $50.00

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:

Fruit Price Quantity Total Formatted Total
Apple 10 5 50 $50.00
Banana 40 8 320 $320.00
Pears, Bananas 20 2 40 $40.00

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
Row Number Fruit Price
1 Apple 10
2 Banana 40
3 Pear 20

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 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 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[鈥楥olumn 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.