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 dropdown 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 dropdown to ID then enter the ID manually. Learn more.
 Select a table from a list of all tables available on your team. The list only includes tables you have permission to view.

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 ordercolumns[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
Was this page helpful?
Thanks for the feedback! If you have any questions, be sure to ask on the Catalytic Community.