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

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:

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:

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.

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.

List of hashes

In AppConnect, 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.

Formulas

AppConnect supports a variety of list 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.

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:

This is the Contacts list in a list of hashes 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.

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

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.

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.

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

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.

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.

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

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.

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 conditionExample of compound where formulaExample of multiple matchesExample where condition with pattern matchingExample where condition with pattern matching (using datapills)Example of chaining where conditions

except

Returns a hash that includes everything except given keys.

pluck

Retrieves only the columns which have been specified.

Sample usage

Example of a single column dataputExample of a multiple column datasetExample of retrieving nested fields

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

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

How it works

The list items are combined into a single text string. The seperator character(s) is added between each item.

Seperator character

You can use a string of characters together as the seperator argument (e.g. ", ").

["Open","Pending","Closed"].split(", ") 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

reverse

Reverses the order of a list.

Syntax

List.reverse

• List - An input of list datatype.

Sample usage

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

uniq

Returns a list containing unique items i.e. remove duplicate items.

Syntax

List.uniq

• List - An input of list datatype.

Sample usage

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

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

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

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

compact

Removes nil values from array and hash.

Sample usage

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

How it works

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.

Syntax

Input.include?(substring)

• Input - A string input.

• substring - The substring to check for.

Sample usage

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, 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

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.

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

How it works

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

The following formulas allow 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

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

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

from_xml

Converts XML string to hash.

Syntax

Input.from_xml

• Input - Input XML data.

Sample usage

Converting XML string to hash

encode_www_form

Join hash into url-encoded string of parameters.

Syntax

Input.encode_www_form

• Input - An input of hash datatype.

Sample usage

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

keys

Returns an array of keys from the input hash.

Syntax

Input.keys

• Input - An input of hash datatype.

Sample usage

Other formulas

This section covers formulas that work with a number of data types.

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

encrypt

Encrypts the input string with a secret key using AES-256-CBC algorithm. Encrypted output string is packed in RNCryptor V3 (opens new window)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.

Example

encrypt([ssn], [encryption_key])

decrypt

Decrypts the encrypted input string with a secret key using AES-256-CBC algorithm. Encrypted input string should be packed in RNCryptor V3 (opens new window)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.

Example

decrypt([encrypted_ssn], [encryption_key])

encode_sha256

Encodes a string or binary array using SHA256 algorithm

Example

"hello".encode_sha256

encode_hex

Converts binary string to its hex representation

Example

decode_hex

Decode hexadecimal into binary string

Example

encode_base64

Encode using Base64 algorithm

Example

decode_base64

Decode using Base64 algorithm

Example

encode_url

URL encode a string

Example

encode_urlsafe_base64

Encode using urlsafe modification of Base64 algorithm

Example

decode_urlsafe_base64

Decode using urlsafe modification of Base64 algorithm

Example

as_string

Decode byte sequence as string in given encoding

Example

as_utf8

Decode byte sequence as UTF-8 string

Example

to_hex

Converts binary string to its hex representation

Example

SHA1

Encrypts a given string using the SHA1 encryption algorithm. Details here(opens new window)

Example

HMAC formulae

Creates a HMAC signatures with a variety of signing algorithms

Example

md5_hexdigest

Accepts a string and creates message digest using the MD5 Message-Digest Algorithm

Example

jwt_encode_rs256

Creates JWT with RS256 - RSA using SHA-256 hash algorithm

Example

parse_yaml

Parse a YAML string. Supports true, false, nil, numbers, strings, arrays, hashes

Example

render_yaml

Render an object into a YAML string.

Example

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

For example, let's use the following lookup table with name Department Codes with an ID of 6:

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

Complex data types

Up to this point, we have only discussed primitive data types like Strings and Integers. With most APIs, data is represented with more complex structures. These can be broadly classified into JSON Objects and Arrays.

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 (opens new window), 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).

To do this, simply toggle the Tags input field to formula mode, and form the required structure. This can be done in a few ways.

First, we can perform a test with a statically defined array of strings. Make sure that the input value observes proper JSON syntax. Invalid values in formula mode will raise design time formula errors.

Next, it is a good idea to perform some tests to make sure that the custom action updates the ticket with the new tags as expected. At this point, you can improve the recipe further to update tickets with tags dynamically.

Since Zendesk API expect an Array of Strings, we need to retrieve only the Escalation Name values from the Escalations returned from a previous action.

We do this by using the pluck formula, which conveniently returns an array of Escalation Name values, perfect!

Finally, make sure that the right data is sent by checking the job details. Always make sure to test your work before setting a recipe live!