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”: 0 1 -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” “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]: 5 If [list] = [ ]: 0 |
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” | 13 10 |
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 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” “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” “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 | GB GB |
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 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 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”: 0 0 123.0 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”: 0 0 123 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”: 2 |
| 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”: 41 |
List of common formulas (operators) and how to use them
Created by: Allan Teng
Modified on: Thu, Aug 29, 2019 at 5:40 AM
A
Did you find it helpful? Yes No
Send feedbackSorry we couldn't be helpful. Help us improve this article with your feedback.
Related Articles
Still can't find your solution?
Visit our forums to search for answers, or post your own questions.