In addition to the basic data types (for example, string and integer), you may encounter more complex data structures that contain information about multiple items or multiple pieces of information about a single item. You may encounter Lists, Hashes, or Lists of Hashes. Formulas in Hive Automate are whitelisted Ruby methods, and therefore not all Ruby methods are supported.

You can refer to the syntax and sample uses of these commands by clicking the links below:


Lists (arrays)

Arrays are ordered, integer-indexed collections of any object. List indexing starts at 0. Lists and arrays refer to the same data structure.

In the example below, a list of integers is expressed as:

number_list = [100, 101, 102, 103, 104]

As lists are ordered, we can use the following formula to get the values. Hive Automate only supports retrieving up to the fifth item using this syntax.

Formula

Result

number_list.first

100

number_list.second

101

number_list.third

102

number_list.fourth

103

number_list.fifth

104

number_list.last

104

We can also use indexes to get corresponding values. Remember, indexes start at 0:

Formula

Result

number_list[0]

100

number_list[1]

101

number_list[2]

102

number_list[3]

103

Lists in Ruby supports negative indexes.

Formula

Result

number_list[-1]

104

number_list[-2]

103

number_list[-3]

102

number_list[-4]

101

Lists also support ranges as indexes. This returns another list, instead of returning only a value.

Formula

Result

number_list[0..2]

[100, 101, 102]

number_list[-3..-1]

[102, 103, 104]

number_list[0..-2]

[100, 101, 102, 103]


Hashes

A hash is a dictionary-like collection of unique keys and their values. They are similar to Lists, but where a List uses integers as its index, a Hash allows you to use any object type. Hashes enumerate their values in the order that the corresponding keys were inserted.

Let's take the example of a hash with 2 values, with 'Acme widgets' and 10 as the values of item_name and item_quantity respectively.

line_item = { 'item_name' => 'Acme widgets', 'item_qty' => 10 }

Formula

Result

line_item["item_name"]

"Acme widgets"

line_item["item_qty"]

10


Lists of hashes

Here is an example of an invoice which has multiple line items. It is represented as a list of hashes.

line_items = [ { 'item_no': 1, 'item_name' => 'Acme widgets', 'item_qty' => 10 }, { 'item_no': 2, 'item_name' => 'RR bearings', 'item_qty' => 99 }, { 'item_no': 3, 'item_name' => 'Coyote tyres', 'item_qty' => 7 } ]


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:

name

email

state

company

company_rev

Joe

joe@abc.com

CA

ABC

1000

Jill

jill@nbc.com

MA

NBC

1000

Joan

joan@nbc.com

MA

NBC

10000

Jack

jack@hbo.com

CA

HBO

30000

This is the Contacts list in a list of hashes form.

[ 
{
'name' => 'Joe', 'email' => 'joe@abc.com', 'state' => 'CA', 'company' => 'ABC', 'company_rev' => 1000, 'description' => { 'summary' => 'First time buyer', 'estimated_value' => 300 }
},
{
'name' => 'Jill', 'email' => 'jill@nbc.com', 'state' => 'MA', 'company' => 'NBC', 'company_rev' => 1000, 'description' => { 'summary' => 'Referral', 'estimated_value' => 500 }
},
{
'name' => 'Joan', 'email' => 'joan@nbc.com', 'state' => 'MA', 'company' => 'NBC', 'company_rev' => 10000, 'description' => { 'summary' => 'Recurring customer', 'estimated_value' => 900 }
},
{
'name' => 'Jack', 'email' => 'jack@hbo.com', 'state' => 'CA', 'company' => 'HBO', 'company_rev' => 30000, 'description' => { 'summary' => 'Recurring customer', 'estimated_value' => 1000 }
}
]


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.

Syntax

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.

Sample usage

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]

How it works

This formula returns the first n items from a list. If n is greater than one, the output is formatted as a list.


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.

Syntax

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.

Sample usage

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]

How it works

This formula returns the last n items from a list. If n is greater than one, the output is formatted as a list.


index

Returns the index of the first item matching the given value. Returns nil if no matching items are found.

Syntax

Input.index(value)

  • Input - An input list.

  • value - The value to search for in the list.

Sample usage

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.

Name

Notation

Example

Equal to (default)

==

leads.where('state': 'CA')

More than

>

leads.where('company_revenue >": 10000)

More than or equal to

>=

leads.where('company_revenue >=": 10000)

Less than

<

leads.where('company_revenue <": 10000)

Less than or equal to

<=

leads.where('company_revenue <=": 10000)

Not equal to

!=

leads.where('state !=': 'CA')

Example of a single where condition

contacts.where('state': 'CA') returns the following rows:

name

email

state

company

company_rev

Joe

joe@abc.om

CA

ABC

1000

Jack

jack@hbo.com

CA

HBO

30000

These rows will be expressed as a list of hashes:

[ 
{
'name' => 'Joe', 'email' => 'joe@abc.com', 'state' => 'CA', 'company' => 'ABC', 'company_rev' => 1000
},
{
'name' => 'Jack', 'email' => 'jack@hbo.com', 'state' => 'CA', 'company' => 'HBO', 'company_rev' => 30000
}
]

Example of a compound where formula

A compound WHERE formula will retrieve only the rows that matches all the conditions.

contacts.where('state': 'CA', 'company_revenue >=": 10000)

will returns the following rows:

name

email

state

company

company_rev

Jack

jack@hbo.com

CA

HBO

30000

These rows will be expressed as a list of hashes:

[ 
{
'name' => 'Jack', 'email' => 'jack@hbo.com', 'state' => 'CA', 'company' => 'HBO', 'company_rev' => 30000
}
]

Example of multiple matches

You can filter out records based on a particular field against more than 1 value. This is done by passing an array value in the WHERE condition.

contacts.where('company': ['ABC','HBO'])

This WHERE condition will return rows where the company is either ABC or HBO:

name

email

state

company

company_rev

Joe

joe@abc.om

CA

ABC

1000

Jack

jack@hbo.com

CA

HBO

30000

These rows will be returned as a list of hashes.

[ 
{
'name' => 'Joe', 'email' => 'joe@abc.com', 'state' => 'CA', 'company' => 'ABC', 'company_rev' => 1000
},
{
'name' => 'Jack', 'email' => 'jack@hbo.com', 'state' => 'CA', 'company' => 'HBO', 'company_rev' => 30000
}
]

Example where condition with pattern matching

You can also filter out records using regex. This is done by passing a regex instead of a string.

contacts.where('name': /^Jo/)

This WHERE condition will return rows where the name starts with Jo:

name

email

state

company

company_rev

Joe

joe@abc.om

CA

ABC

1000

Joan

joan@nbc.com

MA

NBC

10000

These rows will be expressed as a list of hashes:

[ 
{
'name' => 'Joe', 'email' => 'joe@abc.com', 'state' => 'CA', 'company' => 'ABC', 'company_rev' => 1000
},
{
'name' => 'Joan', 'email' => 'joan@nbc.com', 'state' => 'MA', 'company' => 'NBC', 'company_rev' => 10000
}
]

Example where condition with pattern matching (using datapills)

You may use data pills within a regex pattern to dynamically change the string that you are matching. However, using variables in a regex pattern requires escaping within the regex expression.

For example: contacts.where(state: /#{ datapill }/)

The image below shows the method used to obtain all the 'Emails' in lookup table where the value in the 'State' column contains the string in the datapill from Salesforce, State | Step 2.

Datapill in regex expression

Note: All regex metacharacters will need to be escaped if they should not be interpreted as metacharacters.

Example of chaining where conditions

If a series of WHERE conditions are chained, the formula evaluates each where condition in series.

contacts.where('state': 'CA').where('company_revenue >=': 10000) returns the following rows, which is the same as the compound where formula:

name

email

state

company

company_rev

Jack

jack@hbo.com

CA

HBO

30000

In this case, however, the chaining will result in an intermediary array:

contacts.where('state': 'CA') first returns:

name

email

state

company

company_rev

Joe

joe@abc.om

CA

ABC

1000

Jack

jack@hbo.com

CA

HBO

30000

And .where('company_revenue >=': 10000) filters this intermediary array further to return only:

name

email

state

company

company_rev

Jack

jack@hbo.com

CA

HBO

30000

Results will be expressed as a list of hashes:

[ 
{
'name' => 'Jack', 'email' => 'jack@hbo.com', 'state' => 'CA', 'company' => 'HBO', 'company_rev' => '30000'
}
]


except

Returns a hash that includes everything except given keys.

hash = { a: true, b: false, c: nil } 
hash.except(:c) # => { a: true, b: false }
hash.except(:a, :b) # => { c: nil }
hash # => { a: true, b: false, c: nil }


pluck

Retrieves only the columns which have been specified.

Sample usage

Example of a single column dataput

contacts.pluck("email") returns

If a single column, results will be returned as an array:

["joe@abc.com", "jill@nbc.com", "joan@nbc.com", "jack@hbo.com"]

Example of a multiple column dataset

contacts.where("state ==": "CA").pluck("email", "company") returns

Results are returned as a list of a list:

[["joe@abc.com", "ABC"], ["jill@nbc.com", "NBC"], ["joan@nbc.com", "NBC"], ["jack@hbo.com", "HBO"]]

Example of retrieving nested fields

This method can be used to extract nested fields. Use the [<1st-level field>,<2nd-level field>...] format to define which fields to retrieve.

contacts.pluck("email", ["description", "summary"]) returns

email

summary

joe@abc.com

First time buyer

jill@nbc.com

Referral

joan@nbc.com

Recurring customer

jack@hbo.com

Recurring customer

Results are returned as a list of lists:

[ ["joe@abc.com", "First time buyer"], ["jill@nbc.com", "Referral"], ["joan@nbc.com", "Recurring customer"], ["jack@hbo.com", "Recurring customer"] ]


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>}.

Sample usage

contacts.format_map('Name: %{name}, Email: %{email}, Company: %{company}')

returns

[ 
'Name: Joe, Email: joe@abc.com, Company: ABC' ,
'Name: Jill, Email: jill@nbc.com, Company: NBC' ,
'Name: Joan, Email: joan@nbc.com, Company: NBC' ,
'Name: Jack, Email: jack@hbo.com, Company: HBO' ,
]

The preceding example will give you a list of strings, one string for each row of the list "contacts", using data from 3 of the fields: name, email, and company, as stated.


join

Combines all items in a list into a text string. A separator is placed between each item.

Syntax

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.

Sample usage

Formula

Result

["Ms", "Jean", "Marie"].join("-")

"Ms-Jean-Marie"

[1,2,3].join("--")

"1--2--3"

["ab", "cd", "ef"].join

"abcdef"

How it works

The list items are combined into a single text string. The separator characters is added between each item.

Separator character

You can use a string of characters together as the separator argument (for example, ", "). ["Open","Pending","Closed"].join(", ") returns "Open, Pending, Closed".


smart_join

Joins list elements into a string. Removes empty and nil values and trims any white space before joining.

Syntax

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.

Sample usage

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.

Syntax

List.reverse

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.sum

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.uniq

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.flatten

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.length

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.max

  • List - An input of list datatype.

Sample usage

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.

Syntax

List.min

  • List - An input of list datatype.

Sample usage

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.

Sample usage

Formula

Result

["foo", nil, "bar"].compact

["foo", "bar"]

{ foo: 1, bar: nil, baz: 2 }.compact

{ foo: 1, baz: 2 }


blank?

This formula checks the input string and returns true if it is an empty string or if it is null.

Syntax

Input.blank?

  • Input - An input datapill. It can be a string, number, date, or datetime datatype.

Sample usage

Formula

Result

"Any Value".blank?

false

123.blank?

false

0.blank?

false

"".blank?

true

How it works

If the input is null or an empty string, the formula will return true. For any other data, it returns false.


include?

Checks if the string contains a specific substring. Returns true if it does.

Syntax

Input.include?(substring)

  • Input - A string input.

  • substring - The substring to check for.

Sample usage

Formula

Result

"Partner account".include?("Partner")

true

"Partner account".include?("partner")

false

How it works

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, boolean false, an empty string, or an empty list, the formula will return false.

Syntax

Input.present?

  • Input - An input datapill. It can be a string, number, date, or list datatype.

Sample usage

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

How it works

If the input is null, an empty string or an empty list, the formula will return false. For any other data, it returns true.


presence

Returns the data if it exists, returns nil if it does not.

Syntax

Input.presence

  • Input - An input datapill. It can be a string, number, date, or datetime datatype.

Sample usage

Formula

Result

nil.presence

nil

"".presence

nil

"Any Value".presence

"Any Value"

45.0.presence

45.0

0.presence

0

How it works

If the input is null or an empty string, the formula will return nil. For any other data, it returns the original input data.


to_csv

Generates CSV line from an array. This handles escaping. Nil values and empty strings will also be expressed within the csv line.

Syntax

Input.to_csv

  • Input - An input of list datatype.

Sample usage

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.

Syntax

Input.to_json

  • Input - An input datapill. It can be a list or hash datatype.

Sample usage

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.

Syntax

Input.to_xml

  • Input - An input datapill. It can be a list or hash datatype.

Sample usage

Formula

Result

{"name" => "Ken"}.to_xml(root: "user")

<user><name>Ken</name></user>

[{"name" => "Ken"}].to_xml(root: "users")

<users><user><name>Ken</name></user></users>


from_xml

Converts XML string to hash.

Syntax

Input.from_xml

  • Input - Input XML data.

Sample usage

Converting XML string to hash

This XML string:

<?xml version=\"1.0\" encoding=\"UTF-8\" ?> <hash><foo type="integer">123</foo></hash>

represents the following XML data.

<?xml version=\"1.0\" encoding=\"UTF-8\" ?> <hash> <foo type="integer">123</foo> </hash>

XML string.from_xml will return the following hash.

{ "hash": [ "foo": [ { "@type": "integer", "content!": "1" } ] ] }


encode_www_form

Join hash into url-encoded string of parameters.

Syntax

Input.encode_www_form

  • Input - An input of hash datatype.

Sample usage

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.

Syntax

Input.to_param

  • Input - An input of hash datatype.

Sample usage

Formula

Result

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

"name=Jake&age=22"


keys

Returns an array of keys from the input hash.

Syntax

Input.keys

  • Input - An input of hash datatype.

Sample usage

Formula

Result

{"name" => 'Jake', "age" => '22'}.keys

["name", "age"]

Did this answer your question?