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:

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:

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.

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

Lists in Ruby supports negative indexes.

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

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.

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

The following is an example of a list of hashes called Contacts.

This is the Contacts list in a table form:

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

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)

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

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)

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

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

Input.index(value)

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.

Example of a single where condition

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

These rows will be expressed as a list of hashes:

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:

These rows will be expressed as a list of hashes:

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:

These rows will be returned as a list of hashes.

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:

These rows will be expressed as a list of hashes:

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.

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:

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

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

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

Results will be expressed as a list of hashes:

Returns a hash that includes everything except given keys.

Retrieves only the columns which have been specified.

Example of a single column dataput

contacts.pluck("email") returns

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

Example of a multiple column dataset

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

Results are returned as a list of a list:

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

Results are returned as a list of lists:

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

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

returns

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.

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

List.join(separator)

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

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

List.smart_join(separator)

Reverses the order of a list.

List.reverse

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

Returns a list containing unique items.

List.uniq

Flattens a multi-dimensional array (i.e. array of arrays) to a single dimension array.

List.flatten

Returns the number of elements in self. Returns 0 if the list is empty.

List.length

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

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

Removes nil values from array and hash.

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

Input.blank?

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

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

Input.include?(substring)

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.

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.

Input.present?

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

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

Input.presence

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

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

Converts hash or array to JSON string.

Input.to_json

Converts hash or array into XML string.

Input.to_xml

Converts XML string to hash.

Input.from_xml

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 string.from_xml will return the following hash.

Join hash into url-encoded string of parameters.

Input.encode_www_form

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

Input.to_param

Returns an array of keys from the input hash.

Input.keys