Blendr.io knowledge base

Formulas in placeholders

Placeholders are used in "input fields" of blocks, in the Data Blend editor, to reference outputs of other blocks. Example of a placeholder:

You can click the "view" icon above the input field (on the right hand side) to switch from normal view to advanced view. Example of the same placeholder in advanced view:

For example, the placeholder { $.getContact.name } references the field "name" from the output of the block named "Get Contact".

Using formulas

It is possible to use formulas inside placeholders, to manipulate the data. The formula will be applied on the result of the placeholder.

The formula name is put in front of the "path" of the placeholder, inside the curly brackets. Some formulas have extra parameters, they are added behind the path with a comma.

Example of a placeholder: { $.path }

Example of the same placeholder with a formula added:
{ formula-name: $.path, parameter1, parameter2 }

Combining formulas

You can combine more than one formula. Add curly brackets around the inner formula, and add an outer formula around it.
Example:

{ outer-formula: { inner-formula: $.path, inner-parameters }, outer-parameters }

Text Functions

Uppercase and Lowercase

Changes a text to all capital characters (uppercase) or all lower case.

Usage: { uppercase:$.jsonpath }
Usage: { lowercase:$.jsonpath }

Example: { uppercase:$.getCompany.name }
Example: { lowercase:$.getCompany.name }

Capitalize name

Add capitals to all words in a name, but only when the full name is all small caps are all capitalized.

Usage: { capitalizeName: $.path }

Example:
"john doe" will be converted to "John Doe"
"JOHN DOE" will be converted to "John Doe"
"jan van den Broeck" will not be changed (since it already has one capital, this is to avoid changing intended lower case in words such as "de" or "van").

Trim

Trims a given character from the left and right of a string. The character to trim (remove) is optional, if it is not set, the text will be trimmed on spaces and line breaks.

Usage: { trim:$.jsonpath, "character_to_remove" }

Example: { trim:$.getCompany.url, "/" }

You can also use formulas "ltrim" and "rtrim" to only trim on the left or right hand side of a text.

Regex parse

Parses a text (string) from a given text based on a regular expression (regex). Only the first match of the regex will be returned. If you want to return all the matches, use "regexparseall" instead.

Usage: { regexparse:$.jsonpath, "regex" }

Example to parse the part after ".com" in a URL:
{ regexparse:$.getCompany.url, ".com(.*)" }

Example to parse only the first name or last name from a full name (assuming format "Firstname Lastname"):
Get first name: { regexparse:$.fullname, '^([^\s])' }
Get last name: { regexparse:$.fullname, '^[^\s]
\s(.*)' }

The first function will remove everything from $.fullname starting at the first space found. The second function will remove everything up to the first space.

See also: Useful regular expressions

Regex parse all

Parses a text (string) from a given text based on a regular expression (regex) and returns a single list (array) with all matches. This formula supports only one capturing group in the regex. If you only want the first match, use "regexparse" instead.

Usage: { regexparseall:$.jsonpath, "regex" }

See also: Useful regular expressions

Regex replace

Replaces a text (string) in another text based on a regular expression (regex). Each match of the regex will be replaced. The "new text" can have a placeholder "$1" to insert the text that matches a capture group from the regex such as e.g. "(.*)".

Usage: { regexreplace:$.jsonpath, "regex", "new text" }

Example that replaces ".be" in ".com" in a URL:
{ regexreplace:$.getCompany.URL, "(.*)be", "$1com" }

Example that removes special characters from a text (everything that is not a-z or a number):
{ regexReplace: $.getCompany.URL, "[^\w\s-_]", "" }

See also: useful regular expressions.

Replace

Replaces a text (string) in another text.

Usage: { replace:$.jsonpath, "old text", "new text" }

Example: { replace:$.getContact.name, "Mr", "Mister" }

Remove

Removes a text (string) from another text.

Usage: { remove:$.jsonpath, "wrong text" }

Example: { remove:$.getContact.name, "Mr" }

Substring

Returns part of a text (string) based on a fixed start position and length.

Usage: { substr:$.jsonpath, startpos, length }

Example: { substr:$.getContact.name, 5, 10 }

In the above example, the start position is 5 and the length of the text to return is 10 characters. Note that the first position of a text is 0.

String Position

Returns the position of the first occurrence a text in another text (first position is 0).

Usage: { strpos:$.jsonpath, "text to find" }

Following variants are also available:

strrpos: reverse search (right to left, finds the last occurrence of a text in another text)
stripos: case-insensitive search
strripos: case-insensitive reverse search

Number, Boolean, Text, List, Object

Apply "casting" on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using for example custom fields in an update, where Blendr.io does not know the type of the field. When you use the value "true" (typed in as text), this may cause an error because it is sent as the word "true" and not the boolean value True, this can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

Text Length

Returns the length of a text (number of characters including spaces, punctuation etc.).

Usage: { text_length: $.path }

toAscii

This formula will convert a text with special characters to a plain text with "ASCII" characters only. This is useful when sending data to a platform that does not handle special characters (e.g. UTF-8) well.

Usage: { toAscii: $.path }

Example:
When $.path value is e.g. "Gérard" the result will be "Gerard".

Explode

Converts a text with e.g. comma-separated items into a list.

Usage: { explode:$.someJsonPath } or { explode:$.someJsonPath, "character(s)" }

Example to explode on comma's (default): { explode:$.contactlist }

Example to explode on spaces: { explode:$.contactlist, " " }

Random Text

Generates a random text, useful to generate e.g. personal discount codes in a webshop.

Usage: { randomText:5 }

In the above example, 5 is the length. This parameter is optional. Result will be e.g. "fkelz".

Line break

Inserts a line break (new line) in a string or text, e.g. for formatting in an email.

Usage: {linebreak}

Convert Functions

Number, Boolean, Text, List, Object

Apply "casting" on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using for example custom fields in an update, where Blendr.io does not know the type of the field. When you use the value "true" (typed in as text), this may cause an error because it is sent as the word "true" and not the boolean value True, this can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

ISO To Country and Country To ISO

Convert 2-letter country codes (ISO 3166 Alhpa-2 codes) to the full country name in English, or vice versa.

Usage:
{ isotocountry: $.path, "language" }
{ countrytoiso: $.path, "language" }

The parameter "language" is optional. If used it has to be a 2-letter language code (EN, DE, FR...).

Examples:

{ isotocountry: $.path } will result in "France" when the value of $.path is equal to "FR" or "fr"

{ isotocountry: $.path, "DE" } will result in "Frankreich" when the value of $.path is equal to "FR" or "fr"

{ countrytoiso: $.path } will result in "FR" when the value of $.path is equal to "France"

Domain

Extract a domain name from a full website URL. Useful when cleaning data for data enrichment, where you look up company data using a data provider.

Usage: { domain:$.website }

The result will be "google.com" when $.website is e.g. "https://www.google.com/about".

Explode

Converts a text with e.g. comma-separated items into a list.

Usage: { explode:$.someJsonPath } or { explode:$.someJsonPath, "character(s)" }

Example to explode on comma's (default): { explode:$.contactlist }

Example to explode on spaces: { explode:$.contactlist, " " }

National Phone

Normalize a phone number into a national format (meaning without the international access code).

Usage: { nationalphone:$.tel }

For example a phone number "+32 (0)498667788" will be formatted into "498667788" (note that the leading zero is not added or included).

Phone

Normalize a phone number into an international format.

Usage: { phone:$.tel, 'countrycode' }

The second parameter is an optional country code, it is used to normalize a phone number when its international access code (e.g. "+44") is missing.

Example: { phone:$.tel, 'uk' } or { phone:$.tel }

For example a phone number "+32 (0)498667788" will be formatted into "+32 498 66 77 88".

To Phone

Tries to convert a given text to a valid phone number, it will make the text empty otherwise.

Usage: { toPhone: $.path }

Example:

{ toPhone: $.path } when value of $.path is "abc" this will result in "", and when value of $.path is "phone: 555-667788" this will result in "555-667788".

Number, Boolean, Text, List, Object

Apply "casting" on data, which means that you specifically convert an input or output to e.g. a boolean value (True/False). This is useful when using for example custom fields in an update, where Blendr.io does not know the type of the field. When you use the value "true" (typed in as text), this may cause an error because it is sent as the word "true" and not the boolean value True, this can be solved by using { boolean:true } instead.

Usage: { boolean:$path }

Example: { boolean:true }

Object

This formula turns a text that contains JSON data into an actual object, so you can e.g. assign it to a variable of type "object" or "list".

Usage: { object: $.path }

Example:
When $.path value is e.g. '[ "one", "two", "three" ]', the result will be a list with 3 items.

Json

This formula turns an object into a JSON text, so you can e.g. insert it in a text field (varchar) in a database.

Usage: { json: $.path }

Example:
When $.path value is e.g. a list with three items, the result will be a text such as '[ "one", "two", "three" ]'.

See also: how to Compare dates

Date Functions

Convert timezone

Converts a date time into a different time zone.

Usase: {converttimezone: 'datetime', 'source format', 'source timezone', 'destination format', 'destination timezone'}

Examples:
{converttimezone: '2020-01-27T17:55:38', 'Y-m-d\TH:i:s', 'Europe/Paris', 'Y-m-d\TH:i:s', 'UTC'}
{converttimezone: '2020-01-27 17:55:38', 'Y-m-d H:i:s', 'Europe/Paris', 'Y-m-d\TH:i:s', 'America/New_York'}

See also: how to Compare dates

Dates and timestamps

Returns a date and/or time for a given timestamp (e.g. "now") or converts a given date time from any input format into a new format, for example to convert "3/10/2020 14:00:00" to "2020-03-10T14:00:00Z".

Usage: { date: "timestamp or date time", "new format", "input format (optional)" }

Note that the third parameter "input format" is optional.

Example to get current date in format "20-03-18":
{ date: "now", "d-m-y" }

Example to get the date of yesterday in format "19-03-18":
{ date: "now - 1 day", "d-m-y" }

Example to get current date in ISO-8601 format, e.g. "2018-08-20T12:35:24+0000":
{ date: "now", "Y-m-d\TH:i:sO" }

Example to get a timestamp (epoch in seconds) for today plus 5 days, e.g. "1534232971":
{ date: "now + 5 days", "U" }

Example to get the epoch timestamps from the start and end of "Yesterday":
{date: 'now - 1 day midnight', 'U'}
{date: 'now midnight', 'U'}

Example to convert a unix timestamp (epoch in seconds) to a readable date:
{ date: $.epoch, "Y-m-d", "U" }

Example to convert a unix timestamp which is in milliseconds to a readable date (we divide the timestamp by 1000 first and round it):
{date: "{round:{divide: {$.epoch_milliseconds}, 1000 }} ", 'Y-m-d', 'U'}

List Functions

Count

Count the number of items in a list.

Example: { count:$.getContacts }

Result: e.g. 10

Get keys

Get the keys (fieldnames) of an object.

Usage: { getkeys:$object }

Example: { getkeys:$.getContact }

For example if the result of $.getContact is:
{
"name" : "John",
"email" : "[email protected]"
}

Then the above formula will return ["name", "email"].

Implode

Converts a list of items into one text, separated with a comma or another character.

Usage: { implode:$.someJsonPath } or { implode:$.someJsonPath, "character(s)" }

Examples:

  • implode using comma's (default): { implode:$.contacts[*].name }
  • implode using a comma and a space: { implode:$.contacts[*].name, ", " }
  • implode using line break: { implode:$.contacts[*].name, "{linebreak}" }

Sort

Sorts a list ascending.

Usage: { sort: $.list, "keyname" }

The parameter "keyname" is optional and should be used for lists of objects. The objects in the list will be sorted based on the value in this key. For example use keyname "id" when you have a list of objects of the form { "id": 123, "name": "John" }.

Note that this formula applies a natural sort (not an alphanumeric sort). This means that the result will be "1, 2, 3, 10" and not "1, 10, 2, 3". The list "A5, B3, C2" will be sorted to "C2, B3, A5". Use custom code if you want to apply a different type of sorting.

Example for a list of objects with field "id": { sort: $.list, "id" }

Example for a list of text or numbers: { sort: $.list }

Sortdesc

Sorts a list descending.

Usage: { sortdesc: $.list, "keyname" }

The usage is identical to the above "sort" formula.

Math Functions

Add, Substract, Multiply, Divide, Mod

Apply basic algebra.

Usage: { add: $.jsonpath1, $jsonpath2 }
Usage: { substract: $.jsonpath1, $jsonpath2 }
Usage: { multiply: $.jsonpath1, $jsonpath2 }
Usage: { divide: $.jsonpath1, $jsonpath2 }
Usage: { mod: $.jsonpath1, integer } (modulo)

Round

Returns the rounded number of a decimal number.

Example: { round:$.price }

Result: if $.price is equal to 10.30 the result is 10.

Example: { round:$.price, 2 }

Result: if $.price is equal to 10.336 the result is 10.34.

Min and Max

Returns the min or max from a list of items.

Example: { max:$.list[*].id }

Result: e.g. 10

Random Number

Generates a random number, useful to generate e.g. personal discount codes in a webshop.

Usage: { randomNumber:5 }

In the above example, 5 is the length. This parameter is optional. Result will be e.g. "65778".

Formula "guid"

Generates a random guid, e.g. 74ba8b5d-69bd-428c-ae9b-09b9c3fcbbac.

Usage: { guid }

File functions

Base64Encode

Used to convert binary file content (using the "Open file" block) to a base64 encoded string.

Usage: { $.base64encode: { $.readDataFromFile } }

Other Functions

If

Applies a condition on a value and returns one value if the condition is true, and another value if the condition is false.

Usage: { if: $.path1 operator operand, $.path2, 'elsevalue' }

In the above usage, "operand" can be a jsonpath $.path or a text (e.g. "string") or numeric (e.g. 123).

Example: { if: $.path1 = USA, $.path1, unknown country } --> this will replace the value of $.path1 with "unknown country" if it is not equal to "USA".

Following operators are available:

=       (equal)
==      (equal)
!=      (not equal)
<>      (not equal)
<       (smaller than)
>       (greater than)
<=      (smaller than or equal to)
>=      (greater than or equal to)
empty
notEmpty
contain
doesntContain
inList
notInList
isText
isNotText
isNumber
isNotNumber
isObject
isNotObject
isList
isNotList
isEmail
isNotEmail
isPhone
isNotPhone
isTrue
isFalse

These operators operate similar to conditions used in Condition blocks. See the Conditions for more information.

Map

Replaces a set of values with a different set of values.

Usage: { map: $.path, objectvariable, 'default' }

In the above usage, "objectvariable" can be e.g. {'key1':'replace1', 'key2':'replace2'}

Example: { map: $.path, {'UK':'England', 'USA':'America'}, 'default' } --> this will replace "UK" with "England" etc. and the result will be "default" if the value of $.path was not found in the list.

Examples with different notation:
map: $path, 'Belgium -> BE, France -> FR, ...', 'default'
map: $path, {"Belgium" : "BE", "France" : "FR", ...}, 'default'
map: $path, [{"Belgium" : "BE"}, {"France" : "FR"}, ...], 'default'

Remove Empty Properties

Removes the keys from an object, where the value is null or an empty string or a string containing only spaces.

Usage: { removeEmptyProperties:$object }

Example $.object:

{
  "name" : "",
  "address" : null,
  "email" : "[email protected]",
  "remarks" : "    ",
}

The result of applying removeEmptyProperties to $.object is:

{
  "email" : "[email protected]"
}

This formula can be used to avoid sending empty key/values to an endpoint, which would overwrite existing values. This scenario can happen when you build an object based on source data and some fields are missing in the source data. In that case you do not want to send e.g. "name: null" to a destination, this would make the name empty.

Formula "if not in list"

Checks if a value appears in a given list. If not the value is replaced with a given value.

Usage: { ifNotInlist: $.path, list, default value }

Example: { ifNotInlist: $.path, 'item1,item2,item3', null } --> this will replace the value of $.path with null if it is not equal to "item1", "item2" or "item3".

Examples using different notation:
ifNotInlist: $.path, ['FR','NL','BE'], default value
ifNotInlist: $.path, $.listvariable, default value

Formula "if greater than"

Checks if a value is greater than a given value. If so, the value is replaced with a given default value.

Usage: { ifGreaterThan: $.path, value, default value }

Example: { ifGreaterThan: $.path, 100, 50 } --> this will replace the value of $.path with 50 if its value is greater than 100.

Parse Label / Values

Used to parse a text with lines in the format "label: value", typically an email with a form submission. Use e.g. the IMAP connector to read emails from form submissions, and use this formule on the plain text of the email body, to get the structured data from the form submission:

Example text:

name: John Doe
email: [email protected]
question: Hi,
I would like to receive more info.

Usage: { parseLabelValues:$.getMail.text }

The result will be an object with keys "name", "email" and "question" and their corresponding values. Note that key "question" will have a value with a line break.

Error

Get the last error from one block in a Blend. This is used for custom error handling in a Blend.

{ $.state.blockName.error }

Example error:

Example to get the full error object :

{ $.state.getContact.error }

Examples to get one field from the above error:

{$.state.getContact.error.'response code'} ---> result: "404"
{$.state.getContact.error.'response body'.message} ---> result: "Invalid number for id"

Make sure to add single quotes around 'response code' and 'response body' since these properties (keys) contain a space character!

Formula to get current account, bundle, template, blend or job

Following placeholders can be used to retrieve the guid and/or name of the current provider (SaaS partner), account, bundle, template, blend or job:

{ providerguid }
{ providername }
{ accountguid }
{ accountname }
{ accountexternalid }
{ bundleguid }
{ templateguid }
{ templatename }
{ blendguid } or { widgetguid }
{ blendname }
{ jobguid }

See Definitions for more info on each of the above entities.

Formula "url" (incoming webhook URL from Blend) and "executiontoken"

Get the unique URL (REST API endpoint) of a Blend, that can be used to call the Blend from an external system. This URL is typically used to configure a webhook in a system, that needs to call the given Blend.

Usage: { url }

This placeholder is typically used in an output block (Show block) in the Setup of a Blend, to communicate the unique URL of the Blend to the user, so the user can configure it as a webhook in some other cloud application. Note that this is only needed for webhooks that are not natively supported by Blendr.io.

You can also get just the execution token of the webhook URL of a Blend (REST API endpoint):

Usage: { executiontoken }

Note that in the above formula { url }, the execution token is already included in the querystring.

Formula to get index of a loop

Get the index (0, 1, 2, 3...) from a loop. This formula can only be used inside a loop.

{ $.loopblockname.index }

Example in a loop of a block "list contacts" : { $.listContacts.index }

Hash

Creates a hash from a given string (text).

Usage: { hash:$.text, 'algorithm' }

Available algorithms: md4, md5, sha1, sha256, sha512.
If you want to apply a different algorithm, use a Custom Code block instead.

Csv

Converts a flat JSON object with key/values into a single CSV line. Used to write data from a source to a CSV file on cloud storage (e.g. Dropbox, AWS S3 etc.).

Usage: { csv: $.object, 'columns', 'delimiter' }

  • $.object needs to be a flat JSON object, which means keys with values, without nested properties or arrays (use formula "flatten" if needed)
  • Columns: optional, a string or list (array) with the columns in the CSV file
  • Delimiter: optional, a string for the delimeter to use in the CSV line, default is a comma

Example JSON objects:

$.myObject1:

{
  "key3": "val 003",
  "key1": "val 001"
}

$.myObject2:

{
  "key1": "val 011",
  "key2": "val 022",
  "key3": "val 033"
}

$.myObject3:

{
  "key1": "val 111",
  "key2": "val 222"
}

$.columns:
[ "key1", "key2", "key3" ]

Applying the following CSV formulas:

{csv: $.myObject1, $.columns}
{csv: $.myObject2, $.columns}
{csv: $.myObject3, $.columns}

Will provide following result (extra spaces added in first line of example for readability):

"val 001",         ,"val 003"
"val 011","val 022","val 033"
"val 111","val 222"

Note that the CSV formula allows you to preserve the order as well as the correct columns in a CSV file, whereas the order of keys can be different across objects and whereas some keys maybe missing.

Flatten

Flattens any single object to a new object with key/values without any nested properties. Used to write an object to e.g. a CSV file.

Usage: { flatten: $.myObject }

Example:

$.myObject:

{
  "name": "John",
  "address": {
    "city": "Paris"
  },
  "orders": [
    {"id": 55},
    {"id": 66}
  ]
}

The result of { flatten: $.myObject } is:

{
  "name": "John",
  "address.city": "Paris",
  "orders.0.id": 55,
  "orders.1.id": 66
}

Updated 9 days ago

Formulas in placeholders


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.