Other formulas ​

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

Formulas in Workato are allowlisted Ruby methods. Syntax and functionality for these formulas are generally unchanged. Most formulas return an error and stop the job if the formula operates on nulls (expressed as nil in Ruby), except for present?, presence, and blank?.

If a Ruby method is not found in the Workato documentation, it is not included in the allowlist and is therefore unsupported. Contact your Customer Success Manager to request new formulas for the allowlist.

Access nested values using square brackets ​

Square brackets ([]) allow you to access values in a hash in Workato. For nested hashes, you can chain lookups like data["a"]["b"]["c"]. However, this method raises a NoMethodError if any part of the chain doesn't exist (for example, if "b" is missing).

Use the safe navigation operator (&.) instead to safely access nested values without errors. For example, data["a"]&.[]("b")&.[]("c"). This returns nil if any part of the chain is nil.

null ​

Gives a null/nil value.

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

skip ​

Passes nothing to the destination app for this field. If the field has an existing value, it will be left untouched.

Example ​

This example attempts use an updated Salesforce record to update a lead in Marketo. It checks if the Salesforce Company is present. If yes, it will output the Salesforce Company into Marketo. Otherwise, the Marketo record is left untouched.

Skip formula example use case

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 format and base64 encoded.

Example ​

encrypt([ssn], [encryption_key])

decrypt ​

Decrypts the encrypted input string with a secret key using the AES-256-CBC algorithm. The encrypted input string should be packed in RNCryptor V3 format and base64 encoded.

The Ruby decrypt method returns a byte array instead of a string by default. You can convert the decrypt method output to a string by appending the .as_string() or .as_utf8 function to your formula.

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 ​

decode_url ​

URL decode a string. This formula uses CGI.unescape to URL decoding.

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. Refer to the Ruby SHA1 documentation for more information.

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_decode ​

Decodes a JSON web token (JWT) using one of the following algorithms - RS256, RS384, RS512, HS256, HS384, HS512, ES256, ES384, or ES512.

Example ​

jwt_encode ​

Creates a JSON web token (JWT) using one of the following algorithms - RS256, RS384, RS512, HS256, HS384, HS512, ES256, ES384, or ES512. Adds other named parameters to the header, such as kid in the following example:

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 Workato lookup tables using a key. The lookup formula is datatype-sensitive and case-sensitive.

If you use a datapill in the lookup formula, we recommend that you convert the data to the correct format. For example, convert integer-type datapills to a string with a .to_s formula if you plan to compare a column that contains both integers and strings.

Syntax ​

Use the following syntax to structure the formula:

lookup('Lookup table name', 'Match column': 'Match value')['Return column']

This formula searches for a row in the specified lookup table where Match column matches the Match value. It then returns the value from the specified Return column in that row.

The formula includes the following parameters:

• Lookup table name: The lookup table to query.
• Match column: The column used as the lookup key.
• Match value: The value that identifies the row in the key column.
• Return column: The column that provides the result.

Example ​

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

Sample department codes lookup table

https://app.workato.com/lookup_tables/<lookup_table_id>

lookup_table ​

This formula allows you to create a static lookup table and define the keys and values. It is case-sensitive and datatype-sensitive.

Example ​

data_table_lookup ​

This formula allows you to look up values from your data tables using a key. The lookup formula is case-sensitive and datatype-sensitive.

If you use a datapill in the lookup formula, we recommend that you convert the data to the correct format. For example, convert integer-type datapills to a string with a .to_s formula if you plan to compare a string-typed column.

Ensure that project name, table name, lookup key, and lookup criteria are written exactly the way they are stored in the data table, including proper capitalization.

Syntax ​

Use the following syntax to structure the formula:

data_table_lookup('Project name', 'Table name', 'Column name': 'Value to search on')['Return column']

This formula searches for a row in the specified data table where Column name matches the Value to search on. It then returns the value from the specified Return column in that row.

The formula includes the following parameters:

• Project name: The project that holds the data table. This value can also include folder names within the project, separated by / (for example, 'Project name/Folder name').
• Table name: The specific data table to query.
• Column name: The column used to find the match.
• Value to search on: The value that identifies the row in the key column.
• Return column: The column that provides the result.

Example ​

For example, let's use the following data table with name Wedding Guests inside the Wedding project:

Sample "Wedding Guests" Data table

data_table_query ​

This formula allows you to query records from your data tables using flexible filter conditions. Unlike data_table_lookup, which matches on equality and returns only the field values of a single row, data_table_query can also:

• Return multiple matching records in a single call.
• Include each record's ID (so a later action can use it to update, delete, or relate that record), creation time, and last updated time.
• Filter using operators beyond equality, such as greater than, less than, or a list of possible values.

The query is case-sensitive and datatype-sensitive. Ensure that the project name, table name, column names, and filter values are written exactly the way they're stored in the data table, including proper capitalization.

Syntax ​

Use the following syntax to structure the formula:

data_table_query('Project name', 'Table name', {query options})

The formula includes the following parameters:

• Project name: The project that holds the data table. This value can also include folder names within the project, separated by / (for example, 'Project name/Folder name').
• Table name: The specific data table to query.
• query options: A hash that defines what to return and how to filter, sort, and limit the results. Refer to Query options.

Query options ​

The query options hash accepts:

You can reference a column by its name, or use the built-in fields $record_id, $created_at, and $updated_at in where and order. These built-in fields can't be listed in select, since they're always included in the result. If a column name itself starts with $, add an extra $ to reference it (for example, a column named $ spent is referenced as $$ spent).

Query option keys, operators, and field names each accept either a symbol or a string. For example, where: {...} and 'where' => {...} behave the same way.

Link to a table and File columns work a little differently, both for filtering and for reading their values back:

• To filter by a Link to a table column, use the ID of the record it links to.
• To filter by a File column, use the file's name.
• A returned Link to a table column includes both the linked record's record_id and its display_name.
• A returned File column includes the file's filename.
• A multivalue column (one that can hold more than one value) returns a list of values instead of a single one.

Mode ​

mode controls how many records the query expects to match and what the formula returns when zero, one, or multiple records match:

Examples ​

The following examples use a Customers data table with Company name, Region, Revenue, and Status columns:

data_table_query('Sales', 'Customers', {where: {'Company name': 'Globex Inc'}, mode: 'one_unique'})['record_id']

data_table_query('Sales', 'Customers', {where: {'Company name': 'Nonexistent Co'}, mode: 'one_unique_optional'})&.[]('fields')&.[]('Region') || 'Unknown region'

data_table_query('Sales', 'Customers', {where: {'Company name': {'$starts_with': 'Acme'}}, mode: 'all', limit: 50, select: ['Revenue']})['records'].pluck('fields').pluck('Revenue').sum

data_table_query('Sales', 'Customers', {where: {'Region': 'West'}, mode: 'all', limit: 50})['records'].pluck('fields').pluck('Company name')

data_table_query('Sales', 'Customers', {order: {by: 'Revenue', direction: 'desc'}, mode: 'one_first_optional'})&.[]('fields')

Return value ​

A record is a hash with record_id, created_at, updated_at, and fields keys. one_unique, one_unique_optional, one_first, and one_first_optional return a single record in this shape, or nil. For example:

data_table_query('Sales', 'Customers', {where: {'Company name': 'Globex Inc'}, mode: 'one_unique'})

This query returns the following record:

{
  "record_id": "9309e4c0-2548-4508-b938-c781e77a7dda",
  "created_at": "2026-07-28T15:00:00.000+00:00",
  "updated_at": "2026-07-28T15:00:00.000+00:00",
  "fields": {
    "Company name": "Globex Inc",
    "Region": "East",
    "Revenue": 200000,
    "Status": "Active"
  }
}

all and some return a hash with a records key, whose value is an array of records in this shape. For example:

data_table_query('Sales', 'Customers', {where: {'Company name': {'$starts_with': 'Acme'}}, mode: 'all', limit: 50})

This query matches four companies. The following shows the first two:

{
  "records": [
    {
      "record_id": "...",
      "created_at": "...",
      "updated_at": "...",
      "fields": {
        "Company name": "Acme Corp",
        "Region": "West",
        "Revenue": 120000,
        "Status": "Active"
      }
    },
    {
      "record_id": "...",
      "created_at": "...",
      "updated_at": "...",
      "fields": {
        "Company name": "Acme Consulting",
        "Region": "East",
        "Revenue": 95000,
        "Status": "Active"
      }
    }
  ]
}