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

Concatenate With Line Breaks

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

Concatenates two or more input string values, adding line breaks between each value.

Input: LineConcat(["Line 1","Line 2"])

Result:

Line 1

Line 2

Convert JSON Array To String

ConvertArrayToString({Value},{Separator})

Converts an array in JSON format to string.

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

Result: Joyce, Woolf, Stevens

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

CSV to JSON

CsvToJson({Field})

Converts a provided value from CSV to JSON format.

Input: CsvToJson("name,age,city,Evie,5,Los Angeles")

Result: [{"name":"Evie","age":5,"city":"Los Angeles"}]

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({A Pattern},{A Number})

Formats the number value, {A Number}, with the provided pattern, {A Pattern}. Use the following characters to provide the pattern:

In addition to formatting {A Number}, this formula also rounds to the relevant place provided in {A Pattern}.

Input: DecimalFormat("###.##", "713.4792")

Result: 713.48

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 to MD5

EncodeMD5({Field})

Encodes a specified string using the MD5 hash.

Input: EncodeMD5("test")

Result: dc647eb65e6711e155375218212b3964

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 HTML

EscapeHtml({Field})

Escapes special characters in an HTML string.

—

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!

Field Group Take Not Empty

FieldGroupTakeNotEmpty({GroupName})

Returns a field that contains a value from a provided field group.

This formula checks the fields in the group provided randomly; if there are multiple fields that contain values in the provided group, this formula essentially returns one at random.

—

Field Group To Map

FieldGroupToMap({GroupName})

Generates a JSON object containing the field and value pairs for a provided field group.

A field group is a set of fields assigned the same field group name, allowing them to be used collectively for certain operations, like formulas. For more information about adding fields to a field group, see Manual Fields.

Example:

Where a field group "Group 1" contains FieldA with value A and FieldB with value B:

Input: FieldGroupToMap({Group 1})

Result:

{
  "FieldA": "A",
  "FieldB": "B"
}

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 Sequence ID

GenerateSequenceId({Scope})

Generates a unique number for each item or inner item based on the provided {Scope}, which determines the level at which the sequence ID is incremented. For example, if you set the {Scope} to the Project Id (that is, at the board level), all modules in that board with a sequence formula field set to the same scope will increment the same sequence. You can set custom scopes to leverage across different modules as well.

You can create multiple sequence IDs to track items at various levels of the workflow.

{Scope} defaults to the Module ID for root items, incrementing for each item in that specific module; for inner items, {Scope} defaults to the Parent ID, restarting the sequence for the inner items within each parent item. To use this default behavior, manually set {Scope} to an empty string ("").

Once generated, the sequence ID cannot be removed from the relevant item and increments for each item, ensuring the value is unique.

Example:

Input: GenerateSequenceId({customScope})

Result: 1

Subsequent items with the same scope would have values equal to 2, 3, 4, and so on.

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

Get Interface Link By Name

GetInterfaceLinkByName({InterfaceName},{CleanMode})

Returns a link to a custom item interface with a name matching the InterfaceName provided.

Additionally, you can set CleanMode to true to send a link with a streamlined UI or set CleanMode to false to send a link with the standard UI. The default is CleanMode = false. CleanMode is helpful when working with embedded interfaces.

Input: GetInterfaceLinkByName("Manager Review", false)

Result: https://{env}.tonkean.com/PROJ{...}interfaceView?initiativeId=:)

Get Sequence Link By Trigger Name

GetSequenceLinkByTriggerName({TriggerName})

Returns the link to the intake sequence with a trigger name matching the TriggerName provided.

Make sure your trigger names are unique to reliably return a result for this formula.

Input: GetSequenceLinkByTriggerName("Primary Intake")

Result: https://{env}.tonkean.com/PROJ{...}/itemInterfaceIntake/3xampl3

Get User Name

GetUserName({Email})

Returns the full user name for a given user using their email address, using the user name in a connected communication source (for example, Slack or Teams). If no user name is found, this formula returns the email address.

Input: GetUserName("evelyn.rose@tonkean.com")

Result: Evelyn Rose

Get Value By Key

GetValueByKey({JsonString},{Key})

Returns the value for a provided JSON key.

Input: GetValueByKey({User},"city")

Where {User} is the following:

{
  "name": "Ashley Taylor",
  "city": "Northridge"
}

Result: Northridge

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

Join Arrays

JoinArrays({RemoveDuplicates},"{Separator}", [{Array 1},{Array 2}])

Joins two arrays into a single array, either maintaing all values or removing any duplicate values.

To remove duplicate values and keep only unique values in the resulting merged array, set RemoveDuplicates to true.

Example:

Input: JoinArrays(true,",", ["1,2,3", "1,2,4,5"])

Result: [1,2,3,4,5]

Example:

Input: JoinArrays(false,",", ["1,2,3", "1,2,4,5"])

Result: [1,1,2,2,3,4,5]

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 to XML

JsonToXml({Field},{RootName},{ShouldAddDeclaration})

Converts a string in JSON format to XML format with options for adding a RootName and adding a declaration.

—

JSON Transformation Language

JSLT({JSLT},{Input Json,[{Key 1},{Value 1}])

Transforms a JSON input based on the provided JSLT code. Pass in the JSLT code in the JSLT parameter and the JSON to transform in the Input Json parameter. Use the optional key-value pair object to set variables you can use in your JSLT code.

When using the JSON Transformation Language formula, we recommend encoding the JSLT code in Base64 using a free web utility (like this one) and then wrapping the JSLT parameter in the Decode from Base64 formula. Encoding the JSLT code in Base64 prevents unknown characters used in JSLT from causing errors in the formula editor.

For more information about JSLT, see JSLT Tutorial. Another helpful resource for testing is the JSLT demo playground.

Input: JSLT((DecodeBase64("Lm5hbWU="),"{ "name": "Ashley Taylor", "age": 35, "city": "Granada Hills" }")

Result: "Ashley Taylor"

We recommend encoding all JSLT code in Base64 and then using the Decode from Base64 formula with the encoded JSLT. In the example above, the encoded JSLT is .name, accessing the name property. The formula can also be used as-is for very simple use cases, as in the second example below.

Input: JSLT(".name","{ "name": "Ashley Taylor", "age": 35, "city": "Granada Hills" }")

Result: "Ashley Taylor"

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

Regex Replace

RegexReplace({Field},{RegularExpression},{ReplaceWith})

Uses a regular expression to locate values in a provided {Field} and replaces them with the {ReplaceWith} value.

Input: RegexReplace({evelyn.rose@tonkean.com},'@([^@.]+)','@tonkorg')

Result: evelyn.rose@tonkorg.com

Remove Objects With Empty Fields From Array

RemoveObjectsWithEmptyFieldsFromArray({Array of Objects},{Fields})

Checks a passed-in {ArrayOfObjects} for any objects with no value for the specified {Fields} and removes those objects from the array.

This formula is often used in conjunction with Convert Inner Items to Array to remove unwanted line items from an array.

—

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

Unescape JSON

UnescapeJson({Field})

Unescapes a provided JSON string, returning it to standard JSON format.

—

Wrap HTML

WrapHtml({Input}, {HTML element}, {HTML element attributes}, {Self closing tag}, {Wrap with quotes})

Wraps an HTML element with specified HTML tags, enabling the element or elements to display on an item interface or other web page.

This formula is especially useful for displaying dynamic content as HTML. For example, you may have a JSON array of values that are updated as part of your workflow. With Wrap HTML, you can wrap this array in li tags to display it as a bulleted list on an interface.

Input: WrapHtml({Employee Name}, <b>, "style="text decoration:underline;"", true, false)

Result: Evelyn Rose

This is a simplified example intended to show the possible formula attributes. In most cases, the HTML element will be longer and more complex.

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

XML to JSON

XmlToJson({Field})

Converts a provided value from XML to JSON format.

Input: XmlToJson("<person><name>Ashley</name><city>Granada Hills</city></person>")

Result: {"name":"Ashley","city":"Granada Hills"}