Strings

Array Common

ArrayCommon({FirstArray}, {SecondArray},{Separator})

Returns items that are shared in common between {FirstArray} and {SecondArray}. A {Separator} delineates items. The default {Separator} is a comma.

Example:

Input: ArrayCommon("A,B,C,D", "C,E,A", ",")

Result: A,C

Example:

Input: ArrayCommon("a,B,c,D", "B,C", ",")

Result: B

Array Diff

ArrayDiff({BaseArray},{MatchArray}, {Separator})

Returns items from the {BaseArray} that are missing in the {MatchArray}. An optional {Separator} character delineates the items. The default {Separator} is a comma.

Example:

Input: ArrayDiff("1,2,3,4", "2,4")

Result: 1,3

Example:

Input: ArrayDiff("Apple; Banana; Cherry; Durian", "Banana; Cherry", ";")

Result: Apple,Durian

Array Distinct

ArrayDistinct({Array},{Separator})

Returns the unique values from a provided array, excluding any duplicate values.

Input: ArrayDistinct("A,A,B,C,C,D", ",")

Result: "A,B,C,D"

Array Index of

ArrayIndexOf({InputArray}, {Separator},{ValueToFind},  {ReturnLast})

Returns a number based on the position specified by the separator. Returns -1 if the expected value is not found.

Inputs:

Example:

Input: ArrayIndexOf(“A,B,C,B,D”, “,”, “C”, false)

Result: 2

Example:

Input: ArrayIndexOf(“A,B,C,B,D”, “,”, “B”, true)

Result: 3

Example:

Input: ArrayIndexOf(“A,B,C,B,D”, “,”, “E”, false)

Result: -1

At Mention

AtMention({Email},{SendBy})

Converts emails to a Slack "mention." Tonkean searches Slack for the specified email address and returns the username that corresponds with that email address. If no value is provided for {SendBy}, the default communication method is Slack.

This is a special function that requires you to have a Slack instance connected to Tonkean. See Connect Communication Sources for more information.Connect Communication Sources

Input: AtMention(“joe@gmail.com”)

Result: @joe

Char At

CharAt({Input},{Index})

Returns the character at the specified index (zero-based).

Input: CharAt("abcd@tonkean.com", 5)

Result: t

Comment Transcript

CommentTranscript({Skip},{Limit}, {Separator},{SortOrder})

Returns the comments on an item.

Input: CommentTranscript(2, 4, " ", "Asc")

Result: Returns the third and fourth comments on an item, separated by a comma (,).

Concatenate

Concat([{Value1},{Value2}])

Concatenates the string representation of all values provided. Only one input is required, but any number of additional inputs can be provided.

Input: Concat(“ABC”, 123, “DEF”)

Result: ABC123DEF

Convert to Array

ConvertToArray({StringArray},{Separator})

Converts a comma-separated list of values ({StringArray}) to an array.

Input: ConvertToArray("Joyce, Woolf, Stevens", ",")

Result: ["Joyce","Woolf","Stevens"]

Convert to String

ConvertToString({Input})

Converts a specified value to a string.

Input: ConvertToString(“Please convert this to a string”)

Result: Please convert this to a string

Create List

CreateList([Item1,Item2,Item3])

Converts a series of values into a comma-separated list.

Input: CreateList(["Joyce", "Woolf", "Stevens"])

Result: ["Joyce","Woolf","Stevens"]

Date Format

DateFormat({Input},{Separator},{Zone})

Formats a date value in a specified format for a specified time zone.

Input: DateFormat("13 Jul 2021", "MM/dd/yyyy", “UTC”)

Result: 07/13/2021

Day of Week

DayOfWeek({Date},{Zone})

Returns the day of the week for a specified date and time zone.

Input: DayOfWeek("13 Mar 2022", "UTC")

Result: SUNDAY

Decimal Format

DecimalFormat({APattern},{ANumber})

Concatenates a string of text and a number from two separate fields. The first variable must be a string and the second variable must be a number.

Input: DecimalFormat("ABCDEF", "123")

Result: ABCDEF123

Decode from Base64

DecodeBase64({Input})

Decodes a specified Base64-encoded string, converting it into its native format.

Input: DecodeBase64(“dGVzdA==”)

Result: test

Decode HTML URL

DecodeHtmlUrl({Field})

Decodes an HTML-encoded string by removing special characters and numbers to make it human-readable.

Input: DecodeHtmlUrl("<test>Tonkean & Friends</test>")

Result: Tonkean and Freinds

Decode URL

DecodeUrl({Field})

Converts an application-formatted URL and turns it into a human-readable URL.

Input: DecodeUrl("https%3A%2F%2Fwww.google.com%2F")

Result: https://www.google.com

Email Domain

SplitAndTake({Email},{SplitBy},{TakeIndex})

Splits an email address at the @ sign and extracts the domain that follows.

The {SplitBy} value is preset to @ and the {TakeIndex} is preset to 1, extracting the second value (the domain).

Input: SplitAndTake("cnalaar@gatewatch.com","@",1)

Result: gatewatch.com

Encode to Base64

EncodeBase64({Field})

Encodes a specified string into Base64 format.

Input: EncodeBase64(“test”)

Result: dGVzdA==

Encode URL

EncodeUrl({Field})

Converts a human-readable URL into application/x-www-form-url encoded format.

Input: EncodeUrl(“https://www.google.com”)

Result: https%3A%2F%2Fwww.google.com%2

Escape JSON

EscapeJson({Field})

Escapes special characters in a JSON-formatted string. The following characters are escaped:

Input: EscapeJson("TAB AND backslash\ are encoded")

Result: \"TAB\tAND backslash\\ are encoded\"

Fallback

Fallback({InputString},{ValueIfempty})

Returns the input if not empty or null, otherwise returns the fallback value.

Input: Fallback("", "It's an empty cell!")

Result: It’s an empty cell!

Find Words

FindWords({Input},{Before},{After})

Extracts a string between specified {Before} and {After} phrases using OCR.

Example:

The last portion of a receipt reads:

"Total Amount: $3,644.25

Thank You for your business!"

Input: FindWords({Receipt OCR output}, “Total Amount:”, “Thank You”)

Result: [“$3,644.25"]

First Name

FirstName({FullName}, {SplitByCharacter},{Index})

Splits a full name at the designated place and extracts the first value (at the zero index). We recommend setting {SplitByCharacter} to a space in most cases.

The {TakeIndex} value is preset to 0, extracting the first value.

Input: FirstName("Liliana Vess", " ", 0)

Result: Liliana

Format Full Name

FormatFullName({Input})

Reformats input to a standard full name format (that is, "First Last").

Input: FormatFullName("Rose, Evelyn")

Result: Evelyn Rose

Generate Unique ID

UniqueId({Type})

Generates a unique 16-digit number or 36-character string (UUID). There are two valid inputs for {Type}: Number and String.

Example:

Input: UniqueId("Number")

Result: 2162955155798869

Example:

Input: UniqueId("String")

Result: 759902be-9c5d-4aa1-8544-bb3066e9eab0

Greedy HTML Path

GreedyHtmlPath({Html}, {DomTraversalOrCssSelectors})

Returns all of the elements of given DOM traversal or CSS selectors from HTML.

Input: GreedyHtmlPath("<div class="text"> Tonkean </div><div class="text"> Tonkean </div>", .text)

Result: Tonkean, Tonkean

HMAC SHA-256 Encryption

HMAC_SHA256Encryption({Data},{SecretKey})

Hashes an input value using hash-based message authentication code (HMAC) with hash function SHA-256.

This formula is used as a means to authenticate with some APIs that require creating a hash-based signature.

This formula requires two parameters:

The hashed output is a hexidecimal string.

Input: HMAC_SHA256Encryption("654-36-7918", "test"})

Result:5ed37e3e20fe53cf12fd8c32380a8595713c62d0870b82d1efb411016aadb836

HTML Path

HtmlPath({Html}, {DomTraversalOrCssSelectors})

Returns the first match of the specified element of given DOM traversal or CSS selectors from HTML.

Input: HtmlPath("<div class="text"> Tonkean </div><div class="text"> Tonkean </div>", .text)

Result: Tonkean

Index of

IndexOf({Input},{ValueToFind})

Returns the first index of {ValueToFind} in the {Input}. If not found, will return -1. If found it will return the index value from where the match was found.

Example:

Input: IndexOf("abc123", "xyz")

Result: -1

Example:

Input: IndexOf("abc@tonk.com", "@tonk")

Result: 3

JSON Path

JsonPath({Field},{JsonPath},{ReturnEntireArray})

Returns the value of a given JSON path from the provided JSON content. The third parameter, ReturnEntireArray, is optional and defaults to False if left blank.

Example:

Input: JsonPath("{"ids":[1,2,3,4]}", "$.ids", "True")

Result: [1,2,3,4]

Example:

Input: JsonPath("{"ids":[1,2,3,4]}", "$.ids", "False")

Result: 1

JSON Traverse

JsonTraverse({Field},{Path})

Returns a field value from a JSON body using the provided pathway.

Input: JsonTraverse("Contract","$.contracts[:1].type")

Result: nda

Map

Map({JsonArray},{MappingConfiguration})

Maps each item in the JSON array using the specified mapping configuration—usually a formula or other operation you want to perform on the values in the array.

There are two preset values you can use with this formula:

Input: Map("[\"A\",\"B\",\"C\"]", Concat("$singleItemValue", "_2"))

Result: [A_2, B_2, C_2]

Month

Month({DateField},{Timezone})

Returns the month for a specified date and time zone.

Input: Month("2/23/2020", "America/Los_Angeles")

Result: 2

Pairs

Pairs({Key1},{Value1})

Generates key-value pairs in JSON format. Include additional key and value pairs, separated by commas, to generate multiple pairs.

Input: Pairs("First", "Evie", "Last", "Rose")

Result: {"First":"Evie","Last":"Rose"}

Previous Value

PreviousValue({Field})

Returns the previous value for the specified field.

Tonkean keeps track of previous values for all fields. With each change, this formula returns the most recent value.

—

Regex Find

RegexFind({InputString}, {RegularExpression},{RequestedGroups}, {RequestedMatches},{GroupsSeparator})

Finds the requested group of data in the custom ReGex criteria and displays the results.

It’s important to know ReGex formatting in order to properly utilize this function. Visit RegEx for more information.

The following values are optional:

Input: RegexFind("abc@tonkean.com", "(?<=@)([^.]+)(?=\.)", 1)

Result: tonkean

Replace

Replace({Input},{ValueToFind},{ValueToReplace})

Replaces all occurrences of the {ValueToFind} with the {ValueToReplace} value within the {Input}.

Input: Replace("this is fun", " ", "-")

Result: this-is-fun

Split Text and Take

SplitAndTake({Input},{SplitBy}, {TakeIndex},{ReturnLast})

Splits the input text into multiple parts as specified by the SplitBy and returns the part according to the index (zero-based) when the last parameter is false. Setting the last variable to true always returns the last part, disregarding the index.

Example:

Input: SplitAndTake("Here is an example", " ", 1, true)

Result: example

Example:

Input: SplitAndTake("Here is an example", " ", 1, false)

Result: is

String Join

StringJoin({Value1},{Value2})

Concatenates two specified values with a comma separating each value.

Input: StringJoin("1","5","6")

Result: 1,5,6

String Lookup Table

StringLookUpTable({SourceText},[ {Find1},{Replace1}])

Retrieves specified values from a table and replaces a string with those values.

Input: StringLookUpTable("dianalor@docs.com", "diana", "shax", "lor", "shoham", "tonkean", "docs")

Result: shaxshoham@docs.com

Strip HTML

StripHtml({Field})

Returns the raw text of a given HTML or CSS code snippet. All HTML/CSS tags are removed and the output will be comma- delineated.

Input: StripHtml(“<a href="asdf.com">hi there</a>”)

Result: hi there

Substring

Substring({InputString}, {StartingIndex}, {EndIndex})

Extracts a subsection of the {InputString} value based on the specified {StartingIndex} and {EndIndex}. If no {EndIndex} value is specified, it will use the end of the input as the {EndIndex}.

Input: Substring("XXX1432XX", 3, 7)

Result: 1432

Take Not Empty

TakeNotEmpty([{Value1},{Value2},{Value3}])

Extracts the first not-empty value from a list of values.

Input: TakeNotEmpty(["", "Joyce", "Woolf", "Stevens"])

Result: Joyce

To Lower Case

ToLowerCase({InputString})

Converts a specified string to lowercase text.

Input: ToLowerCase("I NEED hElP")

Result: i need help

To Pascal Case

ToPascalCase({Value})

Converts a specified string to Pascal case, where the first letter of each word is capitalized.

Input: ToPascalCase("i neEd help")

Result: I Need Help

To Upper Case

ToUpperCase({InputString})

Converts a specified string to UPPERCASE text.

Input: ToUpperCase("I neEd help")

Result: I NEED HELP

Trim

Trim({InputString})

Removes the spaces before and after a provided string.

Input: Trim(" Tonkean ")

Result: Tonkean

XML Path

XmlPath({Field},{XmlPath})

Returns a field value from an XML body using the provided pathway.

Input: XmlPath("Contract","/contracts/contract[1]/assignee")

Result: Atticus Finch