# 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?.

You can refer to the complete Ruby documentation for arrays (lists) here (opens new window) as well as the Ruby documentation for hashes here (opens new window).

# Lists (arrays)

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:

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

As lists are ordered, we can use the following formula to get the values. Workato only supports retrieving up to the fifth item in the list:

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

# List of hashes

In Workato, you will mostly run into lists of hashes. Let's look at a Quickbooks invoice which has a number of line items. It will be represented as an list of hashes.

line_items = [                                          # list
  { 'item_name' => 'Acme widgets', 'item_qty' => 10 },  # hash 1
  { 'item_name' => 'RR bearings', 'item_qty' => 100 },  # hash 2
  { 'item_name' => 'Coyote tyres', 'item_qty' => 7 }    # hash 3
]

# Formulas

Workato supports a variety of list formulas. Formulas in Workato 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.

# 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.om 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.

Output datatype

If you are returning a single item (i.e. no arguments provided). The output will be formatted according to the item's datatype.

If you are returning more than one item. The output will be formatted as a list datatype.

# See also

  • last: Returns the last n items in a list.
  • where: Returns a subset of list items that meet a certain condition.

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

Output datatype

If you are returning a single item (i.e. no arguments provided). The output will be formatted according to the item's datatype.

If you are returning more than one item. The output will be formatted as a list datatype.

# See also

  • first: Returns the first n items in a list.
  • where: Returns a subset of list items that meet a certain condition.

# index

Returns the index of the first item matching the given value.

# 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')

Use datapills as the conditional argument

Instead of using a static value (e.g. 'CA'), you can use a datapill as the conditional argument. The value of the datapill will be processed at run-time.

contacts.where(state: datapill )

# Sample usage

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 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 Using datapills in regex expressions

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' => '1000'
  }
]

# 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

email
joe@abc.com
jill@nbc.com
joan@nbc.com
jack@hbo.com

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

email company
joe@abc.com ABC
jill@nbc.com NBC
joan@nbc.com NBC
jack@hbo.com HBO

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 above 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 (e.g. ", ").

["Open","Pending","Closed"].split(", ") returns "Open, Pending, Closed".

# See also

  • split: Divides a string around a specified character and returns an array of strings.

# 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 i.e. remove duplicate 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 }

# Conditionals

# 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 false. For any other data, it returns true.

# See also

  • presence: Returns the data if it exists, returns nil if it does not.
  • present?: Returns true if there is a valid input.

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

# See also

  • exclude?: Checks if the string contains a specific substring. Returns false if it does.

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

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

Evaluating a list with nil values

  • Only an empty list will return false.

[].present? returns false.

  • A list with nil and empty string will return true.

[nil,""].present? returns true.

# See also

  • presence: Returns the data if it exists, returns nil if it does not.
  • blank?: Returns nil if the data does not exist or if the string consist of only white spaces.

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

# See also

  • blank?: Returns nil if the data does not exist or if the string consist of only white spaces.
  • present?: Returns true if there is a valid input.

# Conversion

The following formulas allows you to convert data from arrays to other data types


# 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"]