Field: Field Formulas

Use this action to work with Javascript in Catalytic. You can use this action like how you would use formulas in Excel. There are many useful and simple formulas, and also many complex formulas. If you’re comfortable with Javascript, you can add many new capabilities with field formulas.

⚠️ Heads-up: Field: Field formulas is an advanced action that depends on Javascript.

Use case

Advanced calculations and parsing can be done with the Field: Field Formulas action. This action can be used to:

  • Perform complex calculations on table values, such as those that involve exponents, logarithms, trigonometry, etc.
  • Parse text and extract specific information from a customer service request email
  • Calculate whether an invoice amount is above or below the required threshold for review

How field formulas work

Here is an example of how a field formula works. This examples looks at generating a random number. All it takes to configure this action is some Javascript code.

screen readers look here

Set the Javascript code to calculate fields field to result = Math.random() to generate a random decimal between 0 and 1, such as 0.44637348688935763 or 0.8944293544406741.

Field formulas are commonly used for math calculations like this, but for basic calculations, use the Numbers: Perform basic math action for easier setup.

This action only outputs into text field type. For example, if this action performs a calculation and outputs the integer 300, the output field is not an integer field. Using a text field as input for an action that requires an integer will create a fix task. See What happens if an input is formatted incorrectly for more information.

How to configure this action

Fields for this action

  • Javascript code to calculate fields

    • Enter javascript directly into this field. See Examples below.
  • Return field name

    • Name the field to store your data. Choose any text to name the output of this action, can be used later in {{field-names}} format to reference resulting field

Field formula structure & components

  • Formulas typically start with result = for calculations and if for flow control based on whether a statement is true or false.
    • If structure: if (condition-1) { result = do.something } else (condition-2) { result = do.something.else }
  • When using an If structure, always consider whether you can use task conditions or field conditions to accomplish the same goal.
  • Variables can be abbreviated in the beginning of a field formula to reference a long string several times throughout a formula. This helps with consistency, ease of editing and reuse of the formula. Example: var a = “this-is-the-long-string” allows replacement of this-is-the-long-string with a throughout the field formula
  • Always important to determine whether variables are text vs number as many formulas will have a different outcome based on format
  • When using quotes within quotes for a literal string of text, use \ to escape the inside quotes. Example: “I like to read \“Harry Potter\”. It is a great book!”
  • Basic rules for special characters within field formulas:
    • [] used for fields and arrays
    • {} used for results
    • . used to connect fields to actions/methods

Completed general examples

All items below enclosed in ** are to be replaced with the specific value or field name within your Workflow using the {{field-names}} format, but surrounded in "", not {{}}. So the field name {{my-results}} would be added to the syntax as "my-results"

💡 Tip: Identify potential roadblocks before using the following general examples: The following examples may not fit your specific business need and often require significant modifications. Before adding or relying on any of these examples, have a conversation with a team member who understands Javascript to determine if this is the right direction for your Workflow.

Business Need Example
Search the body field for any of these words (regardless of case): warranty, return, replacement result = !!fields.body.match(/**warranty|return|replacement**/gi)
Locate purchase order number (for example “2017-04-000002129”) from a long string of text result = fields["**ocr-output**"].match(/\d{4}-\d{2}-\d{9}/g)[0]
Convert email address domain to web URL result = 'www.'+fields.**column2**.slice((fields.**column2**.indexOf( '@’ )+1),250)
Analyze sentiment score (from Sentiment: Calculate Score task) if ((fields['magnitude'] >= .5) && (fields['sentiment'] >= .2)) { result = 'positive' } else if ((fields['magnitude'] >= .5) && (fields['sentiment'] <= -.2)) { result = 'negative' } else if ((fields['magnitude'] < .5 ) && (fields['sentiment'] > -.2 ) && (fields['sentiment'] < .2)) { result = 'neutral' } else if ((fields['magnitude'] > .5 ) && (fields['sentiment'] > -.2 ) && (fields['sentiment'] < .2)) { result = 'neutral' } else if (((fields['magnitude'] < .5 ) && (fields['sentiment'] <= -.2 )) || ((fields['magnitude'] < .5 ) && (fields['sentiment'] >= .2))) { result = 'mixed' }
Get fiscal year based on fiscal year start and end dates if (new Date(fields["**date**"]) >= new Date('**1/1/2017**') && new Date(fields["**date**"]) <= new Date('**12/31/2018**')) { result = '**2017**'; } else if (new Date(fields["**date**"]) >= new Date('**7/1/2018**') && new Date(fields["**date**"]) <= new Date('**6/30/2019**')) { result = '**2018**'; } else { result = 'none'; }
Pull “last name” from a “full name” input="{{**fullname**}}" fullname = input.split(',').reverse().join(' ').trim().replace(/[^\w\s-']/g, '') splitname = fullname.split(' ') lastname = splitname.slice(-1).join(' ') result=lastname
Pull “first name” from a “full name” input="{{**fullname**}}"fullname = input.split(',').reverse().join(' ').trim().replace(/[^\w\s-']/g, '') splitname = fullname.split(' ') firstname = splitname.slice(0, -1).join(' ') result=firstname
Create URL link to the Workflow instance result = "https://" + run['teamName'] + ".pushbot.com/pushbots/" + run['runID']
Convert a “Choose Multiple” field to allow for use within an “Excel: Apply Filter” task result = fields['**field-name**'].join(' || ')
Currency format (ex. $1000.00) result = "$" + fields['**amount**'].toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",")
Convert a “string” of text to a “number” format result = Number("**text-string**")
Convert a “number” to a “string” of text result = fields["**number**"].toLocaleString()

Field formulas operators and logic syntax

The formulas below will return True or False result. For the examples below, assume:

  • fields['bignumber'] is equal to 100
  • fields['smallnumber'] is equal to 3
  • fields['truevalue'] is equal to True
  • fields['nullvalue'] is empty or null
Formula Syntax Example
Greater than > result = fields['bignumber'] > fields['smallnumber'] Returns True
Greater than or equal to >= result = fields['bignumber']>= 10] Returns True
Less than < result = fields['smallnumber']< fields['bignumber'] Returns True
Less than or equal to <= result = fields['smallnumber']<= 10 Returns True
Equal to === result = fields['bignumber']=== fields['smallnumber'] Returns False
Not equal to !== result = fields['bignumber']!== 10 Returns True
And && result = (fields['smallnumber'] < 10) && (fields['bignumber'] >= 10) Returns True, because both of the formulas are true
Or || result = (fields['smallnumber'] > 10) || (fields['bignumber'] > 10) Returns True, because at least one of the two formulas are true
Not ! result = !(fields['truevalue']) Returns False
Convert to true/false !! result = !!fields['bignumber'] Returns True result = !!fields['nullvalue'] Returns False

Math formula syntax

The formulas below will return a number result. For the examples below, assume:

  • fields['bignumber'] is equal to 100
  • fields['smallnumber'] is equal to 3
Formula Syntax Example
Addition + result = fields['bignumber'] + 10 Returns 110
Subtraction - result = fields['bignumber'] - fields['smallnumber'] Returns 97
Multiplication * result = fields['smallnumber'] * 10 Returns 30
Division / result = fields['bignumber'] / fields['smallnumber'] Returns 33.333333333
Minimum value Math.min() result = Math.min(fields['smallnumber'], fields['bignumber'], 10) Returns 3
Maximum value Math.max() result = Math.max(fields['smallnumber'], fields['bignumber'], 10) Returns 100
Minimum date value, subtracting days Math.min() result =,new Date(Math.min(new Date(''), new Date('')) - '' * 86400000).toISOString();
Maximum date value, adding days Math.max() result =,new Date(Math.max(new Date(''), new Date('')) + '' * 86400000).toISOString();
Random number Math.random() result = Math.random() Returns a random number between 0 and 1, such as .4129352301 result = Math.random() * 10 Returns a random number between 0 and 10, such as 4.129352301
Round Math.round() result = Math.round(9.95) Returns 10 result = Math.round(fields['bignumber'] / fields['smallnumber']) Returns 33
Round down Math.floor() result = Math.floor(9.95) Returns 9

Use parentheses to control the order of operation. In the example below, if fields['mynumber'] is equal to 5, the result is 25. This is because multiplication takes place before addition.

result = fields['mynumber'] + 10 * 2

With parentheses, the result is 30.

result = (fields['mynumber'] + 10) * 2

Text formula syntax

For the examples below, assume:

  • fields['professor-name'] is equal to Maya Sanchez
Formula Syntax Example
Left side of text (based on fixed # of characters) .slice(0,#) fields['professor-name'].slice(0,3) Returns Maya, because it returns characters from the text field starting with the first character as 0 and then counting up to character number 3 (which is actually the fourth character) fields[‘professor-name’].slice(0,1) Returns Ma, because it returns characters from the text field starting with the first character as 0 and then counting up to character number 1 (which is actually the second character)
Left side of text (searching for specific text) .split("text")[0] fields['professor-name'].split(" Sanchez")[0] Returns Maya, because it is the entire portion of the text field to the left of “ Sanchez”
Right side of text (based on fixed # of characters) .slice(-#) fields['professor-name'].slice(-5) Returns Sanchez, because it returns characters from the text field starting from the last character and counting backward. fields['professor-name'].slice(-1) Returns z
Right side of text (searching for specific text) .split("text")[1] fields['professor-name'].split("May")[1] Returns a Sanchez, because it is the entire portion of the text field to the right of “May”
Middle portion of text .slice(#,#) fields['professor-name'].slice(2,3) Returns ya fields['professor-name'].slice(5,7) Returns San
Concatenate text + fields['professor-name'] + ' PhD’ Returns Maya Sanchez PhD
Find text .indexOf('text') fields['professor-name'].indexOf('Sanchez') Returns 5 fields['professor-name'].indexOf('M') Returns 0
Match text .match(/text/g) fields['professor-name'].match(/San/g) Returns San fields['professor-name'].match(/san/gi)[0] Returns San. The i on the end makes the search ignore case.
Replace Text .replace(/text/ig,”) Replaces Instance Field with text you input in place of “text.” Example to remove special characters and move all text to lower case: .replace(/[^a-z0-9_]/ig, '').toLowerCase()

Comment syntax

If you would like to add comments to your field formula, use the format below. A /* at the start, * for each line and */ at the end of the comments. All comments will be excluded from evaluation of the formula.

/* Enter your comments here
*Enter a second line of comments here
*Enter a third line here
*/

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.

Why is my formula not working as expected?

Check your formula and make sure you’re not using both straight single quotes and “smart” single quotes.

screen readers look here

For the highest compatibility, use only straight single quotes for sections of your formulas like fields['professor-name'], and .indexOf('text').

If you are using an Apple computer, Mac OS converts straight quotes into smart quotes by default. To turn this off, go to System Preferences > Keyboard > Text and uncheck Use smart quotes and dashes.

The output field from my formula is [object Object], what does that mean?

This means the output includes multiple JSON values. Try adjusting your formula to output single values.

What order do my field formulas run or execute in?

Each task can hold up to 5 formulas. However, all trigger at once, so you cannot use the output field from one in the next formula. You will need to create another task with a dependency on this task.