Formulas transform and reformat data as it moves through a Recipe. AppConnect formulas are whitelisted Ruby methods applied to one of four data types: String, Integer or number, Date or datetime, and Array/hash (list).
On this page:
- Formula mode
- The formula editor
- Conditionals with ternary syntax
- The safe navigation operator
- Formula reference
⚠️ Most formulas error and stop the job if they operate on a null. Nulls are expressed as nil in Ruby. The exceptions are present?, presence and blank?, which handle nulls safely. This is the single most common cause of a formula failing at runtime rather than at build time.
Not every Ruby method is supported. AppConnect uses a whitelist, and syntax generally matches standard Ruby. Where a formula you need is missing, it can be requested for addition to the whitelist.
Formula mode
Formula mode is set per field, and most input fields support it. Toggle it with the text/formula switch on the field; the field’s type icon changes to fx once active.
In text mode, text and pills map into a field and produce exactly what they look like. In formula mode, text has to be written with proper string syntax, because you are writing an expression rather than composing plain text.
For example: sending “Hi Madison,” to a new lead is plain text in text mode, but in formula mode the same message needs explicit string formatting.
The formula editor
The editor filters what it offers by the data type of the pill you are working with. Drop a string pill into a formula field and it prompts for a period, per Ruby syntax, then lists the string formulas. A date pill lists date formulas. With no pill in the field, it shows the general structure of formulas and starts suggesting as you type.
Selecting a formula auto-completes it into the field, with an explanation of what it does and its syntax.
Conditionals with ternary syntax
Formulas can be executed conditionally using Ruby’s ternary syntax, the shorthand for if-else:
condition ? expression1 : expression2
| Part | Behaviour |
|---|---|
| condition | A boolean expression evaluating to true or false. |
| expression1 | Returned if condition is true. |
| expression2 | Returned if condition is false. |
One example passes either Full name or First name into a Message field:
Full name.present? ? Full name : First name checks whether the Full name pill has a value, evaluating to true if it does. The second ? separates the condition from the expressions; the first ? belongs to .present? itself, and the second is preceded by a space and belongs to the ternary.
⚠️ A ternary fallback can still fail. If neither pill has a value and Message is a required input field, the job fails at that step. The fallback only helps if the fallback itself has data.
The safe navigation operator
&. checks whether input data is valid before operating on it. It returns null if the input is null or undefined, and otherwise applies the operation.
Input&.operation
-
Input- An input datapill, of any datatype. -
Operation- Applied to the input where the input is not null. Must be compatible with the input datatype.
This is the simpler alternative to a ternary for null handling. Converting a Created Date pill with to_date errors when the pill is null, which would otherwise need a ternary to work around. &. handles the same case in one expression.
Formula reference
Expand a section below to see its formulas.
String formulas
In Ruby, strings refer to sequences of text and characters.
AppConnect supports a variety of string formulas. Formulas in AppConnect are whitelisted Ruby methods, and therefore not all Ruby methods are supported. You can always reach out to us to add additional formulas to the whitelist. Syntax and functionality for these formulas are generally unchanged. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present? , presence and blank? .
In the examples below, we will look at some of the methods that can be used to manipulate a string of text, which in this case the input string is ‘Jean Marie’.
Conditionals
blank?
This formula checks the input string and returns true if it is an empty string or if it is null.
Input.blank?
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| "Any Value".blank? | false |
| 123.blank? | false |
| 0.blank? | false |
| "".blank? | true |
If the input is null or an empty string, the formula will return false. For any other data, it returns true.
is_not_true?
Converts a value to boolean and returns true if the value is not truthy.
Input.is_not_true?
- Input - An input number or a string.
| Formula | Result |
|---|---|
| 123.is_not_true? | false |
| "false".is_not_true? | false |
| 0.is_not_true? | true |
| "".is_not_true? | false |
| nil.is_not_true? | true |
Converts the input into a boolean and returns true if the value is not truthy. truthy vs falsey
is_true?
Converts a value to boolean and returns true if the value is truthy.
Input.is_true?
- Input - An input number or a string.
| Formula | Result |
|---|---|
| 123.is_true? | true |
| "false".is_true? | true |
| 0.is_true? | false |
| "".is_true? | false |
| nil.is_true? | false |
Converts the input into a boolean and returns true if the value is truthy. truthy vs falsey
present?
This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the function will return false.
Input.present?
- Input - An input datapill. It can be a string, number, date, or list datatype.
| Formula | Result |
|---|---|
| "Any Value".present? | true |
| 123.present? | true |
| 0.present? | true |
| "2017-04-02T12:30:00.000000-07:00".present? | true |
| nil.present? | false |
| "".present? | false |
| [].present? | false |
If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true. Evaluating a list with nil values
presence
Returns the data if it exists, returns nil if it does not.
Input.presence
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| nil.presence | nil |
| "".presence | nil |
| "Any Value".presence | "Any Value" |
| 45.0.presence | 45.0 |
| 0.presence | 0 |
If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.
include?
Checks if the string contains a specific substring. Returns true if it does.
Input.include?(substring)
- Input - A string input. substring - The substring to check for.
| Formula | Result |
|---|---|
| "Partner account".include?("Partner") | true |
| "Partner account".include?("partner") | false |
This formula check is the string contains a specific substring. Returns true if it does, otherwise, returns false. This substring is case sensitive. This function acts in an opposite manner from exclude? . It will return true only if the input string contains the stated keyword.
exclude?
Checks if the string contains a specific substring. Returns false if it does.
Input.exclude?(substring)
- Input - A string input. substring - The substring to check for.
| Formula | Result |
|---|---|
| "Partner account".exclude?("Partner") | false |
| "Partner account".exclude?("partner") | true |
This formula check is the string contains a specific substring. Returns false if it does, otherwise, returns true. This substring is case sensitive. This function acts in an opposite manner from include? . It will return true only if the input string does NOT contain the stated keyword.
match?
Checks if the string contains a specific pattern. Returns true if it does.
Input.match?(pattern)
- Input - A string input. pattern - The pattern to check for.
| Formula | Result |
|---|---|
| "Jean Marie".match?(/Marie/) | true |
| "Jean Marie".match?(/ /) | true |
| "Partner account".match?(/partner/) | false |
This formula check is the string contains a specific pattern. Returns true if it does, otherwise, returns false.
ends_with?
Checks if the string ends with a specific substring. Returns true if it does.
Input.ends_with?(substring)
- Input - A string input. substring - The substring to check for.
| Formula | Result |
|---|---|
| "Jean Marie".ends_with?("rie") | true |
| "Jean Marie".ends_with?("RIE") | false |
| "Jean Marie".upcase.ends_with?("RIE") | true |
This formula check is the string ends with a specific substring. Returns true if it does, otherwise, returns false.
starts_with?
Checks if the string starts with a specific substring. Returns true if it does.
Input.starts_with?(substring)
- Input - A string input. substring - The substring to check for.
| Formula | Result |
|---|---|
| "Jean Marie".starts_with?("Jean") | true |
| "Jean Marie".starts_with?("JEAN") | false |
| "Jean Marie".upcase.starts_with?("JEAN") | true |
This formula check is the string starts with a specific substring. Returns true if it does, otherwise, returns false.
Text manipulation
parameterize
Replaces special characters in a string. Used when app does not accept non-standard characters.
Input.parameterize
- Input - An input string.
| Formula | Result |
|---|---|
| "öüâ".parameterize | "oua" |
This formula searches for special characters in the string and replaces them with standard characters. Used when app does not accept non-standard characters.
lstrip
This formula removes the white space at the beginning of the input string.
String.lstrip
- String - An input string.
| Formula | Result |
|---|---|
| " Test "..lstrip | "Test " |
This formula removes white spaces from the beginning of a string. If the string doesn’t have any white spaces before, the input string will be returned as is. Quicktip: Selectively remove white spaces
rstrip
This formula removes the white space at the end of the input string.
String.rstrip
- String - An input string.
| Formula | Result |
|---|---|
| " Test "..rstrip | " Test" |
This formula removes white spaces from the end of a string. If the string doesn’t have any white spaces at the end, the input string will be returned as is. Quicktip: Selectively remove white spaces
strip
This formula removes the white space at the beginning and the end of the input string.
String.strip
- String - An input string.
| Formula | Result |
|---|---|
| "Welcome to the future of automation! ".strip | "Welcome to the future of automation!" |
| " This is an example ".strip | "This is an example" |
This formula removes white spaces from both sides of a string. If the string doesn’t have any white spaces before or after, the input string will be returned as is. Quicktip: Selectively remove white spaces
strip_tags
This formula removes html tags embedded in a string.
String.strip_tags
- String - An input string.
| Formula | Result |
|---|---|
| "<p>Jean Marie</p>"..strip_tags | "Jean Marie" |
This formula check for html tags within the input string. It removes any html tags found and returns the string.
ljust
Aligns the string to left and pads with whitespace or pattern until string is specified length.
String.ljust(length,character)
- String - An input string. length - The length of the output string. character - (optional) The character to pad the string. If unspecified, the default pad character will be a blank space.
| Formula | Result |
|---|---|
| "test"..ljust(5) | "test " |
| "test"..ljust(10, "*") | "test******" |
rjust
Aligns the string to left and pads with whitespace or pattern until string is specified length.
String.rjust(length,character)
- String - An input string. length - The length of the output string. character - (optional) The character to pad the string. If unspecified, the default pad character will be a blank space.
| Formula | Result |
|---|---|
| "test"..rjust(5) | " test" |
| "test"..rjust(10, "*") | "******test" |
reverse
Inverts a string, reordering the characters in a backward manner. Case is preserved.
String.reverse
- String - An input string.
| Formula | Result |
|---|---|
| "Jean Marie".reverse | "eiraM naeJ" |
| " jean marie ".reverse | " eiram naej " |
gsub
Replace parts of a text string. Returns a new string with the replaced characters.
String.gsub(find,replace)
- String - An input string. You can use a datapill or a static string value. find - The string to look for. You can use a /pattern/ syntax. replace - The replacement string. You can define the replacement using a string or hash .
| Formula | Result |
|---|---|
| "I have a blue house and a blue car".gsub("blue", "red") | "I have a red house and a red car" |
| "Jean Marie".gsub("J", "M") | "Mean Marie" |
| "Jean Marie".downcase.gsub("j", "M") | "Mean marie" |
This formula works like find and replace. It takes two input parameters: The first input is the string that you want to replace. This is case-sensitive - so make sure to type correctly in either uppercase or lowercase to find all occurrences that are an exact match. The second input is the new string that will be used for replacing all occurrences of first input.
sub
Replaces the first occurrence of the first input value, with the second input value, within the string. This formula is case-sensitive - make sure to type in uppercase or lowercase before comparison if you are concerned about case sensitivity.
String.sub(find,replace)
- String - An input string. You can use a datapill or a static string value. find - The string to look for. You can use a /pattern/ syntax. replace - The replacement string. You can define the replacement using a string or hash .
| Formula | Result |
|---|---|
| "Mean Marie".sub(/M/, "J") | "Jean Marie" |
| "Hello".sub(/[aeiou]/, "*") | "H*llo" |
length
Returns the number of characters within an input string, including the white-spaces.\
String.length
- String - An input string.
| Formula | Result |
|---|---|
| "Jean Marie".length | 10 |
| " jean marie ".length | 12 |
slice
Returns a partial segment of a string.
String.slice(start,end)
- String - An input string. start - The index of the string to start returning. end - (optional) The number of characters to return. If unspecified, the formula will return only one character.
| Formula | Result |
|---|---|
| "Jean Marie".slice(0,3) | "Jea" |
| "Jean Marie".slice(5) | "M" |
| "Jean Marie".slice(3,3) | "n M" |
| "Jean Marie".slice(-5,5) | "Marie" |
The formula returns a partial segment of a string. It takes 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, negative numbers will be taken from the last character), the second parameter decides how many characters to return. If only the first parameter is passed in, only 1 character will be returned.
scan
Scan the string for the pattern to retrieve and return an array
String.scan(pattern)
- String - An input string. pattern - The pattern to search for.
| Formula | Result |
|---|---|
| "Thu, 01/23/2014".scan(/\d+/) | ["01","23","2014"] |
| "Thu, 01/23/2014".scan(/\d+/).join("-") | "01-23-2014" |
encode
Returns the string in a given encoding.
String.encode(encoding)
- String - An input string. encoding - Name of the encoding (e.g. Windows-1252).
| Formula |
|---|
| “Jean Marie”.encode(“Windows-1252”) |
transliterate
Replaces non-ASCII characters with an ASCII approximation, or if none exists, a replacement character which defaults to ‘?’.
String.transliterate
- String - An input string.
| Formula | Result |
|---|---|
| "Chloé".transliterate | "Chloe" |
Text case manipulation
capitalize
Converts the input string into sentence case, i.e. the first character of each sentence is capitalized.
String.capitalize
- String - An input string.
| Formula | Result |
|---|---|
| "ticket opened. Gold SLA".capitalize | "Ticket opened. gold sla" |
| "jean MARIE".capitalize | "Jean marie" |
titleize
Converts the input string into title case, i.e. the first character of each word is capitalized.
String.titleize
- String - An input string.
| Formula | Result |
|---|---|
| "ticket opened. Gold SLA".titleize | "Ticket Opened. Gold Sla" |
| "jean MARIE".titleize | "Jean Marie" |
upcase
Convert text to uppercase.
String.upcase
- String - An input string.
| Formula | Result |
|---|---|
| "Automation at it's FINEST!".upcase | "AUTOMATION AT IT'S FINEST!" |
| "Convert to UPCASE".upcase | "CONVERT TO UPCASE" |
This formula searches for any lowercase character and replace it with the uppercase characters. Quicktip: Search strings better with upcase
downcase
Convert text to lowercase.
String.downcase
- String - An input string.
| Formula | Result |
|---|---|
| "Automation at it's FINEST!".downcase | "automation at it's finest! |
| "Convert to DOWNCASE".downcase | "convert to downcase" |
This formula searches for any uppercase character and replace it with the lowercase characters. Quicktip: Search strings better with downcase
quote
Quotes a string, escaping any ’ (single quote) characters
String.quote
- String - An input string.
| Formula | Result |
|---|---|
| "Paula's Baked Goods".quote | "Paula''s Baked Goods" |
Converting to arrays and back
split
This formula divides a string around a specified character and returns an array of strings.
String.split(char)
- String - An input string value. You can use a datapill or a static value. char - (optional) The character at which to split the text. This is case sensitive. If no character is defined, then by default, strings are split by white spaces.
| Formula | Result |
|---|---|
| "Ms-Jean-Marie".split("-") | ["Ms", "Jean", "Marie"] |
| "Ms Jean Marie".split | ["Ms", "Jean", "Marie"] |
| "Split string".split() | ["Split", "string"] |
| "Split string".split("t") | ["Spli", " s", "ring"] |
| "01/23/2014".split("/") | ["01", "23", "2014"] |
| "01/23/2014".split("/").join("-") | "01-23-2014" |
This formula looks for the specified character in the input string. Every time it is found, the input will be split into a new string. Split character(s)
bytes
Returns an array of bytes for a given string.
String.bytes
- String - An input string.
| Formula | Result |
|---|---|
| "Hello".bytes | ["72","101","108","108","111"] |
Conversion formulas
Conversion of other data types to strings
to_s
Converts data to a string (text) datatype.
Input.to_s
- Input - Any input data. You can use number, array, object, or datetime datatypes.
| Formula | Result |
|---|---|
| -45.67.to_s | "-45.67" |
| "123".to_s | "123" |
| [1,2,3].to_s | "[1,2,3]" |
| {key: "AppConnect"}.to_s | "{:key=>"AppConnect"}"" |
| "2020-06-05T17:13:27.000000-07:00".to_s | "2020-06-05T17:13:27.000000-07:00" |
| "2020-06-05T17:13:27.000000-07:00".to_s(:short) | "05 Jun 17:13" |
| "2020-06-05T17:13:27.000000-07:00".to_s(:long) | "June 05, 2020 17:13" |
This formula returns a string representation of the input data. Quicktip: Output is a string datatype.
ordinalize
Turns a number into an ordinal string used to denote the position in an ordered sequence such as 1st, 2nd, 3rd, 4th.
Input.ordinalize
- Input - Any input number.
| Formula | Result |
|---|---|
| 1.ordinalize | "1st" |
| 2.ordinalize | "2nd" |
| 3.ordinalize | "3rd" |
| 1003.ordinalize | "1003rd" |
| -3.ordinalize | "-3rd" |
Conversion of strings to other data types
to_f
Converts data to a float (number) datatype.
Input.to_f
- Input - An number input data. You can use a string datatype or a integer datatype.
| Formula | Result |
|---|---|
| 45.to_f | 45.0 |
| -45.to_f | -45.0 |
| "45.67".to_f | 45.67 |
| "AppConnect".to_f | 0 |
This formula checks whether the input contains any numbers, if no numbers are found, it returns 0. If the number does not have a decimal point, .0 will be added the number.
to_i
Converts data to an integer (whole number) datatype.
Input.to_i
- Input - An number input data. You can use a string datatype or a float datatype.
| Formula | Result |
|---|---|
| 45.43.to_i | 45 |
| -45.43.to_i | -45 |
| "123".to_i | 123 |
| "AppConnect".to_i | 0 |
This formula checks whether the input contains any numbers, if no numbers are found, it returns 0. If the number has a decimal point, everything after the decimal will be omitted. Check for integers
to_country_alpha2
Convert alpha-3 country code or country name to alpha2 country code (first 2 initials).
Input.to_country_alpha2
- Input - Any input string.
| Formula | Result |
|---|---|
| "GBR".to_country_alpha2 | "GB" |
| "United Kingdom".to_country_alpha2 | "GB" |
to_country_alpha3
Convert alpha-2 country code or country name to alpha3 country code (first 3 initials).
Input.to_country_alpha3
- Input - Any input string.
| Formula | Result |
|---|---|
| "GBR".to_country_alpha3 | "GBR" |
| "United Kingdom".to_country_alpha3 | "GBR" |
to_currency
Formats integers/numbers to a currency-style.
Input.to_currency
- Input - Any input string.
| Formula | Description | Result |
|---|---|---|
| “345.60”.to_currency Ad | ds default currency symbol “$” “$3 | 45.60” |
to_currency_code
Convert alpha-2/3 country code or country name to ISO4217 currency code
Input.to_currency_code
- Input - Any input string.
| Formula | Result |
|---|---|
| "GBR".to_currency_code | "GBP" |
| "US".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.
Input.to_currency_name
- Input - Any input string.
| Formula | Result |
|---|---|
| "GBR".to_currency_code | "Pound" |
| "USD".to_currency_code | "Dollars" |
to_currency_symbol
Convert alpha-3 currency code or alpha-2/3 country code or country name to ISO4217 currency symbol.
Input.to_currency_symbol
- Input - Any input string.
| Formula | Result |
|---|---|
| "GBR".to_currency_symbol | "£" |
| "USD".to_currency_symbol | "$" |
to_phone
Converts string or number to a formatted phone number (user-defined).
Input.to_phone
- Input - Any input string or number.
| Formula | Result |
|---|---|
| "5551234".to_phone | 555-1234 |
| 1235551234.to_phone | 123-555-1234 |
| 1235551234.to_phone(area_code: true) | (123) 555-1234 |
| 1235551234.to_phone(delimiter: " ") | 123 555 1234 |
| 1235551234.to_phone(area_code: true, extension: 555) | (123) 555-1234 x 555 |
| 1235551234.to_phone(country_code: 1) | +1-123-555-1234 |
| "123a456".to_phone | 123a456 |
to_state_code
Convert state name to code.
Input.to_state_code
- Input - Any input string.
| Formula | Result |
|---|---|
| "California".to_state_code | CA |
to_state_name
Convert state code to name.
Input.to_state_name
- Input - Any input string.
| Formula | Result |
|---|---|
| "CA".to_state_name | CALIFORNIA |
bytesize
Returns the length of a given string in bytes.
Input.bytesize
- Input - Any input string.
| Formula | Result |
|---|---|
| "Hello".bytesize | 5 |
Integer and number formulas
In Ruby, Fixnum refers to integers, e.g. 9, 10, 11, while Float refers to decimals, e.g. 1.75.
AppConnect supports a variety of number formulas. Formulas in AppConnect are whitelisted Ruby methods, and therefore not all Ruby methods are supported. You can always reach out to us to add additional formulas to the whitelist. Syntax and functionality for these formulas are generally unchanged. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present? , presence and blank? .
In the cases of arithmetic operations, whether the values are of integer types or decimal (float) types are important. Formulas will alway stick to the types given as input, and the returned result will be of the most precise type.
Arithmetic operations
The add (+) operator
This operator allows the addition of operands on either side. This section talks about number arithmetics. Date arithmetics is possible as well.
| Formula | Result | Type |
|---|---|---|
| 4 + 7 | 11 | Fixnum |
| 4.0 + 7 | 11.0 | Float |
| 4.0 + 7.0 | 11.0 | Float |
The subtract (-) operator
This operator subtracts the right hand operand from the left hand operand. This section talks about number arithmetics. Date arithmetics is possible as well.
| Formula | Result | Type |
|---|---|---|
| 4 - 7 | -3 | Fixnum |
| 4.0 - 7 | -3.0 | Float |
| 4.0 - 7.0 | -3.0 | Float |
The multiply (*) operator
This operator multiplies the operands on either side.
| Formula | Result | Type |
|---|---|---|
| 4 * 7 | 28 | Fixnum |
| 4.0 * 7 | 28.0 | Float |
| 4.0 * 7.0 | 28.0 | Float |
The divide (/) operator
Divides left hand operand by right hand operand.
| Formula | Result | Type |
|---|---|---|
| 4 / 7 | 0 | Fixnum |
| 4.0 / 7 | 0.571428... | Float |
| 7 / 4 | 1 | Fixnum |
| 7 / 4.0 | 1.75 | Float |
| 7.0 / 4 | 1.75 | Float |
| 7.0 / 4.0 | 1.75 | Float |
The exponential (**) operator
Left hand operand to the power of the right hand operand.
| Formula | Result | Type |
|---|---|---|
| 5**3 | 125 | Fixnum |
| 4**1.5 | 8.0 | Float |
| 4.0**2 | 16.0 | Float |
| 3**-1 | “1/3” Ra | tional |
| 8**(3**-1) | 2.0 | Float |
| 7**-1.6 | 0.044447... | Float |
The modulo (%) operator
Divides left hand operand by right hand operand and returns the remainder.
| Formula | Result | Type |
|---|---|---|
| 4 % 7 | 4 | Fixnum |
| 4.0 % 7 | 4.0 | Float |
| 4 % 7.0 | 4.0 | Float |
| 7 % 4 | 3 | Fixnum |
| 7.0 % 4.0 | 3.0 | Float |
Other number formulas
abs
Returns the absolute (positive) value of a number.
number.abs
- number - An input integer or float.
| Formula | Result |
|---|---|
| 45.abs | 45 |
| -45.abs | 45 |
| 45.67.abs | 45.67 |
| -45.67.abs | 45.67 |
round
Rounds off a numerical value. This formula returns a value with a specified number of decimal places.
number.round(offset)
- number - An input integer or float. offset - (optional) The number of decimal places to return, you can provide negative values. If not specified, this formula will return the number with no decimal places.
| Formula | Result |
|---|---|
| 1234.567.round | 1234 |
| 1234.567.round(2) | 1234.56 |
| 1234.567.round(-2) | 1200 |
Conditionals
blank?
This formula checks the input and returns true if it is non a value number or if it is null.
Input.blank?
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| 123.blank? | false |
| 0.blank? | false |
| nil.blank? | true |
| "".blank? | true |
If the input is null or an empty string, the formula will return false. For any other data, it returns true.
even?
Checks the integer input and returns true if it is an even number.
integer.even?
- integer - An input integer.
| Formula | Result |
|---|---|
| 123.even? | false |
| 1234.even? | true |
odd?
Checks the integer input and returns true if it is an odd number.
integer.odd?
- integer - An input integer.
| Formula | Result |
|---|---|
| 123.odd? | true |
| 1234.odd? | false |
is_not_true?
Converts a value to boolean and returns true if the value is not truthy.
Input.is_not_true?
- Input - An input number or a string.
| Formula | Result |
|---|---|
| 123.is_not_true? | false |
| "false".is_not_true? | false |
| 0.is_not_true? | true |
| "".is_not_true? | false |
| nil.is_not_true? | true |
Converts the input into a boolean and returns true if the value is not truthy. truthy vs falsey
is_true?
Converts a value to boolean and returns true if the value is truthy.
Input.is_true?
- Input - An input number or a string.
| Formula | Result |
|---|---|
| 123.is_true? | true |
| "false".is_true? | true |
| 0.is_true? | false |
| "".is_true? | false |
| nil.is_true? | false |
Converts the input into a boolean and returns true if the value is truthy. truthy vs falsey
present?
This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the function will return false.
Input.present?
- Input - An input datapill. It can be a string, number, date, or list datatype.
| Formula | Result |
|---|---|
| "Any Value".present? | true |
| 123.present? | true |
| 0.present? | true |
| "2017-04-02T12:30:00.000000-07:00".present? | true |
| nil.present? | false |
| "".present? | false |
| [].present? | false |
If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true. Evaluating a list with nil values
presence
Returns the data if it exists, returns nil if it does not.
Input.presence
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| nil.presence | nil |
| "".presence | nil |
| "Any Value".presence | "Any Value" |
| 45.0.presence | 45.0 |
| 0.presence | 0 |
If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.
Conversions
ceil
Rounds the input number to the next greater integer or float. You can specify the precision of the decimal digits.
number.ceil(precision)
- number - An input integer or float. precision - (optional) The number of decimal places to return, you can provide negative values. If not specified, this formula will return the number with no decimal places.
| Formula | Result |
|---|---|
| 1234.567.ceil | 1235 |
| -1234.567.ceil | -1234 |
| 1234.567.ceil(2) | 1234.57 |
| 1234.567.ceil(-2) | 1300 |
floor
Rounds the input number to the next smaller integer or float. You can specify the precision of the decimal digits.
number.floor(precision)
- number - An input integer or float. precision - (optional) The number of decimal places to return, you can provide negative values. If not specified, this formula will return the number with no decimal places.
| Formula | Result |
|---|---|
| 1234.567.floor | 1234 |
| -1234.567.floor | -1235 |
| 1234.567.floor(2) | 1234.56 |
| 1234.567.floor(-2) | 1200 |
to_f
Converts data to a float (number) datatype.
Input.to_f
- Input - An number input data. You can use a string datatype or a integer datatype.
| Formula | Result |
|---|---|
| 45.to_f | 45.0 |
| -45.to_f | -45.0 |
| "45.67".to_f | 45.67 |
| "AppConnect".to_f | 0 |
This formula checks whether the input contains any numbers, if no numbers are found, it returns 0. If the number does not have a decimal point, .0 will be added the number.
to_i
Converts data to an integer (whole number) datatype.
Input.to_i
- Input - An number input data. You can use a string datatype or a float datatype.
| Formula | Result |
|---|---|
| 45.43.to_i | 45 |
| -45.43.to_i | -45 |
| "123".to_i | 123 |
| "AppConnect".to_i | 0 |
This formula checks whether the input contains any numbers, if no numbers are found, it returns 0. If the number has a decimal point, everything after the decimal will be omitted. Check for integers
to_s
Converts data to a string (text) datatype.
Input.to_s
- Input - Any input data. You can use number, array, object, or datetime datatypes.
| Formula | Result |
|---|---|
| -45.67.to_s | "-45.67" |
| "123".to_s | "123" |
| [1,2,3].to_s | "[1,2,3]" |
| {key: "AppConnect"}.to_s | "{:key=>"AppConnect"}"" |
| "2020-06-05T17:13:27.000000-07:00".to_s | "2020-06-05T17:13:27.000000-07:00" |
| "2020-06-05T17:13:27.000000-07:00".to_s(:short) | "05 Jun 17:13" |
| "2020-06-05T17:13:27.000000-07:00".to_s(:long) | "June 05, 2020 17:13" |
This formula returns a string representation of the input data. Quicktip: Output is a string datatype.
to_currency
Formats integers/numbers to a currency-style.
Input.to_currency
- Input - Any input string.
| Formula | Description | Result |
|---|---|---|
| “345.60”.to_currency Ad | ds default currency symbol “$” “$3 | 45.60” |
to_phone
Converts string or number to a formatted phone number (user-defined).
Input.to_phone
- Input - Any input string or number.
| Formula | Result |
|---|---|
| "5551234".to_phone | 555-1234 |
| 1235551234.to_phone | 123-555-1234 |
| 1235551234.to_phone(area_code: true) | (123) 555-1234 |
| 1235551234.to_phone(delimiter: " ") | 123 555 1234 |
| 1235551234.to_phone(area_code: true, extension: 555) | (123) 555-1234 x 555 |
| 1235551234.to_phone(country_code: 1) | +1-123-555-1234 |
| "123a456".to_phone | 123a456 |
Date and datetime formulas
AppConnect supports a variety of date and datetime formulas. Formulas in AppConnect are whitelisted Ruby methods, and therefore not all Ruby methods are supported. You can always reach out to us to add additional formulas to the whitelist.
Syntax and functionality for these formulas are generally unchanged. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present? , presence and blank? .
User settings within AppConnect cannot be edited to alter the time zone, although users can utilize formulas to change the output to different time zones as applicable
Basics
now
Returns the time and date at runtime in US Pacific Time Zone.
| Formula | Result |
|---|---|
| now | "2020-12-02 14:45:29 -0700" |
| now + 2.days | "2020-12-04 14:45:29 -0700" |
| now + 8.hours | "2020-12-02 22:45:29 -0700" |
The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the timestamp at which the step runs. Output datapill
today
Returns the date at runtime in US Pacific Time Zone.
| Formula | Result |
|---|---|
| today | "2020-12-02" |
| today + 2.days | "2020-12-04" |
| today + 8.hours | "2020-12-02 08:00:00 -0700" |
The formula calculates the timestamp when the a job is being processed. Each step using this formula will return the timestamp at which the step runs. Output datapill
from_now
Returns an future timestamp by a specified time duration. The timestamp is calculated at runtime.
Unit.from_now
- Unit - A time value to offset.
| Formula | Result |
|---|---|
| 2.months.from_now | "2021-02-04 14:45:29 -0700" |
| 3.days.from_now | "2020-12-07 14:45:29 -0700" |
| 30.seconds.from_now | "2020-12-04 15:15:29 -0700" |
The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs. Units
ago
Returns an earlier timestamp by a specified time duration. The timestamp is calculated at runtime.
Unit.ago
- Unit - A time value to offset.
| Formula | Result |
|---|---|
| 2.months.ago | "2020-10-04 14:45:29 -0700" |
| 3.days.ago | "2020-12-01 14:45:29 -0700" |
| 30.seconds.ago | "2020-12-04 14:15:29 -0700" |
The formula calculates the current timestamp and offsets by a specified time duration. This timestamp is calculated when the a job is being processed. Each step using this formula will return a timestamp for each step that runs. Units
wday
Returns day of the week. Sunday returns 0, monday returns 1.
Date.wday
- Date - A date or datetime datatype.
| Example | Result |
|---|---|
| today.wday | 4 |
| “01/12/2020”.to_date(format:“DD/MM/YYYY”).wday 2 |
The formula calculates the current day when the a job is being processed. The day of the week is converted into an integer output. Sunday = 0, monday = 1. Quickip: Convert to date datatype
yday
Returns day number of the year.
Date.yday
- Date - A date or datetime datatype.
| Example | Result |
|---|---|
| today.yday | 338 |
| “2020-01-01”.to_date(format:“YYYY-MM-DD”).yday 1 | |
| “2020-02-01”.to_date(format:“YYYY-MM-DD”).yday 32 |
The formula calculates the current day when the a job is being processed. The day of the year is converted into an integer output. Quickip: Convert to date datatype
yweek
Returns week number of the year.
Date.yweek
- Date - A date or datetime datatype.
| Example | Result |
|---|---|
| today.yweek | 49 |
| “2020-01-01”.to_date(format:“YYYY-MM-DD”).yweek 1 | |
| “2020-02-01”.to_date(format:“YYYY-MM-DD”).yweek 5 |
The formula calculates the current day when the a job is being processed. The week of the year is converted into an integer output. Quickip: Convert to date datatype
Getting first/last timestamp of the current/next periods
beginning_of_hour
Returns datetime for top-of-the-hour for a given datetime.
Datetime.beginning_of_hour
- Datetime - An input datetime.
| Formula | Result |
|---|---|
| today.to_time.beginning_of_hour | "2020-12-02T16:00:00.000000-07:00" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_hour | "2020-06-01T01:00:00.000000+00:00" |
| "2020-06-01".to_time.beginning_of_hour | "2020-06-01T00:00:00.000000+00:00" |
beginning_of_day
Returns datetime for midnight on date of a given date/datetime.
Date.beginning_of_day
- Date - An input date or datetime.
| Formula | Result |
|---|---|
| today.beginning_of_day | "2020-12-02T00:00:00.000000-07:00" |
| "2020-06-01".to_date.beginning_of_day | "2020-06-01T00:00:00.000000+00:00" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_day | "2020-06-01T00:00:00.000000+00:00" |
beginning_of_week
Returns date of the previous monday for a given date/datetime.
Date.beginning_of_week
- Date - An input date or datetime.
| Formula | Result |
|---|---|
| today.beginning_of_week | "2020-11-30T00:00:00.000000+00:00" |
| "2020-06-01".to_date.beginning_of_week | "2020-06-01T00:00:00.000000+00:00" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_week | "2020-06-01T00:00:00.000000+00:00" |
beginning_of_month
Returns first day of the month for a given date/datetime.
Date.beginning_of_month
- Date - An input date or datetime.
| Formula | Result |
|---|---|
| today.beginning_of_month | "2020-12-01T00:00:00.000000+00:00" |
| "2020-06-01".to_date.beginning_of_month | "2020-06-01T00:00:00.000000+00:00" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_month | "2020-06-01T00:00:00.000000+00:00" |
beginning_of_year
Returns first day of the year for a given date/datetime.
Date.beginning_of_year
- Date - An input date or datetime.
| Formula | Result |
|---|---|
| today.beginning_of_year | "2020-01-01T00:00:00.000000+00:00" |
| "2020-06-01".to_date.beginning_of_year | "2020-01-01T00:00:00.000000+00:00" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_year | "2020-01-01T00:00:00.000000+00:00" |
end_of_month
Returns last day of the month for a given date/datetime. This formula will return a date or datetime based on the input data.
Date.beginning_of_month
- Date - An input date or datetime.
| Formula | Result |
|---|---|
| today.beginning_of_month | "2020-12-31" |
| "2020-06-01".to_date.beginning_of_month | "2020-06-30" |
| "2020-06-01T01:30:45.000000+00:00".beginning_of_month | "2020-06-30T23:59:59.999999+00:00" |
Display conversion
strftime
Returns a datetime input as a user-defined string.
Date.strftime(format)
- Date - An input date or datetime. format - The format of the user-defined datetime written as a string.
| Formula | Result |
|---|---|
| "2020-06-05T17:13:27.000000-07:00".strftime("%Y/%m/%d") | "2020/06/05" |
| "2020-06-05T17:13:27.000000-07:00".strftime("%Y-%m-%dT%H:%M:%S%z") | "2020-06-05T17:13:27-0700" |
| "2020-06-05T17:13:27.000000-07:00".strftime("%B %e, %l:%M%p") | "June 5, 5:13PM" |
| "2020-06-05T17:13:27.000000-07:00".strftime("%A, %d %B %Y %k:%M") | "Friday, 05 June 2020 0:00" |
Allows the user to define a datetime format. Returns the datetime input in the specified format. Input datatype
in_time_zone
Converts a time value to a different timezone. This formula will return a date or datetime based on the input data.Syntax Date.in_time_zone(format)
| Formula | Result |
|---|---|
| today.in_time_zone | "2020-12-02" |
| today.to_time.in_time_zone("America/New_York") | "2020-12-01T20:00:00.000000-04:00" |
| "2020-06-01".to_time.in_time_zone | "2020-05-31T20:00:00.000000-04:00" |
| "2020-06-01T01:30:45.000000+00:00".in_time_zone | "2020-05-31T12:30:00.000000-05:00" |
This formula uses the list of timezone names from the IANA time zone database. The output will be an equivalent time in a different timezone.
dst?
Returns true if the input datatime is within Daylight Savings Time.
Datetime.dst?
- Datetime - An input date or datetime.
| Formula | Result |
|---|---|
| today.dst? | false |
| today.in_time_zone("America/New_York").dst? | true |
| "2020-06-01".in_time_zone("America/New_York").dst? | true |
| "2020-09-06T18:30:15.671720-05:00".dst? | true |
Converting datetime to date
to_date
This formula converts the input data into a date. Returns the date formatted as YYYY-MM-DD.
String.first(format: format)
- String - An input datetime or a string that describes a date or datetime. format - (optional) The date format of the input written as a string. If not specified, AppConnect will parse the input string automatically.
| Formula | Result |
|---|---|
| "23-01-2020 10:30PM".to_date(format: "DD-MM-YYYY") | "2020-01-23" |
| "01-23-2020 10:30PM".to_date(format: "MM-DD-YYYY") | "2020-01-23" |
| "2020/01/23".to_date(format: "YYYY/MM/DD") | "2020-01-23" |
Converts the input data into a date datatype. Input data best practice
to_time
Converts a string to an ISO timestamp. The response will use the UTC timezone (+00:00).
String.to_time
- String - An input string that describes a date or datetime.
| Formula | Result |
|---|---|
| "2020-04-02T12:30:30.462659-07:00".to_time | "2020-04-02T19:30:30.462659+00:00" |
| "2020-04-02".to_time | "2020-04-02T00:00:00.000000+00:00" |
Converts the input string into a datetime datatype. The output datetime will be converted to the UTC timezone (+00:00). Autofill time
Conditionals
blank?
This formula checks the input string and returns true if it is an empty string or if it is null.
Input.blank?
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| "Any Value".blank? | false |
| 123.blank? | false |
| 0.blank? | false |
| "".blank? | true |
If the input is null or an empty string, the formula will return false. For any other data, it returns true.
present?
This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the formula will return false.
Input.present?
- Input - An input datapill. It can be a string, number, date, or list datatype.
| Formula | Result |
|---|---|
| "Any Value".present? | true |
| 123.present? | true |
| 0.present? | true |
| "2017-04-02T12:30:00.000000-07:00".present? | true |
| nil.present? | false |
| "".present? | false |
| [].present? | false |
If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true. Evaluating a list with nil values
presence
Returns the data if it exists, returns nil if it does not.
Input.presence
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| nil.presence | nil |
| "".presence | nil |
| "Any Value".presence | "Any Value" |
| 45.0.presence | 45.0 |
| 0.presence | 0 |
If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.
Array, hash and list formulas
Arrays are ordered, integer-indexed collections of any object. List indexing starts at 0. Lists are the same as Ruby arrays, and we will be using lists and arrays interchangeably in this article.
Let’s take the example of a list with 4 list items: 100, 101, 102, 103. This list is expressed as:
As lists are ordered, we can use the following formula to get the values. AppConnect only supports retrieving up to the fifth item in the list:
Lists and hashes
When you work with formulas and repeating structures, there are 2 key data structures you need to understand: arrays (lists) and hashes. Take note that most formulas will return an error and stop the job if it tries to operate on nulls (expressed as nil in Ruby), except for present? , presence and blank? .
Formulas
Example list of hashes
The following is an example of a list of hashes called Contacts . This is the Contacts list in a table form:
first
This formula returns the first item in a list. It can also be used to return the first n items in a list. In this case, the output will be formatted as a list.
List.first(number)
- List - An input list. number - (optional) The number of items to retrieve from the list. If not specified, the formula will return only one item.
| Formula | Result |
|---|---|
| ["One","Two","Three","Four","Five"].first() | "One" |
| ["One","Two","Three","Four","Five"].first(2) | ["One","Two"] |
| [1,2,3,4,5].first() | 1 |
| [1,2,3,4,5].first(3) | [1,2,3] |
This formula returns the first n items from a list. If n is greater than one, the output is formatted as a list. If you are returning a single item (i.e. no arguments provided). The output will be formatted according to the item’s datatype.
last
This formula returns the last item in a list. It can also be used to return the last n items in a list. In this case, the output will be formatted as a list.
List.last(number)
- List - An input list. number - (optional) The number of items to retrieve from the list. If not specified, the formula will return only one item.
| Formula | Result |
|---|---|
| ["One","Two","Three","Four","Five"].last() | "Five" |
| ["One","Two","Three","Four","Five"].last(2) | ["Four","Five"] |
| [1,2,3,4,5].last() | 5 |
| [1,2,3,4,5].last(3) | [3,4,5] |
This formula returns the last n items from a list. If n is greater than one, the output is formatted as a list. If you are returning a single item (i.e. no arguments provided). The output will be formatted according to the item’s datatype.
index
Returns the index of the first item matching the given value.
Input.index(value)
- Input - An input list. value - The value to search for in the list.
| Formula | Result |
|---|---|
| [4, 5, 6, 7].index(6) | 2 |
| [4, 5, 6, 7].index(8) | nil |
where
Retrieves only the rows (hashes) which satisfy the specified WHERE condition. This formula accepts a single argument in the form of a hash with one or more key-value pairs. The default operand for the condition is equal to ( == ). This formula also supports the following operands. Operands should be added to the end of key separated by a space.
pluck
Retrieves only the columns which have been specified.
format_map
Create an array of strings by formatting each row of given array of hashes. Allows you to add static text to the created strings as well. Fields to be represented in the format %{ <field_name> }.
join
Combines all items in a list into a text string. A separator is placed between each item.
List.join(separator)
- List - An input of list datatype. separator - The character to add between items when they are joined. If no separator is specified, the list items will be joined together.
| Formula | Result |
|---|---|
| ["Ms", "Jean", "Marie"].join("-") | "Ms-Jean-Marie" |
| [1,2,3].join("--") | "1--2--3" |
| ["ab", "cd", "ef"].join | "abcdef" |
The list items are combined into a single text string. The seperator character(s) is added between each item. Seperator character
smart_join
Joins list elements into a string. Removes empty and nil values and trims any white space before joining.
List.smart_join(separator)
- List - An input of list datatype. separator - The character to add between items when they are joined. If no separator is specified, a blank space will be used as the joining character.
| Formula | Result |
|---|---|
| [nil, "", "Hello", " ", "World"].smart_join(" ") | "Hello World" |
| ["111 Vinewood Drive", "", "San Francisco", "CA", "95050"].smart_join(",") | "111 Vinewood Drive, San Francisco, CA, 95050" |
reverse
Reverses the order of a list.
List.reverse
- List - An input of list datatype.
| Formula | Result |
|---|---|
| ["Joe", "Jill", "Joan", "Jack"].reverse | ["Jack", "Joan", "Jill", "Joe"] |
| [100, 101, 102, 103].reverse | [103, 102, 101, 100] |
sum
For integers and decimals, the numbers will be added together and the total sum obtained. For strings, the strings will be concatenated together to form a longer string.
List.sum
- List - An input of list datatype.
| Formula | Result |
|---|---|
| [1, 2, 3].sum | 6 |
| [1.5, 2.5, 3].sum | 7.0 |
| ["abc", "xyz"].sum | "abcxyz" |
uniq
Returns a list containing unique items i.e. remove duplicate items.
List.uniq
- List - An input of list datatype.
| Formula | Result |
|---|---|
| ["joe", "jack", "jill", "joe", "jack"].uniq | ["joe","jack", "jill"] |
| [1, 2, 3, 1, 1, 3].uniq | [1, 2, 3] |
| [1.0, 1.5, 1.0].uniq | [1.0, 1.5] |
flatten
Flattens a multi-dimensional array (i.e. array of arrays) to a single dimension array.
List.flatten
- List - An input of list datatype.
| Formula | Result |
|---|---|
| [[1, 2, 3], [4, 5, 6]].flatten | [1, 2, 3, 4, 5, 6] |
| [[1, [2, 3], 3], [4, 5, 6]].flatten | [1, 2, 3, 3, 4, 5, 6] |
| [[1, [2, 3], 9], [9, 8, 7]].flatten | [1, 2, 3, 9, 9, 8, 7] |
length
Returns the number of elements in self. Returns 0 if the list is empty.
List.length
- List - An input of list datatype.
| Formula | Result |
|---|---|
| [ 1, 2, 3, 4, 5 ].length | 5 |
| [{..}, {..}, {..}].length | 3 |
| [" ", nil, "", nil].length | 4 |
| [].length | 0 |
max
Returns largest value in an array. When comparing numbers, the largest number is returned. When comparing strings, the string with the largest ASCII value is returned.
List.max
- List - An input of list datatype.
| Formula | Result |
|---|---|
| [-5, 0, 1, 2, 3, 4, 5].max | 5 |
| [-1.5, 1.5, 2, 3, 3.5].max | 3.5 |
| ["cat", "dog", "rat"].max | "rat" |
min
Returns smallest value in an array. When comparing numbers, the smallest number is returned. When comparing strings, the string with the smallest ASCII value is returned.
List.min
- List - An input of list datatype.
| Formula | Result |
|---|---|
| [-5, 0, 1, 2, 3, 4, 5].min | -5 |
| [-1.5, 1.5, 2, 3, 3.5].min | -1.5 |
| ["cat", "dog", "rat"].min | "cat" |
compact
Removes nil values from array and hash.
| Formula | Result |
|---|---|
| ["foo", nil, "bar"].compact | ["foo", "bar"] |
| { foo: 1, bar: nil, baz: 2 }.compact | { foo: 1, baz: 2 } |
Conditionals
blank?
This formula checks the input string and returns true if it is an empty string or if it is null.
Input.blank?
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| "Any Value".blank? | false |
| 123.blank? | false |
| 0.blank? | false |
| "".blank? | true |
If the input is null or an empty string, the formula will return false. For any other data, it returns true.
include?
Checks if the string contains a specific substring. Returns true if it does.
Input.include?(substring)
- Input - A string input. substring - The substring to check for.
| Formula | Result |
|---|---|
| "Partner account".include?("Partner") | true |
| "Partner account".include?("partner") | false |
This formula check is the string contains a specific substring. Returns true if it does, otherwise, returns false. This substring is case sensitive. This function acts in an opposite manner from exclude? . It will return true only if the input string contains the stated keyword.
present?
This formula will check the input and if there is a value present, it will return true. If the input is nil, an empty string or an empty list, the function will return false.
Input.present?
- Input - An input datapill. It can be a string, number, date, or list datatype.
| Formula | Result |
|---|---|
| "Any Value".present? | true |
| 123.present? | true |
| 0.present? | true |
| "2017-04-02T12:30:00.000000-07:00".present? | true |
| nil.present? | false |
| "".present? | false |
| [].present? | false |
If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true. Evaluating a list with nil values
presence
Returns the data if it exists, returns nil if it does not.
Input.presence
- Input - An input datapill. It can be a string, number, date, or datetime datatype.
| Formula | Result |
|---|---|
| nil.presence | nil |
| "".presence | nil |
| "Any Value".presence | "Any Value" |
| 45.0.presence | 45.0 |
| 0.presence | 0 |
If the input is null or an empty string, the formula will return nil. For any other data, it returns the orignal input data.
Conversion
to_csv
Generates CSV line from an array. This handles escaping. Nil values and empty strings will also be expressed within the csv line.
Input.to_csv
- Input - An input of list datatype.
| Formula | Result |
|---|---|
| ["John Smith", "No-Email", " ", nil, "555-1212"].to_csv | "John Smith,No-Email, ,,555-1212" |
| ["John Smith", "No-Email", " ", nil, 1212].to_csv | "John Smith,No-Email, ,,1212" |
to_json
Converts hash or array to JSON string.
Input.to_json
- Input - An input datapill. It can be a list or hash datatype.
| Formula | Result |
|---|---|
| {"pet" => "cat", "color" => "gray"}.to_json | {"pet":"cat","color":"gray"} |
| ["1","2","3"].to_json | ["1", "2", "3"] |
to_xml
Converts hash or array into XML string.
Input.to_xml
- Input - An input datapill. It can be a list or hash datatype.
| Formula | Result |
|---|---|
| {"name" => "Ken"}.to_xml(root: "user") | Ken |
| [{"name" => "Ken"}].to_xml(root: "users") | Ken |
from_xml
Converts XML string to hash.
Input.from_xml
- Input - Input XML data.
encode_www_form
Join hash into url-encoded string of parameters.
Input.encode_www_form
- Input - An input of hash datatype.
| Formula | Result |
|---|---|
| {"apple" => "red green", "2" => "3"}.encode_www_form | "apple=red+green&2=3" |
to_param
Returns a string representation for use as a URL query string.
Input.to_param
- Input - An input of hash datatype.
| Formula | Result |
|---|---|
| {name: 'Jake', age: '22'}.to_param | "name=Jake\&age=22" |
keys
Returns an array of keys from the input hash.
Input.keys
- Input - An input of hash datatype.
| Formula | Result |
|---|---|
| {"name" => 'Jake', "age" => '22'}.keys | ["name", "age"] |
Other formulas
null
Gives a null/nil value. Note: passing this into an input field will not update the field value as null. Use clear formula to update a field value to null. Remember to toggle the field to formula mode.
clear
Clears the value of the field in the target app to null/nil. Remember to toggle the field to formula mode. Use clear formula instead of null when looking to clear field in target app
uuid
Generates an UUID.
| Example | Result |
|---|---|
| uuid | “c52d735a-aee4-4d44-ba1e-bcfa3734f553” |
encrypt
Encrypts the input string with a secret key using AES-256-CBC algorithm. Encrypted output string is packed in RNCryptor V3 format and base64 encoded. Note: The encryption key should not be hard coded in the recipe. Use account properties (with key or password in the name) to store the encryption keys.
decrypt
Decrypts the encrypted input string with a secret key using AES-256-CBC algorithm. Encrypted input string should be packed in RNCryptor V3 format and base64 encoded. Note: The encryption key should not be hard coded in the recipe. Use account properties (with key or password in the name) to store the encryption keys.
encode_sha256
Encodes a string or binary array using SHA256 algorithm
encode_hex
Converts binary string to its hex representation
| Example | Result |
|---|---|
| “0101010101011010”.encode_hex “3 | 0313031303130313031303131303130” |
decode_hex
Decode hexadecimal into binary string
| Example | Result |
|---|---|
| “30313031303130313031303131303130”.decode_hex “0 | 101010101011010” |
encode_base64
Encode using Base64 algorithm
| Example | Result |
|---|---|
| “Hello World!”.encode_base64 “a | GVsbG8gd29ybGQh” |
decode_base64
Decode using Base64 algorithm
| Example | Result |
|---|---|
| “aGVsbG8gd29ybGQh”.decode_base64 “H | ello World!” |
encode_url
URL encode a string
| Example | Result |
|---|---|
| “Hello World”.encode_url “H | ello%20World” |
encode_urlsafe_base64
Encode using urlsafe modification of Base64 algorithm
| Example | Result |
|---|---|
| “Hello World”.encode_urlsafe_base64 “S | GVsbG8gV29ybGQ=” |
decode_urlsafe_base64
Decode using urlsafe modification of Base64 algorithm
| Example | Result |
|---|---|
| “SGVsbG8gV29ybGQ”.decode_urlsafe_base64 “H | ello World” |
as_string
Decode byte sequence as string in given encoding
| Example | Result |
|---|---|
| “SGVsbG8gV29ybGQ=”.decode_base64.as_string(‘utf-8’) “Hel | lo World” |
as_utf8
Decode byte sequence as UTF-8 string
| Example | Result |
|---|---|
| “SGVsbG8gV29ybGQ=”.decode_base64.as_utf8 “H | ello World” |
to_hex
Converts binary string to its hex representation
| Example | Result |
|---|---|
| “SGVsbG8gV29ybGQ=”.decode_base64.to_hex “4 | 8656c6c6f20576f726c64” |
SHA1
Encrypts a given string using the SHA1 encryption algorithm. Details here.
| Example | Result |
|---|---|
| “abcdef”.sha1.encode_base64 “H | 4rBDyPFtbwRZ72oS4M+XAV6d9I=” |
HMAC formulae
Creates a HMAC signatures with a variety of signing algorithms
| Signing algorithm | Example |
|---|---|
| SHA-256 | “username:password:nonce”.hmac_sha256(“key”) |
| SHA-1 | “username:password:nonce”.hmac_sha1(“key”) |
| SHA-512 | “username:password:nonce”.hmac_sha512(“key”) |
| MD5 | “username:password:nonce”.hmac_md5(“key”) |
md5_hexdigest
Accepts a string and creates message digest using the MD5 Message-Digest Algorithm
| Example | Result |
|---|---|
| “hello”.md5_hexdigest “5 | d41402abc4b2a76b9719d911017c592” |
jwt_encode_rs256
Creates JWT with RS256 - RSA using SHA-256 hash algorithm
| Example | Result |
|---|---|
| appconnect.jwt_encode_rs256({ name: “John Doe” }, “PEM key”) “eyJ | hbGciO...” |
parse_yaml
Parse a YAML string. Supports true, false, nil, numbers, strings, arrays, hashes
| Example | Result |
|---|---|
| appconnect.parse_yaml(“---\nfoo: bar”) “{ | “foo” => “bar” }” |
| appconnect.parse_yaml(“---\n- 1\n- 2\n- 3\n”) “ | [1, 2, 3]” |
render_yaml
Render an object into a YAML string.
| Example | Result |
|---|---|
| appconnect.render_yaml({ “foo” => “bar” }) “- | –\nfoo: bar\n” |
| appconnect.render_yaml([1,2,3]) | “---\n- 1\n- 2\n- 3\n” |
lookup
This formula allows you to lookup values from your AppConnect lookup tables via a key. It is case sensitive and data type sensitive. If you use a data pill in the lookup formula, it is recommended that the data is converted to the right format. For example, integer-type data pills should be converted to string with a .to_s formula if comparing to a column containing both integers and strings.
| Example | Result |
|---|---|
| lookup(‘Department Codes’, ‘Department code’: ‘ACC’)[‘Department’] “Account | ing” |
| lookup(‘Department Codes’, ‘Department code’: ‘SLS’)[‘Department’] “Sales” | |
| lookup(‘Department Codes’, ‘Department’: ‘Marketing’)[‘Department code’] “MKT” | |
| lookup(‘6’, ‘Department code’: ‘ACC’)[‘Department’] “Account | ing” #interchangeable lookup table name and ID |
| lookup(‘Department Codes’, ‘Department’: ‘marketing’)[‘Department code’] nil #cas | e sensitive value for “Marketing” |
| lookup(‘Department Codes’, ‘Department’: ‘Marketing’)[‘Department Code’] nil #cas | e sensitive value for column name “Department code” |
lookup_table
This formula allows you to create a static lookup table and define the keys and values. It is case sensitive and data type sensitive.
| Example | Result |
|---|---|
| {“key1” => “value1”, “key2” => “value2”, “key3” => “value3”}[“key2”] “value2” | |
| {“High” => “urgent”, “Medium” => “mid”, “Low” => “normal”}[“Low”] “normal” | |
| {“High” => “urgent”, “Medium” => “mid”, “Low” => “normal”}[“low”] nil | |
| {“High” => “urgent”, “Medium” => “mid”, “Low” => “normal”}[“normal”] nil | |
| {1 => “1”, 2 => “2”, 3 => “3”}[2] “2” | |
| {1 => “1”, 2 => “2”, 3 => “3”}[2.0] nil | |
| {1 => “1”, 2 => “2”, 3 => “3”}[“2”] nil |
Complex data types
Mapping complex data in formula mode
Benefits Deal with primitive arrays You may not have prior knowledge of schema Too many fields in a single object to map
Example
In the following example, we apply tags to Zendesk tickets for associated Escalations, which (in this example) we are tracking in Insightly as a custom object. For the purpose of demonstration, we will perform this using a custom action. According to Zendesk API documentation, tags should be sent as an array of strings. The payload should look like this: Now, this presents a problem for us, because AppConnect input field mapping exist primary as key/value pairs. This required format is a primitive array of strings. This requires a complex data type (Array of Strings).