Strings

Array Diff

ArrayDiff({BASE_ARRAY}, {MATCH_ARRAY} [, {SEPARATOR}])

Returns items from the {BASE_ARRAY} that are missing in the {MATCH_ARRAY}. 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 Index of

ArrayIndexOf({Input Array}, {Separator}, {Value to find} [, {Return last}])

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

Array Length

ArrayLength({INPUT} [, {SEPARATOR}])

Returns the number of items in an array for a given input. An optional {SEPARATOR} character delineates the items. The default {SEPARATOR} is a comma (,).

Example:

Input: ArrayLength(“546, Apple, ABC 123”)

Result: 3

At Mention

AtMention({Email}, {SEND_BY(optional)})

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 {SEND_BY}, 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

CharAt

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}, {SORT_ORDER})

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({INPUT_1} [, {INPUT_n}]+)

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

Conditional Value

ConditionalValue({Variable}, {Condition1}, {Result1}, {Condition2}, {Result2}, ... , {DefaultValue})

Compares a variable to a set of condition-result pairs and returns the corresponding result when the variable matches a condition (also known as a "switch" function). If the variable does not match any of the conditions, the default value is returned.

Input: ConditionalValue({A}, “yes”, 10, “no”, 20, 0)

Result: If yes is the provided variable, 10 is returned.

Convert to Array

ConvertToArray({A, B, C},"{SEPARATOR}")

Converts a comma-separated list of values to an array.

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

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

Convert to String

ConvertToString({A})

Converts a specified value to a string.

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

Result: Please convert this to a string

Create List

CreateList([A, B, C])

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

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

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

Day of Week

DayOfWeek({DATE-VARIABLE}, {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({A_COLUMN}, {B_COLUMN})

Concatenates a string of text and a number from two separate columns. 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({COLUMN})

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

Input: DecodeHtmlUrl("https%3A%2F%2Ftonkean.com%2Fabout%2F")

Result: https://tonkean.com/about

Encode/Decode URL

EncodeUrl({INPUT})

DecodeUrl ({INPUT})

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

Decode URL does the opposite: takes an application-formatted URL and turns it into a human-readable URL.

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

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

Encode to Base64

EncodeBase64({INPUT})

Encodes a specified string into Base64 format.

Input: EncodeBase64(“test”)

Result: dGVzdA==

Escape JSON

EscapeJson({JSON})

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

Input: EscapeJson({ color: "red", value: "#f00" })

Result: { \tcolor: \"red\", \tvalue: \"#f00\" }

Fallback

Fallback({INPUT}, {VALUE_IF_EMPTY})

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

Format Full Name

FormatFullName({A})

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

Input: FormatFullName("Rose, Evelyn")

Result: Evelyn Rose

Generate Unique ID

UniqueId({NUMBER} or {STRING})

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

Example:

Input: UniqueId("Number")

Result: 2162955155798869

Example:

Input: UniqueId("String")

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

Greedy HTML Path

GreedyHtmlPath({HTML}, {HTML_PATH})

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({INPUT},{SECRET_KEY})

Encrypts 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 encrypted output is a hexidecimal string.

Input: HMAC_SHA256Encryption(654-36-7918,{SECRET_KEY})

Result:a080f274a2e5a685992ad3df02df68af8bc39c507d004ff608433894a2c2d924

HTML Path

HtmlPath({HTML}, {HTML_PATH})

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

JSON Path

JsonPath({COLUMN}, {JSON_PATH}, {RETURN_ENTIRE_ARRAY(OPTIONAL)})

Returns the value of a given JSON path from the provided JSON content. The third parameter, RETURN_ENTIRE_ARRAY, 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

Map

Map({Json Array}, {Mapping Configuration})

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("[2,4,6]", ("$singleItemValue" * 2))

Result: [4,8,12]

Month

Month({DATE_COLUMN}, {ZONE})

Returns the month for a specified date and time zone.

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

Result: 2

OCR Output Contains Words

OcrOutputContainsWords({OCR_OUTPUT}, {WORDS_TO_FIND_ARRAY}, {IGNORE_CASE}, {SEPARATOR})

Checks whether an OCR output contains the specified words.

Note: Dependent on the correctness of the OCR output.

Input: OcrOutputContainsWords("extractedText":"This is a test", "this test", "true", " ")

Result: true

Pairs

Pairs({KEY }, {VALUE})

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({Column})

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( {INPUT}, {REGEX}, {GROUP_INDEX(optional)}, {MATCH_INDEX(optional)}, {GROUPS_SEPARATOR(optional)} )

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.

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

Result: tonkean

Replace

Replace({INPUT}, {FIND}, {REPLACE})

Replaces all occurrences of the {FIND} value with the {REPLACE} value within the {INPUT}.

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

Result: this-is-fun

Split Text and Take

SplitAndTake({Input}, {SplitByCharacter}, {Index}, {True/False})

Splits the input text into multiple parts as specified by the SplitByCharacter 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

Starts With

StartsWith({INPUT}, {SUBSTRING})

Checks whether the {INPUT} string begins with the characters specified in {SUBSTRING} and returns true or false. The comparison is case-sensitive.

Input: StartsWith(“Hello”, “he”)

Result: false

String Join

StringJoin({STRING_A}, {STRING_B})

Concatenates two specified values with a comma separating each value.

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

Result: 1,5

String Lookup Table

StringLookUpTable({FIELD}, {FIND}, {REPLACE} [, {FIND}, {REPLACE}]+)

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({INPUT})

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({INPUT}, {START_INDEX}, [{END_INDEX}])

Extracts a subsection of the {INPUT} value based on the specified {START_INDEX} and {END_INDEX}. If no {END_INDEX} value is specified, it will use the end of the input as the {END_INDEX}.

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

Result: 1432

Take Not Empty

TakeNotEmpty([{A}])

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

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

Result: Joyce

Text Analysis

TextAnalysis({Variable}, {Expression1}, {Result1}, {Expression2}, {Result2}, {DefaultValue})

Compares a variable to a set of expression-result pairs and returns the corresponding result when the variable matches an expression (also known as a "switch" function). If the variable does not match any of the expressions, the default value is returned.

Input: TextAnalysis("Tonkean", "Tonkean", "test1", "123", "test2", "test3")

Result: test1

To Lower Case

ToLowerCase({INPUT})

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({INPUT})

Converts a specified string to UPPERCASE text.

Input: ToUpperCase("I neEd help")

Result: I NEED HELP

Trim

Trim({INPUT})

Removes the spaces before and after a provided string.

Input: Trim(" Tonkean ")

Result: Tonkean