Formula Mode: List of common formulas (operators) and how to use them : Workato Success Center

If you are new to formula mode, please click here to understand what it is and how to use it.

Operator

Explanation (scenario)

Examples

Output

ago

Goes back a specified period of time, with the base time as server time when job occurs.

Data types: Integer, Number

Taking date of job as 6/15/2015:

1.days.ago

1.months.ago

If [pill] is 3:

[pill].days.ago

[pill].months.ago

Taking date of job as 6/15/2015:

5/14/2015

5/15/2015

If [pill] is 3:

6/12/2015

3/15/2015

abs

Returns the absolute (positive) value of a number.

Data types: Integer

If [pill] is 3.142:

[pill].abs

If [pill] is -10.5:

[pill].abs

If [pill] is 3.142:

3.142

If [pill] is -10.5:

10.5

beginning_of_hour

Top-of-the-hour from a given timestamp. Returns timestamp.

Data types: Timestamp

If [pill] is 2016-07-19 10:45:30:

[pill].beginning_of_hour

If [pill] is 2016-07-19 10:45:30:

2016-07-19T10:00:00.000+00:00

beginning_of_day

Midnight on date of given date/timestamp. Returns timestamp.

Data types: Date, Timestamp

If [pill] is 07/12/2016 11:30AM: [pill].beginning_of_day

If [pill] is 07/12/2016 11:30AM:

2016-07-12 00:00:00 -0700

beginning_of_week

Start of week (Monday) for given date/timestamp. Returns date.

Data types: Date, Timestamp

If [pill] is 07/21/2016 11:30AM:

[pill].beginning_of_week

If [pill] is 07/21/2016 11:30AM:

2016-07-18

beginning_of_month

Start of month for given date/timestamp. Returns date.

Data types: Date, Timestamp

If [pill] is 07/16/2016 10:30AM:

[pill].beginning_of_month

If [pill] is 07/16/2016 10:30AM:

[pill].beginning_of_month

beginning_of_year

Start of year for given date/timestamp. Returns date.

Data types: Date, Timestamp

If [pill] is 07/12/2016 11:30AM:

[pill].beginning_of_year

If [pill] is 07/12/2016 11:30AM:

2016-01-01

blank?

Checks to see if [pill] is empty. If pill is empty, returns true. If pill is not empty, returns false.

Null values and blank spaces count as empty.

Data types: String, Date_time, Number, Boolean, Integer, Array

If [pill] is null:

[pill].blank?

If [pill] has an empty string “ ”:

[pill].blank?

If [pill] has a non empty string “ x”:

[pill].blank?

If [pill] is null:

true

If [pill] has an empty string “ ”:

true

If [pill] has a non empty string “ x”:

false

capitalize

Capitalizes the first letter of the entire string only

Data types: String

“Double Bubble”.capitalize

“Double. Bubble”.capitalize

“Double bubble”

“Double. bubble”

casecmp

(currently unavailable)

Compares 2 strings, and disregards case sensitivity.

Returns 0 if strings are identical, returns 1 if [pill] has a greater ASCII value, and returns -1 if [pill] has s smaller ASCII value.

If [pill] is the string “Double bubble”:

[pill].casecmp(“DOUBLE BUBBLE”)

[pill].casecmp(“DOUBLE UBBLE”)

[pill].casecmp(“DOUBLE BUBBLE BUBBLE”)

If [pill] is the string “Double bubble”:

-1

days

Units of measurement for dates. Can be used to add or subtract days from a date.

If [pill] is the date 6/15/2015:

[pill] + 1.days

[pill] + 1.months

[pill] - 12.months

If [pill] is the date 6/15/2015:

6/16/2015

7/15/2015

6/15/2014

downcase

Makes all letters in the string to be in the lowercase.

Data types: String

“Double bubble”.downcase

“DOUBLE bubble”.downcase

“double bubble”

encode_www_form

Joins hash into url-encoded string of parameters.

Data types: Hash

{"a" => "c d", "2" => "3"}.encode_www_form

"a=c+d&2=3"

ends_with?

Checks to see if a string ends with a certain string. This is case sensitive.

If it does, returns true. If it doesn’t, returns false.

Data types: String

If [pill] is “Double bubble”:

[pill].ends_with?(“bubble”)

[pill].ends_with?(“Bubble”)

[pill].ends_with?(“ubble”)

If [pill] is “Double bubble”:

true

false

true

exclude?

The opposite of include?. Searches and returns true if value is not found.

Data types: String

‘spectacular’.exclude?(“abc”)

‘spectacular’.exclude?(“spec”)

true

false

first

Returns the first item in a list. Can also be used to return the first n items in a list, as a list.

Split, join, first and last are powerful functions for manipulating strings. More information here.

If [list] = [item1, item2, item3, item4, item5]:

[list].first

[list].first(2)

If [list] = [item1, item2, item3, item4, item5]:

item1

[item1, item2]

flatten

Flattens a multi-dimensional array to simple array.
Data types: Array

[[1, 2, 3],[4,5,6]].flatten

[1, 2, 3, 4, 5, 6]

format_map

Data types: Array

from_now

Produces a value that is forward in time from that point in time. Needs to be combined with <value>.<time-unit>

Data types: Integer

If current time = March 18, 2016; 3pm (PST)

3.months.from_now

6.days.from_now

If current time = March 18, 2016; 3pm (PST)

2016-06-18 15:00:00 -0700

2016-03-24 15:00:00 -0700

gsub

Replaces a specific character in a string with another one.

Data types: String

‘Jean Marie’.gsub(‘J’, ‘M’)

‘Jean Marie’.gsub(‘e’, ‘i’)

"Awesome".gsub(/[Ae]/, 'A'=>'E', 'e' => 'a')

“Mean Marie”

“Jian Marii”

"Ewasoma"

humanize

(currently unavailable)

Converts a string to sentence case, i.e. capitalizes only the first letter.

‘the quick brown fox’.humanize

‘The Quick Brown Fox’.humanize

The quick brown fox

include?

Searches for a match within a string or an array. Returns true or false. Similar to match?.

Data types: String, Array

‘hahaha’.include?(“ha”)

‘spectacular’.include?(“ha”)

true

false

in_time_zone

Converts a time stamp to a different time zone.

Data types: Date, Timestamp
Uses the list of time zone names from the IANA time zone database. More information here.

"2016-07-14 18:45:56

-0700".to_time.in_time_zone("US/Eastern")

"2016-07-14 18:45:56

-0500"

join

Joins elements in a list by a specified character to form a string. If no character is defined, by default, elements will just be joined together.

Split, join, first and last are powerful functions for manipulating strings. More information here.

Data types: Array

If [list] = [1, 2, 3, 4, 5]:

[list].join

[list].join(“ ”)

[1, 2, 3, 4, 5].join(“, ”)

If [list] = [1, 2, 3, 4, 5]:

“12345”

“1 2 3 4 5”

“1, 2, 3, 4, 5”

last

Returns the last item in a list. Can also be used to return the last n items in a list, as a list.

Split, join, first and last are powerful functions for manipulating strings. More information here.

If [list] = [item1, item2, item3, item4, item5]:

[list].last

If [list] = [item1, item2, item3, item4, item5]:

item5

length

Returns the number of elements in a list.

Data types: String, Array

If [list] = [item1, item2, item3, item4, item5]:

[list].length

If [list] = [ ]:

[list].length

If [list] = [item1, item2, item3, item4, item5]:

If [list] = [ ]:

ljust

Aligns the string to the left. You will need to specify the length of the string as this will add spaces at the end of it.

Data types: String

“qwerty”.ljust(12)

“qwerty ”

lookup

Allows you to retrieve a corresponding value from a lookup table.

More information here.

For example, if this is a lookup table called country_table with corresponding columns - country and code, with two rows filled.
| Country | Code |
| United States | US |
| Canada | CA |

To get the corresponding country from a code:
lookup('country_table', code: 'US')['Country']

Note: You can replace the 'US' with a data pill that contains other codes values such as CA to retrieve the corresponding country

To get the corresponding country from a code:
United States

lstrip

Removes only leading white space in a string.

Data types: String

“ Double bubble ”.strip

“Double bubble”.strip

“Double bubble"

"Double bubble”

match?

Searches for a match within a string. Returns true or false. Similar to include?.

Data types: String

‘hahaha’.match?(/ha/)

If [email] = hello@foo.com

email.match?(/(foo.com)|(bar.com)|()/) ? doX : doY

( ) = " "

| = or

true

If [email] = hello@foo.com

doX

max

Returns the largest value in an array. See also min.
Data types: Array

If [array] = ["book", "apple", "cart"]:

[array].max

If [array] = ["book", "apple", "cart"]:

"cart"

min

Returns the smallest value in an array. See also max.
Data types: Array

If [array] = ["book", "apple", "cart"]:

[array].min

If [array] = ["book", "apple", "cart"]:

"book"

now

Returns the current time now.

parameterize

Replaces special characters in a string. Used when app does not accept non-standard characters.

Data types: String

"öüâ".parameterize

"oua"

pluck

Pluck field(s) from array of objects/hashes. Returns an array. See .join for combining an array into a normal string.

See here for additional help.

Data types: Array

If [TaxLine] contains an array of different Taxes with many fields:

[TaxLine].pluck(["TaxLineDetail", "TaxRateRef", "value"])

[TaxLine].pluck(["TaxLineDetail", "TaxRateRef", "value"]).join(",")

If [TaxLine] contains an array of different Taxes with many fields:

[8.75, 6.00, 8.49]

8.75,6.00,8.49

present?

Checks to see if [pill] is empty. If [pill] is not empty, returns true. If [pill] is empty, returns false.

Null values and blank spaces count as empty.

Data types: String, Date, Timestamp, Number, Boolean, Integer, Array

If [pill] is null:

[pill].present?

If [pill] has an empty string “ ”:

[pill].present?

If [pill] has a non empty string “ x”:

[pill].present?

If [pill] is null:

false

If [pill] has an empty string “ ”:

false

If [pill] has a non empty string “ x”:

true

presence

Checks to see if [pill] is null. If [pill] is null, returns null. If [pill] is not null, returns [pill].

Data types: String, Date, Timestamp, Number, Boolean, Integer, Array

If [pill] is null:

[pill].presence

If [pill] has an empty string “ ”:

[pill].presence

If [pill] has a non empty string “ x”:

[pill].presence

If [pill] is null:

null

If [pill] has an empty string “ ”:

“ ”

If [pill] has a non empty string “ x”:

“ x”

reverse

Reverses the order of lists or strings.

Data types: String, Array

If [list] = [item1, item2, item3, item4, item5]:

[list].reverse

“Double bubble”.reverse

If [list] = [item1, item2, item3, item4, item5]:

[item5, item4, item3, item2, item1]

“elbbub elbuoD”

rjust

Aligns the string to the right. You will need to specify the length of the string as this will add spaces at the start of it.

Data types: String

“qwerty”.rjust(12)

“ qwerty”

round

Rounds off a numerical value to a specified number of decimal points. Number specified after the formula.

Data types: Number

If [pill] = 114.3456

[pill].round

If [pill] = 141.9962

[pill].round(2)

If [pill] = 114.3456

114

If [pill] = 141.9962

142.00

rstrip

Removes only trailing white space in a string.

Data types: String

“ Double bubble ”.strip

“Double bubble”.strip

“ Double bubble”

“Double bubble”

scan

Scans the string for the pattern and returns an array.

Data types: String

"Thu, 01/23/2014".scan(/d+/).join("-")

01-23-2014

size

(currently unavailable)

Returns the number of characters in a string if used on strings, inclusive of white spaces.

“Double bubble”.size

“12345 6789”

slice

Returns a partial segment of a string. Pass in 2 parameters - the first parameter is the index that decides which part of the string to start returning from (first letter being 0 and subsequently progressing incrementally), the second parameter decides how many characters to return. If only the first parameter is passed in, only 1 character will be returned.

“Double bubble”.slice(0)

“Double bubble”.slice(0, 6)

“Double bubble”.slice(7)

“Double bubble”.slice(7, 6)

“D”

“Double”

“b”

“bubble”

smart_join

Joins array into string by removing empty and nil values. Also trims the white space before joining.

Data types: Array

If [pill] = [nil, “ ”, “Hello”, “ World”]

[pill].smart_join

If [pill] = [nil, “ ”, “hello”, “world”]

“Hello World”

split

Splits up a string based on certain characters. Character is case sensitive. If no character is defined, by default, strings are split up by white spaces.

Null values cannot be split. An error will be thrown.

Split, join, first and last are powerful functions for manipulating strings. More information here.

Data types: String

"Double bubble”.split

“Double bubble”.split(“b”)

“Double bubble”.split(“d”)

“Double bubble”.split(“D”)

[“Double”, “bubble”]

[“Dou”, “le ”, “”, “u”, “”, “le”]

“Double bubble”

[“”, “ouble bubble”]

starts_with?

Checks to see if a string starts with a certain string. This is case sensitive.

If it does, returns true. If it doesn’t, returns false.

Data types: String

If [pill] is "Double bubble":

[pill].starts_with?(“Double”)

[pill].starts_with?(“Do”)

[pill].starts_with?(“double”)

If [pill] is "Double bubble":

true

false

strftime

Takes a date/time string and formats it as defined.

%Y — Year with century

%m — Month with zero-prefix

%B — Full month name

%b — Abbreviated month name

%d — Day of the month with zero-prefix

%e — Day of the month without zero-prefix

%H — Hour of the day (24-hour)

%k — Hour of day without 0 prefix (24-hour)

%I (capital i) — Hour of the day (12-hour)

%l (lowercase L) — Hour of day without 0 prefix (12-hour)

%p — AM or PM

%M — Minute of the hour

%S — Second of the minute

%z — Time zone offset from UTC (e.g. -0700)

%Z — Time zone abbrev. name

Data types: Date

‘Date’.strftime(“%B %e,%l:%M%p“)

‘Date’.strftime(“%A, %d %B %Y %k:M“)

August 7, 7:00AM

Friday, 07 August 2015 7:00

strip

Removes leading and trailing white space in a string.

Data types: String

“ Double bubble ”.strip

“Double bubble”.strip

“Double bubble”

strip_tags

Removes HTML tags in a string.

Data types: String

"<html><body>Double bubble</body></html>"

"Double bubble"

sum

Sum items in an array.

Data types: Array

[array].pluck(:total).sum

titleize

Converts the first letter of each word in a string to uppercase.

Data types: String

“double bubble”.titleize

“DOUBLE BUBBLE”.titleize

“Double Bubble”

to_country_alpha2

Convert alpha-3 country code or country name to alpha2 country code (first 2 initials).

Data types: String

‘GBR’.to_country_alpha2

‘United Kingdom’.to_country_alpha2

to_country_alpha3

Convert alpha-2 country code or country name to alpha3 country code (first 3 initials).

Data types: String

‘GB’. to_country_alpha3

‘United Kingdom’. to_country_alpha3

GBR

to_country_name

Convert alpha-2/3 country code or country name to ISO3166 country name.

Data types: String

‘GB’. to_country_name

‘GBR’. to_country_name

United Kingdom

to_country_number

(currently unavailable)

Converts country to its country number.

to_csv

Generates CSV line from an array. This handles escaping and nil values.
Data types: Array

["John Smith","No-Email", "555-1212"].to_csv

"John Smith,No-Email,555-1212"

to_currency

Formats integers/numbers to a currency-style. You may optionally modify it with other parameters.
Data types: String, Number

1234567890.50.to_currency

"1234567890.506".to_currency

1234567890.506.to_currency(precision: 3)

1234567890.506.to_currency(locale: :fr)

$1,234,567,890.50

$1,234,567,890.51

$1,234,567,890.506 1

234 567 890,51 €

to_currency_code

Convert alpha-2/3 country code or country name to ISO4217 currency code

Data types: String

‘USA’.to_currency_code

USD

to_currency_name

Convert alpha-3 currency code or alpha-2/3 country code or country name to ISO4217 currency name.

Data types: String

‘USA’.to_currency_name

Dollars

to_currency_symbol

Convert alpha-3 currency code or alpha-2/3 country code or country name to ISO4217 currency symbol.

Data types: String

‘USA’.to_currency_name

to_date

Converts string or timestamp to date format.
Data types: String, Timestamp

‘20150420’.to_date

‘04/20/2015’.to_date(format: ‘MM/DD/YYYY’)

‘04-20-2015’.to_date(format: ‘MM-DD-YYYY’)

‘Mon, 20 Apr 2015’.to_date(format: ‘%a, %d %b %Y’)
Refer to strftime for manipulating the % values.

‘20150420’.to_date + 2.days

‘20150420’.to_date - 2.weeks

‘20150420’.to_date + 2.years

Mon, 20 Apr 2015

Mon, 20 Apr 2015

Mon, 20 Apr 2015

Mon, 20 Apr 2015

Wed, 22 Apr 2015

Mon, 06 Apr 2015

Thu, 20 Apr 2017

to_f

Changes the type of a variable to a float.

Data types: String, Number, Timestamp

If [pill] is “Double bubble”:

[pill].to_f

“Double123”.to_f

“123”.to_f

“123bubble321”.to_f

123.555.to_f

If [pill] is “Double bubble”:

123.0

123.555

to_i

Changes the type of a variable to an integer.

Converts strings into the value 0.

Data types: String, Number, Timestamp

If [pill] is “Double bubble”:

[pill].to_i

“Double123”.to_i

“123”.to_i

“123bubble321”.to_i

If [pill] is “Double bubble”:

123

to_json

Converts hash or array to JSON string.

Data types: Hash, Array

If [hash] is {"pet" => "cat", "color" => "gray"}:

[hash].to_json

If [array] is ["Array","1","2","3"]:

[array].to_json

If [hash] is {"pet" => "cat", "color" => "gray"}:

{"pet":"cat","color":"gray"}

If [array] is ["Array","1","2","3"]:

["Array","1","2","3"]

to_param

Returns a string representation for use as a URL query string.

Data types: String, Array, Hash

{name: 'Jake', age: '22'}.to_param

name=Jake&age=22

to_phone

Converts string or number to a formatted phone number (user-defined).

Data types: String, Number, Integer

5551234.to_phone

"5551234".to_phone

1235551234.to_phone

1235551234.to_phone(area_code: true)

1235551234.to_phone(delimiter: " ")

1235551234.to_phone(area_code: true, extension: 555)

1235551234.to_phone(country_code: 1)

"123a456".to_phone

555-1234
555-1234
123-555-1234
(123) 555-1234
123 555 1234
(123) 555-1234 x 555
+1-123-555-1234

123a456

to_s

Changes the type of a variable to a string.

Data types: String, Number, Timestamp

If [pill] is “Double bubble”:

[pill].to_s

123.to_s

If [pill] is “Double bubble”:

“Double bubble”

“123"

to_state_code

Converts state name to code

Data types: String

"California".to_state_code

"CA"

to_state_name

Converts code name to state name

Data types: String

"CA".to_state_name

"CALIFORNIA"

to_time

Converts string or date to timestamp

Data types: String, Date

to_sym

(currently unavailable)

today

Returns the date and day when the recipe job runs.

uniq

Return unique items in an array.
Data types: Array

[array].pluck(:email).uniq

["array", "items", "array", "array", "items"].uniq

-

["array","items"]

upcase

Makes all letters in the string to be in the uppercase.

Data types: String

If [pill] is “Double bubble”:

[pill].upcase

“DOUBLE bubble”.upcase

If [pill] is “Double bubble”:

“DOUBLE BUBBLE”

“DOUBLE BUBBLE"

utc

Converts time/date to UTC (+0).

Data types: Date, Timestamp

[Last Modified Date].utc

2016-06-22 17:51:49 UTC

wday

Returns day of the week (numbering starts with Monday)

Data types: Date, Timestamp

If [pill] is “Tues, 12 July 2016”:

[pill].wday

If [pill] is “Tues, 12 July 2016”:

where

Filter array by given condition. Used in conjunction with other functions.

See here for additional help.

Data types: Array

If [pill] = List of all users with location and email address
[pill].where('country': 'US').pluck("email").join(",")

If [pill] = List of all users with location and email address
hello@workato.com,
support@workato.com,
partners@workato.com

yday

Returns the day number of the year

Data types: Date, Timestamp

If [pill] is “Mon, 5 Oct 2015”:

[pill].yday

If [pill] is “Mon, 5 Oct 2015”:

278

yweek

Returns the week number of the year

Data types: Date, Timestamp

If [pill] is “Mon, 5 Oct 2015”:

[pill].yweek

If [pill] is “Mon, 5 Oct 2015”:

Looking for a regular expression (regex) tutorial?

Formula Mode: List of common formulas (operators) and how to use them

Related Articles

Still can't find your solution?