Skip to main content

Formulas: syntax, operators and functions

Use formulas for optimal data processing

You are here: In formula editor for "product filter" or "own data fields".

Syntax and Operators

The required syntax for formulas essentially corresponds to that of many programming languages. Defined parameters by their name, as well as numbers and brackets can be used. Text must be within double quotes.

In addition, the following operators may be used:

Function

Operator

Text

field

Number

field

Own

datafield

Filters

equal | unequal

== | !=

x

x

(x)

x

smaller | larger

< | >

x

x

(x)

x

“and”-operator

AND

x

x

(x)

x

“or”-operator

OR

x

x

(x)

x

multiplication

*

x

x

x

divide

/

x

x

x

addition

+

x

x

x

x

subtract

-

x

x

x

modulo

%

x

x

x

x: useable - (x): useable in complex structures

Formula functions

In Formulas, aside from the basic operations such as addition or multiplication, you will find "functions". This site will give you an overview of all the functions you can use.

You can also use your data fields as function parameters.

To ensure that your formulas output the desired data, you have a large selection of predefined functions and operators at your disposal. In addition, you must adhere to a certain syntax.

If requested, we can implement a personalized function for you. It is also possible to bind web service of stock or products in your ERP system to a function's name, so you can create own data fields and filters with your current products.

Simply contact us and we will be more than happy to assist you!

Overview of the functions

An overview of all functions can be found below.

Here, you get an overview of the functions that you can use in Formulas. Every entry has the following values:

  • Name: The name enables you to use the function in your formula.

  • Parameter: Number of parameters expected by the function.

  • Entry: Type of parameter expected by the function.

  • Issue: Type of parameter given back by the function. Could depend on the type of entry.

  • Description: Description of the function.

  • Example: Example of use.

If you do not find the function you are looking for, please let us know. We can broaden our range of functions and we would be delighted to hear any feedback you have got for us!

Functions

Every function entry in the list below has the following values:

  • Name: name of the function and enables the use in the formula

  • Usage: a short description of the normal use cases for this function

  • Input: what kind of data can be used (number, text, true/false)

  • Output: what kind of data will you get from the function

  • Information: short description of all parameters and important information

  • Example: samples for usage of the function and typical errors

Functions are sorted by general use cases:

Statistic function

abs(inputNumber)

Returns the absolute value of a single given number. Negative values are converted to positive values.

settings and samples

Input: Number

Output: Number

Information: -

Example:

abs(-2.95) = 2.95

abs(4.55) = 4.55

abs(-0) = 0

abs(-80.7) if “-80.7” is text = Evaluation error! → can be solved with abs(to_number(-80.7))

avg(inputNumber, inputNumber, n)

Calculates the average of the given values, works with integer and decimal values.

settings and samples

Input: Number

Output: Number

Information: -

Example:

avg(3, 7, 8, 6) = 6

avg(30,40,3.80,963.2) = 259.25

avg(3, “7”, 8, 6) = Evaluation error! → 7 is text in this case

min(inputNumber, inputNumber,…) / max(inputNumber, inputNumber,…)

Calculates the minimum (lowest) or maximum (largest) of the given values, works with integer and decimal values.

settings and samples

Input: Number

Output: Number

Information: -

Example:

max(3, 7, 8, 6) = 8

max(30,40,3.80,963.2) = 963.2

max(30,40,"3.80",963.2) = Evaluation error! → 3.80 is text in this case

min(3, 7, 8, 6) = 3

min(30,40,3.80,963.2) = 3.8

sum(inputNumber, inputNumber, inputNumber,…)

Returns the sum of the given values, works with integer and decimal values.

settings and samples

Input: Number

Output: Number

Information: -

Example:

sum(1,2,2,3,4,4,56,7) = 79

sum(30,40,3.80,963.2) = 1037

sum(30,40,"3.80",963.2) = Evaluation error! → 3.80 is text in this case

Rounding functions

ceil(inputNumber) / floor(inputNumber)

Returns the given number rounded up/down to the next whole number.

settings and samples

Input: Number

Output: Number

Information:

The inputNumber has to be given with a dot as decimal separator. If the number is only available with a comma, then use to_number(inputNumber,”.”) to provide the correct format.

Example:

ceil(2.4) = 3

ceil(5.937653) = 6

ceil(4) = 4

ceil(-7.4) = -7

floor(2.4) = 2

floor(5.937653) = 5

floor(4) = 4

floor(-7.4) = -8

ceil(“5.3”)/floor(“5.3”) = Evaluation error! → 5.3 is text in this case

ceil(5,4)/floor(5,4) = Syntactic error → the number has to be writing with a dot as decimal separator

rand()

Returns a random number between 0 and 1.

settings and samples

Input: Number

Output: Number

Information: The random number always begins with 0. and has 9 or 10 digits

Example:

rand() = 0.5145997042

rand(5) = Syntactic error → there should be no value between the brackets

round(inputNumber, decimalPosition)

Rounds a number to the given decimal digits or a full integer.

settings and samples

Input: Number

Output: Number

Information:

  • inputNumber → the number which has to be rounded

  • decimalPosition → defines the digits after the separator to which the inputNumber has to be rounded, if no decimalPosition is given it will be taken as 0 (full integer).

Example:

round(2.4) = 2 → standard which is defined as 0

round(3.589, 2) = 3.59

round(5.44,8) = 5.44 → the decimal digits after the separator will be maximum as much as the inputNumber

round(“3.589”, 2) = Evaluation error! → 3.589 is text in this case

round_mode(inputNumber, decimalPosition, “roundMode”)

Rounds the given number to given decimal places. You can also state the rounding mode.

settings and samples

Input: Number

Output: Number

Information:

  • inputNumber → checked input

  • decimalPosition → decimal places

  • roundMode → defines how the inputNumber has to be rounded

    • UP

    • DOWN

    • HALF_UP (accounting up from xx,5)

    • HALF_DOWN (accounting down below xx,5)

    • FLOOR

    • CEILING

The third value has to be in “ “ to work. You can deliver text as number in this function.

Example:

round_mode(3.6736, 2, "UP") = 3.68

round_mode(3.6736, 2, "DOWN") = 3.67

round_mode(3.6756, 2, "HALF_UP") = 3.68

round_mode(3.6756, 2, "HALF_DOWN") = 3.67

round_mode(3.6736, 2, "FLOOR") = 3.67

round_mode(3.6736, 2, "CEILING") = 3.68

round_mode("3.6736", 2, "UP") = 3.68 → you can use text as inputNumber and it will work

round_mode(3.6736, 2, UP) = Evaluation error → there are missing “ for the roundMode

Control functions

case(inputSource, inputValue, n, default)

Checks an inputSource for a list of values and provides an individual output for each value. There can be defined a default value too, if non of the checks before gives an match.

settings and samples

Input: Number or Text

Output: Number or Text

Information:

  • inputSource → the source to be searched into

  • inputValue - n → values which has to be checked for availability in the inputSource, has to be a pair of 2 for every check (input and output)

  • default → if none of the inputValues are available

The default value is not mandatory, but recommended. You can compare data fields too or use them for output. For a better overview especially with many inputValues it is recommended to write it like:

case(inputSource,
     inputValue1, output1,
     inputValue2, output2,
     default)

Example:

inputSource = “Testtext”

case(inputSource, “Testtext”, “Output 1”, “Testtext2”, “Output 2”) = “Output 1”

inputSource = 12345

case(inputSource, 49054, “number 1”, 12345, “number 2”) = “number 2”

inputSource = “Unknown”, inputSource2 = “NoValueAvailable”

case(inputSource, “Test1”, “Output1”, “Test2”, “Output2”, inputSource2) = “NoValueAvailable”

inputSource = “Unknown”

case(inputSource, “Test1”, “Output1”, “Test2”, “Output2”) = Evaluation error → no default defined

if(condition, trueValue, falseValue)

If(…) can be used if a check is more complex or nested, then you can check it with the case(…) function. The check will always give an TRUE or FALSE back.

settings and samples

Input: -

Output: Number, Text or complex formula

Information:

  • condition → the condition which has to be evaluated as true or false

  • trueValue → the value or formula to be returned if the condition is evaluated as true

  • falseValue → the value or formula to be returned if the condition is evaluated to false

The if(…) function can be nested as much as needed, but you have to monitor the structure and make clear that every ( ) pair is closed correctly.

For a better overview especially with many nested if-functions it is recommended to write it like:

if(condition1, 
   trueValue1, 
   if(condition2,
      trueValue2,
      falseValue
   )
)

For the condition you can use simple operators like ==, !=, >, < , >=, <= or complex formulas.

Example:

testvalue = 7

if(testvalue >= 3, “true”, “false”) = “true”

testvalue = “testtext”

if(is_text(testvalue), “is_text”, “is_notext”) = “is_text”

testvalue = 12345

if(is_text(testvalue), “is_text”, “is_notext”) = “is_notext”

testvalue = 12345, test2 = “Testtext”

if(is_text(testvalue) AND is_text(test2), “is_text”, “is_notext”) = “is_notext”

testvalue = 5, testvalue2 = 13, testvalue3 = 19485

if(testvalue < 10, if(testvalue2 >=12, testvalue3, “wrong”), “wrong”) = 19485

testvalue = 5, testvalue2 = 13, testvalue3 = 19485

if(testvalue < 10, if(testvalue2 >=12, testvalue3, “wrong”) = Syntactic error → there is one falseValue and a bracket missing at the end

switch(positionInList, inputList) / switch_default(positionInList, inputList, default)

A value can be checked and gives an output depending of the position in the list given in the function.

settings and samples

Input: Number

Output: Number or Text

Information:

  • positionInList → position in the given list

  • inputList → the list to be searched into

  • default (only for switch_default) → if the positionInList is higher then the counts in the provided list, there can be a default value which will be the output instead

switch_default(…) is more recommended, because it produces less errors!

Example:

switch(5, 1, 8, 10, “Test”, “Text”, 1000) = “Text”

switch(1000, 1, 8, 10, “Test”, “Text”, 1000) = Evaluation error → lesser then 1000 elements in the list

switch_default(1000, 1, 8, 10, “Test”, “Text”, 1000, “toShortList”) = “toShortList”

Functions for text

base64_encode(inputText, coding) / base64_decode(inputText, coding)

Encode or decode a given text with the selected coding.

settings and samples

Input: Text

Output: Text (encoded or decoded)

Information:

  • inputText → the text to be encoded or decoded

  • coding (OPTIONAL) → selected coding, default is UTF8

Supported coding at the moment is UTF8 and ANSI.

Example:

base64_encode("This is a test", "UTF-8") = “VGhpcyBpcyBhIHRlc3Q=”

base64_decode("VGhpcyBpcyBhIHRlc3Q=", "UTF-8") = “This is a test”

base64_decode("VGhpcyBpcyBhIHRlc3Q=") = “This is a test” → coding is optional

base64_encode("This is a test", UTF-8) = Evaluation error → coding is optional, but if a value is given, the syntax has to be correct (in this case “ “ are missing)

capitalize(inputText, wordToLowercase)

Converts the first letter of every word in capital letters.

settings and samples

Input: Text, true/false

Output: Text

Information:

  • inputText → the text to be transformed

  • wordToLowercase (OPTIONAL) → can be “true” of “false”, if “true” is selected for all other letters “lowercase” is used (false is default)

Example:

capitalize("This is expensive") = "This Is Expensive"

capitalize("THIS IS EXPENSIVE", true) = "This Is Expensive"

capitalize("THIS IS EXPENSIVE", false) = "THIS IS EXPENSIVE”

capitalize("12345FFFF", true) = “12345ffff” → in this case a number is the first letter which is not transformed

capitalize("TEST", TRUE) = Evaluation error → true/false has to be in lowercase

case_in_string(inputText, searchText, trueValue, falseValue)

Check a text if another text can be found and provides an output depending on the result.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • searchText → text what you want to search for in inputText

  • trueValue → output, if the search was successful

  • falseValue → output, if the search was not successful

Example:

case_in_string("This is a formula test", "test", "yes", "no") = "yes"

case_in_string("This is a formula test", "cooking", "yes", "no") = “no”

case_in_string("This is a formula test", "test", "yes", no) = Syntactic error → missing “ “ for falseValue

case_in_string("This is a formula test", "test", "yes", "no",”maybe”) = Syntactic error → to many parameters given

case_in_string_concat(inputText, searchText, trueValue, n, separator)

Checks for several texts in the base text and concates the found results.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • searchText - n → checked for texts in the inputText

  • trueValue → if the result for searchText is true, this will be concated with the given “separator”

  • n → more searchText/trueValue combination to look for in the inputText

  • separator → given separator which is used to separate each single text in the concatination of all found texts

For every searchText there has to be a pair of values: searched text, value if the text is found. If only one value is given, it produces an error.

There is no falseValue which can be defined!

Example:

case_in_string_concat(“This is a test text”,”This”,”You”,”is”,”are”,”a test text”,”great”,”|”) = “You|are|great”

case_in_string_concat(“This is a test text”,”This”,”You”,”are”,”a test text”,”great”,”|”) = Syntactic error → is one variable missing it comes to an error, it works if a pair of variables are deleted

clean_html(inputText)

Clean a text of all HTML/XML-tags.

settings and samples

Input: Text

Output: Text

Information:

All information which are written between < and > are deleted, independently of the information which is provided in it! Other structural languages are not effected like JSON.

Example:

clean_html("<html><body>Hello World</body></html>") = Hello World

clean_html('<this><is><no><html>Hello World') = Hello World

clean_html(<html><body>Hello World</body></html>) = Syntactic error → missing “ “ for inputText

convert_currency(inputNumber, inputBasecurrency, inputTargetcurrency)

Converts a given currency in another with the daily rate.

settings and samples

Input: Number, Text in ISO norm

Output: Number

Information:

  • inputNumber → number which has to be converted

  • inputBasecurrency → currency in which the base value is given

  • inputTargetcurrency → currency you want to convert to

All actual ISO currency codes can be used. For samples you can look here

ISO 4217 . The rate which is used can be checked with “currency_rate(…)”.

Example:

convert_currency(2.5, "EUR", "JPY") = 354.13

convert_currency(2.5, "EURO", "YEN") = Syntactic error → please use an ISO conform currency code

convert_currency(2,5, "EUR", "JPY") = Syntactic error → the given value has to be with a dot as decimal separator

count(inputValue, valueToCount) / count_regexp(inputValue, valueToCount)

Counts the frequency of a single letter or word.

settings and samples

Input: Text or regular expression

Output: Number

Information:

  • inputValue → the value to be searched into

  • valueToCount → character or word which has to be counted or a regular expression

For a single character or word please use “count(…)” and for regular expressions or whole sentence “count_regexp(..)”

Example:

count("This is only a sample text","i") = 2

count(“This is only a sample text”,”u”) = 0

count("This is only a sample text","") = 0 → because nothing is giving it can be checked for

count("This is only a sample text","only") = 1 → because the check is for the word “only” not the letters

count_regexp("This is only a sample text","") = 27 → all letters are counted including one of the “

count("This is only a sample text") = Syntactic error → the second variable is missing

currency_rate(inputBasecurrency, inputTargetcurrency)

Shows the rate between two currencies.

settings and samples

Input: Text in ISO norm

Output: Number

Information:

  • inputBasecurrency → currency which is the base

  • inputTargetcurrency → currency you want to convert to

All actual ISO currency codes can be used. For samples you can look here

ISO 4217 . Can be checked to understand the rate which is used in “convert_currency(…)”.

Example:

currency_rate("EUR", "JPY") = 141.65

currency_rate("EURO", "YEN") = Syntactic error → please use an ISO conform currency code

escape_html(inputText)

Replaces special characters and umlauts with the HTML equivalent.

settings and samples

Input: Text with special characters or umlauts

Output: Text with HTML-entities

Information: -

Example:

escape_html("verschlüsseln") = “verschl&uuml;sseln”

escape_html("üöäß&") = “&uuml;&ouml;&auml;&szlig;&amp;”

escape_html(“?!”) = “?!” → not all special characters are replaced

escape_html(verschlüsseln) = Syntactic error → missing “

in_list(searchText, inputList, valueSeparator)

Checks if a value is in a provided list.

settings and samples

Input: Text

Output: true / false

Information:

  • searchText→ the text to be searched into

  • inputList → list of values - divided by the “separator” - where searchText has to be searched for

  • valueSeparator → separator which divides the single entries of the list, can be , ; or :

Example:

in_list(“text”,”test;text”,”;”) = true → the search “text” is in the list

in_list(“somethingelse”,”test:text”,”:”) = false → the search “somethingelse” is not in the list

in_list(“text”,”test|text”,”|”) = false → the wrong separator is selected (the search does not work properly)

in_list(test,”test,text”,”,”) = Syntactic error → missing “ “ for searchText

in_string(inputText, searchText)

Checks if a text is in another text.

settings and samples

Input: Text

Output: true / false

Information:

  • inputText → the text to be searched into

  • searchText → the value which has to be checked for, can be partial

Example:

in_string("price with deliverycosts", "delivery") = true → searchText is in the provided inputText

in_string("price with deliverycosts", "co") = true

in_string("price with deliverycosts", "items") = false → searchText is not in the provided inputText

in_string(this is the provided text,”provided”) = Syntactic error → missing “ “ in the inputText

in_string_regexp(inputText, searchText)

Checks if a regular expression is in another text.

settings and samples

Input: Text or regular expression

Output: true / false

Information:

  • inputText → the text to be searched into

  • searchText → the value which has to be checked for, can be partial

Example:

in_string("price with deliverycosts", "price|costs") = true → both searchTexts are in the provided inputText

in_string("price with deliverycosts", "co") = true

in_string("price with deliverycosts", "u|a|ö|ä|ü") = false → none of the searchTexts are in the provided inputText

in_string(this is the provided text,”provided”) = Syntactic error → missing “ “ in the inputText

is_empty(inputValue)

Checks if a given value is empty or not and returns true or false.

settings and samples

Input: -

Output: true / false

Information:

This formula only works for the data type “Text”. If an data field is configured as “Number” the result is in any case “false”, because a numbered data field will be 0 if it is empty.

Example:

datafield1 = “test”, datafield2 = EMPTY, datafield3 = 0 (datatyp number)

is_empty(datafield1) = false

is_empty(datafield2) = true

is_empty(datafield3) = false → because the datatyp “number” provides a number or 0 and so the datafield will be never empty

is_empty(“test”) = false → a given text can be checked without a datafield

is_empty(test) = Evaluation error → the formula tried to check the datafield “test” which is not defined

is_number(inputValue)

Checks a given value if it is a number or not and returns a true or false.

settings and samples

Input: -

Output: true / false

Information:

If the inputValue is a number, it can be provided as number or text. If inputValue is a text, it has to be provided as text.

Example:

is_number(“5”) = true

is_number(5) = true → the inputValue can be a text or a number

is_number(“test Text”) = false

is_number(test text) = Evaluation error → if a text is given, it has to be in “ “

left(inputText, numberOfCharacters)

Returns a given number of characters, starting from left to right.

settings and samples

Input: Text, Number

Output: Text

Information:

  • inputText → the text to be searched into

  • numberOfCharacters → Characters which have to be returned, starting from left. The first digit begins with 1.

For numberOfCharacters it is possible to give a number larger then the initial text. Then you will get the whole text as result. Negativ numbers are not allowed, this leads to an error.

Example:

left("This is a test text", 5) = “This”

left("This is a test text", 5000) = “This is a test text” → the number is larger then the text, so the whole text will be returned

left("This is a test text", 0) = ““ → The number indicates that zero digits have to be returned.

left("This is a test text", -5) = Evaluation error → negativ numbers are not allowed.

len(inputText)

Checks how many characters the given text has.

settings and samples

Input: Text

Output: Number

Information:

The output only returns a integer of the characters which are counted (including white space), no kind of additional information. The result can be used in any kind of mathematical formulas.

Example:

len("Article") = 7
len(" ") = 1
len(" Article ") = 9

len(Text) = Evaluation error → missing “ “ for the inputText

lookup(LOOKUPinformation, uniqueKey)

Add information from another import feed with help of a unique key.

settings and samples

Input: -

Output: -

Information:

  • LOOKUPinformation → the data field from the separate import feed which has to be returned, the data field in the formula has to be named with “LOOKUP” at the beginning

  • uniqueKey → a unique identifier, which is used to identify the information in the additional feed

The additional feed don’t has to create new articles to use the information. This is very useful for cases where the identifier of the additional feed does not match with the Masterfeed (e.g. a category tree).

The uniqueKey has to be defined as data field in the formula and can not be written directly in the function.

It is recommended to ask your account manager for detailed information for this function!

Example:

LOOKUPpath = “10010 / samplecategory”, category = “10010”

lookup(LOOKUPpath,category) = “samplecategory”

lookup(LOOKUPpath,”10010”) = Syntactic error → the uniqueKey is not defined in a datafield

lookup(path, category) = Evaluation error → the datafield of the additional feed does not start with LOOKUP

lower(inputText)

Converts and returns the given text into a lower case.

settings and samples

Input: Text

Output: Text

Information: -

Example:

lower(“Test Text for Converting”) = “test text for converting”

lower(“TEXT IN CAPITAL LETTERS”) = “text in capital letters”

lower(Test Text) = Syntactic error → missing “ “ in the inputText

md5(inputValue)

Creates a md5-hash of a given value.

settings and samples

Input: Number, Text

Output: md5-hash

Information: -

Example:

md5("sample_value") = “87e7c049f8c6caa33ec94a46c3e904b0”

md5(“5”) = “e4da3b7fbbce2345d7772b0674a318d5”

md5(5) = Evaluation error → missing “ “ for the inputValue

mid(inputText, startPosition, returnDigits)

Returns a number of characters of a text, starting from a given position.

settings and samples

Input: Text, Numbers

Output: Text

Information:

  • inputText → text from which some characters must be returned

  • startPosition → indicates the starting position in the inputText where to start from

  • returnDigits → the amount of digits which need to be returned starting from the startPosition

Please note that the first digit starts with the position 0. All white spaces have to be counted too.

Example:

mid("This is a test text", 0, 5) = “This ”

mid("This is a test text", 9, 7) = “ test t”

mid(This is a test text, 3, 6) = Syntactical error → missing “ “ at inputText

mid("This is a test text", 1000, 5) = Evaluation error → the start position is out of range in the inputText

remove_duplicates(inputText,separator)

Removes duplicate words from a text.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text which has to be checked for duplicates

  • separator (OPTIONAL) → changes the logic of the formula and removes every duplicated character and returns only the unique characters divided by the separator. As separator you can only use |, with the wrong separator the formula does not work

Be sure to use the right version of the formula. With the separator “|” it removes duplicated characters, without the separator it removes only duplicated words.

Example:

remove_duplicates("This is a test test text text") = “This is a test text”

remove_duplicates("This is a test test test text text text test test test test","|") = “T|h|i|s| |a|t|e|x”

remove_duplicates("This is a test test text text",”;”) = “This is a test test text text" → because of a wrong separator, the formula does not work

remove_duplicates(This is a test test text text) = Syntactical error → missing “ “ at inputText

remove_non_printable_characters(inputText)

Removes non visible special characters or symbols like emojis from a text.

settings and samples

Input: Text

Output: Text

Information:

Especially for automatic functions like creating or mapping categories or standard data fields for marketplaces, it is necessary to remove this characters to prevent errors!

Example:

remove_non_printable_characters(“This is a text with ⭐ and 🎮”) = “This is a text with and ”

replace(inputText, oldText, newText)

Search for a text and replaces it with another one.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • oldText → text which is going to be replaced

  • newText → new text to be added

Special characters can be replaced too. In some cases it is recommended to use ' ' instead of “ “ specially when the special character “ has to be replaced or of the inputText contains this character.

Example:

replace("the article is refurbished", "refurbished", "new") = "the article is new"

replace(“123456”,”6”,”7777”) = “123457777”

replace("This text will be very good","test","text") = the inputText won’t be changed, because the oldText is not in it

replace(“This text will be “very” good”,”good”,”bad”) = Syntactical error → in the inputText is a “ so you have to use ' ' in the formula to get the correct result

replace_regexp(inputText, regularExpression, newText)

Search for a regular expression and replaces it with another one.

settings and samples

Input: Text, regular Expression

Output: Text

Information:

  • inputText → the text to be searched into

  • regularExpression → the regular expression which is going to be replaced

  • newText → new text to be added

Example:

replace_regexp("This is a regular expression with a number 123","[a-z]","-") = "T--- -- - ------- ---------- ---- - ------ 123" → replaces every expression which is found with the new text

replace_regexp("This is a regular expression with a number 123","[a-z1-9]","") = "T" → the regular expression selects all letters and numbers, with addition A-Z the T would be deleted too

replace_regexp(a regular expression,"[a-z1-9]","") = Syntactical error → missing “ “ at inputText

replace_regexp_list(inputText,searchText,replaceText)

Search for different texts and if a search is successful, this text will replaces by another one.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • searchText → the text which has to be replaced, if in the inputText (can be 1-n texts)

  • replaceText → the text which replaces the searchText, if it is found (can be 1-n texts)

searchText and replaceText has to be a pair, if you want to delete one of these data, then you have to delete both. Only deleting one of them leads to an error.

Example:

replace_regexp_list("This is a test text","This","That","is","was","text","block") = “That was a test block”

replace_regexp_list("This is a test text","no","That","number","was","there","block") = "This is a test text" → the inputText is unchanged, because the searchTexts are not found

replace_regexp_list("This is a test text") = Syntactical error → no searchText or replaceText is given or there is one data of the pair missing

right(inputText, numberOfCharacters)

Returns a given number of characters, starting from right to left.

settings and samples

Input: Text, Number

Output: Text

Information:

  • inputText → the text to be searched into

  • numberOfCharacters → Characters which have to be returned, starting from left. The first digit begins with 1.

For numberOfCharacters it is possible to give a number larger then the initial text. Then you will get the whole text as result. Negativ numbers are not allowed, this leads to an error.

Example:

right("This is a test text", 5) = “ text”

right("This is a test text", 5000) = “This is a test text” → the number is larger then the text, so the whole text will be returned

right("This is a test text", 0) = ““ → The number indicates that zero digits have to be returned.

right("This is a test text", -5) = Evaluation error → negativ numbers are not allowed.

search(inputText, searchText, occurrence)

Returns the first found position of a searched character or text in a given source.

settings and samples

Input: Text

Output: Number

Information:

  • inputText → the text to be searched into

  • searchText → The character or text which has to be searched for in inputText

  • occurrence (OPTIONAL) → Optional specification of which occurrence should be considered in the case of multiple occurrences (1-#occurrences).

All search-formulas can be combined and can use the result from a higher search (nested searches).

Example:

search(“This is a sample text”, “a”) = 8

search("This is a sample text", "text") = 17 → returns the position of the first character of the search

search(This is a sample text, "text") = Syntactical error → missing “ “ somewhere in the formula

search(“This is a sample text”, “a”, 2) = 11 → index of second “a” in inputText

search_and_cut(inputText, searchText)

Searches for a text in a source and returns everything till the search position.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • searchText → the text which has to be searched for in the inputText, if the search found something, the inputText will be returned until the position where the search was successfully

All search-formulas can be combined and can use the result from a higher search (nested searches).

Example:

search_and_cut("This is a test text","is a") = “This “

search_and_cut("This is a test text","is") = “Th” → the search takes the first found source

search_and_cut("This is a test text","not") = "This is a test text" → returns the full inputText because the searched word is not found

search_and_cut("This is a test text",is a) = Syntactical error → missing “ “ somewhere in the formula

search_and_cut_back(inputText, searchText) / search_and_cut_back_v2(inputText, searchText)

Searches for a text in a source and returns everything from the search position.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into

  • searchText → the text which has to be searched for in the inputText, if the search found something, the inputText will be returned from the first position of the first character where the search was successfully

All search-formulas can be combined and can use the result from a higher search (nested searches).

Example:

search_and_cut_back("This is a test text","is a") = “s a test text” → the return counts from the first character of the successful search (in this case the “i”)

search_and_cut_back("This is a test text"," test") = “test text”

search_and_cut_back("This is a test text","not") = "This is a test text" → returns the full inputText because the searched word is not found

split(inputText, separator, outputPosition)

Divides a text be a separator into a array and returns the chosen position in the array.

settings and samples

Input: Text

Output: Text

Information:

  • inputText → the text to be searched into, 1-n single texts divided by the separator

  • separator → separator which divides each single text block in inputText

  • outputPosition → returns the position between the separator, starts with 1

As separator every character can be used, but it should be clear that this character is not part of the inputText which have to be returned.

Example:

split("Test|Text|for|this|formula","|",2) = “Text”

split("Test|Text|for|this|formula",";",2) = EMPTY → the wrong separator is selected

split("Test|Text|for|this|formula","|",12) = EMPTY → the outputPosition is out of range (5 is the maximum for this inputText)

split("Test|Text|for|this|formula",|,2) = Syntactical error → missing “ “ somewhere in the formula

substr(inputText, startPosition, endPosition)

Returns a part of a text between the given start and end position.

settings and samples

Input: Text, Number

Output: Text

Information:

  • inputText → the text to be searched into

  • startPosition → indicates the starting position of the subtext, starts with 0

  • endPosition → indicates the ending position and the characters which has to be returned

startPosition and endPosition can be combined with other functions like search(…), if the position is not the same in any cases or not known.

Example:

substr("This is a test text", 5, 17) = “is a test te”

substr("This is a test text", 0, 5) = “This “

substr("This is a test text", 1000, 5) = Evaluation error → if the startPosition is before the endPosition or one of both positions is out of range of the inputText

substr(This is a test text, 0, 5) = Syntactical error → missing “ “ somewhere in the formla

to_currency(inputNumber, decimalSeparator) / to_currency(inputNumber, decimalSeparator, currencyISO, currencySymbol)

Transforms a number to currency with a chosen decimal separator, optional a currency symbol can be added.

settings and samples

Input: -

Output: -

Information:

  • inputNumber → a given number which has to be converted to a currency, can be a number or text

  • decimalSeparator → decimal separator which has to be used for the currency (has to be written in “ “)

  • currencyISO (OPTIONAL) → if used, it adds a currency in ISO3 to the number

  • currencySymbol (OPTIONAL) → if used, it has to be true or false and if set to true a currency symbol is available for the currency which is selected for currencyISO, then a symbol (special character) instead of the ISO3 code is used

currencyISO and currencySymbol have to be used together. If one option is chosen then the other one has to be chosen too.

Example:

to_currency(4.5, “,”) = “4,50”

to_currency(“4.5”, “.”, “EUR”, false) = “EUR 4.50”

to_currency(“4.5”, “.”, “EUR”, true) = “€ 4.50”

to_currency(4.5, . , EUR, true) = Syntactical error → missing “ “ somewhere in the formula

to_number(inputText, deleteTextOption)

Converts a given text into a number and deletes all characters which are no number.

settings and samples

Input: Text, true / false

Output: Number

Information:

  • inputText → a text which has to be converted to a number

  • deleteTextOption → set this on "true" if all text except of the numbers has to be deleted first, "false" is the default setting

Example:

to_number("5") = 5 → as number

to_number("5,6789 is a decimal number", true) = 5,6789 → as number

to_number("5 is a number and 6 too", true) = 56

to_number("5 is a number and 6 too") = Evaluation error → the deleteTextOption has to be true in this case, false (default) will lead to this error

to_number(5 is a number and 6 too, "true") = Syntactical error → missing “ “ somewhere in the formula

to_text(inputNumber) / str(inputNumber)

Transforms a given number into a text.

settings and samples

Input: Number

Output: Text

Information:

This function works with integer and decimal numbers, but only with a dot as decimal separator, not with a comma.

All functions which are only working with a text can be used after the transformation.

Example:

to_text(12345) = “12345” → as text

to_text(5.6789) = “5.6789” → as text

to_text(6,8467) = Syntactical error → wrong decimal separator

to_text(“6,8467”) = “6,8467” → if as inputNumber a text is given, it will be returned 1:1

trim(inputText)

Removes leading and trailing spaces from the given text.

settings and samples

Input: Text

Output: Text

Information: -

Example:

trim(“ Test Text “) = “Test Text”

trim(“Test Text “) = “Test Text”

trim(" Test Text ") = “Test Text” → only leading and trailing spaces will be removed

trim( Test Text ) = Syntactical error → missing “ “ in the formula

unescape_html(inputText)

Translate HTML-entities into special characters or umlauts.

settings and samples

Input: Text with HTML-entities

Output: Text with special characters or umlauts

Information:

Not all special characters can be translated.

Example:

unescape_html("verschl&uuml;sseln") → unescape_html("verschlüsseln")

unescape_html("&uuml;&ouml;&auml;&szlig;&amp;") → unescape_html("üöäß&")

unescape_html(verschl&uuml;sseln) → Syntactic error → missing “

upper(inputText)

Converts and returns a given text in upper case.

settings and samples

Input: Text

Output: Text

Information: -

Example:

upper(“this is a test text”) = “THIS IS A TEST TEXT”

upper(“1234567test”) = “1234567TEST” → only letters will be converted

upper(1234567test) = Evaluation error → inputText has to be a text

url_encode(inputText)

Converts an text to a URL-encoded text.

settings and samples

Input: Text

Output: Text in URL-coding

Information: -

Example:

url_encode("http://www.channelpilot.com ") = "http%3A%2F%2Fwww.channelpilot.com"

url_encode(http://www.channelpilot.com) = Syntactic error → missing “

validate_url(inputText)

Validates a URL if it is valid or not. There are no detailed information about the content.

settings and samples

Input: Text

Output: true / false

Information:

The URL which has to be checked has to be complete. A partial address will be shown as “false”. You have to add the whole address including the http(s)://www.

Example:

validate_url("https://www.google.de") = true

validate_url(“www.google.de”) = false

validate_url(https://www.google.de) = Syntactic error → missing “ “ for the URL

Date functions

compareDates(inputDate1, inputDate2, format)

Two dates can be compared with a custom date format. Both dates have to be valid.

settings and samples

Input: -

Output: 1, 0, -1

Information:

  • inputDate1 → first date to compare

  • inputDate2 → second date to compare

  • format → format which is used for both dates (has to be the same)

You will only get the information which one is older/newer or if the dates are the same.

Example:

compareDates("2021-10-22 13:30", "2022-10-22 13:30", "yyyy-MM-dd HH:mm") = -1 → inputDate2 is newer then inputDate1

compareDates("2022-10-22 13:30", "2021-10-22 13:30", "yyyy-MM-dd HH:mm") = 1 → inputDate1 is newer then inputDate2

compareDates("2021-10-22 13:30", "2021-10-22 13:30", "yyyy-MM-dd HH:mm") = 0 → both dates are the same

compareDates("2021-10-22", "2022-10-22", "yyyy-MM-dd HH:mm") = Not a valid date format in one of the provides dates

compareDates("2021-10-22 13:30", "2022-10-22 13:30", "yyyy-MM-dd") = -1 → a date can have more information then defined by the format but should not have less

date(format)

Gives the actual date in the defined format.

settings and samples

Input: Text as format from the list below

Output: Text as Date in the format given

Information:

Not every date format is supported. The variants below can be used:

  • "dd.MM.yyyy" (european date) -> "30.10.2013"

  • "dd.MM.yyyy HH:mm" (european date with time of the day) -> "30.10.2013 16:48"

  • "yyyy-MM-dd" (date) -> "2013-10-30"

  • "yyyy-MM-dd HH:mm" (date with time of the day) -> "2013-10-30 16:48"

  • "HH:mm" (time of the day) -> "16:48"

  • "EEEE" (day of the week as text) -> "Donnerstag"

  • "e" (day of the week as number) -> 4

  • "d" (day of the month) -> 30

  • "D" (day of the year) -> 303

  • "ww" (calender week as number) -> 43

  • "M" (month as number) -> 10

  • "MMMM" (month as text) -> "Oktober"

  • "yyyy" (year) -> 2013

  • "t" (timestamp) -> 1383087600

Example:

date("yyyy-MM-dd HH:mm") = 2023-01-17 10:59

date(“yyyy-MM-dd”) = 2023-01-17

date() = Syntactic error → the format is missing

date(yyyy-MM-dd HH:mm) = Syntactic error → the format is not written in “ “

date(inputDate, currentFormat, newFormat)

Transform a given date from one format to another.

settings and samples

Input: Text as Date

Output: Text as Date

Information:

  • inputDate → date to transform

  • currentFormat → original format in which the date is provided

  • newFormat → format the date has to be transformed

  • date(a) and date(a,b,c) can be combined, but it is not useful

date(date("yyyy-MM-dd HH:mm"),"yyyy-MM-dd HH:mm","dd.MM.yyyy HH:mm") -> 17.01.2023 11:23

Not every date format is supported. For variants see detailed information in date(format)!

Example:

date(“2023-01-17 10:59”,”yyyy-MM-dd HH:mm”,”yyyy-MM-dd”) = 2023-01-17

date(“17.01.2023 10:59”,”dd.MM.yyyy HH:mm”,”HH:mm”) = 10:59

date(“2023-01-17 10:59”,”yyyy-MM-dd HH:mm”,”EEEE”) = Tuesday

date(2023-01-17 10:59,”dd.MM.yyyy”,”HH:mm”) = Syntactic error → somewhere is a “ “ missing

Functions for special data fields

category(separator, inputCategory, n)

Combines several information to a category tree with a defined separator.

settings and samples

Input: Text, Numbers

Output: category tree combined from the provided data

Information:

  • separator → separator which is used between the single steps of the categoryhierarchy

  • inputCategory - n → provided information to create a categorytree, the hierarchy is created from left (hightest) to right (lowest)

There are only some characters which are allowed as separator (a).

This will be: > / - :

Example:

category(">", "Men", "Shoes", "Sportshoes") = "Men > Shoes > Sportshoes"

category("/","Top","1","2","6","Bottom") = "Top / 1 / 2 / 6 / Bottom"

category("/",Top,"1","2","6","Bottom") = Evaluation error → Top is not valid because of the missing “ “

category("|","Top","1","2","6","Bottom") = Evaluation error → separator is not allowed

decrypt(usedKey, usedMethod, inputValue)

Decrypt a value with the the used method and a defined key.

settings and samples

Input: 3 parameters

Output: Text

Information:

  • usedKey → key which was used to encrypt a text

  • usedMethod → method which defines how it is encrypted

  • inputValue → text as HEX which has to be decrypted

The encryption rate depends on the chosen method:

  • Blowfish - 32 bits

  • DES - 64 bits

  • DESede (3DES, TrippleDES) - 192 bits

  • AES - 256 bits

Example:

decrypt("Testkey", "Blowfish", "22F75ED0390C3B1B88C601A6D95E2E90929EB06713883F1A") = This is a Testtext

decrypt(Testkey, "Blowfish", "22F75ED0390C3B1B88C601A6D95E2E90929EB06713883F1A") = Evaluation error → if there is a “ missing

decrypt("Testkey", "Blowfish", "wrong HEX-Key") = will cause a general formula error which makes the formula unusable and causes errors in the logs

encrypt(usedKey, usedMethod, inputValue)

Encrypt a text with a method and a defined key.

settings and samples

Input: 3 parameters

Output: Text as HEX

Information:

  • usedKey → Key which is used to decrypt

  • usedMethod → Method which defines how to encrypt

  • inputValue → text which has to be encrypted

The encryption rate depends on the choosen method:

  • Blowfish - 32 bits

  • DES - 64 bits

  • DESede (3DES, TrippleDES) - 192 bits

  • AES - 256 bits

Example:

encrypt("Testkey", "Blowfish", "This is a Testtext") = 22F75ED0390C3B1B88C601A6D95E2E90929EB06713883F1A

encrypt("48404D63", "DES", "This is a Testtext") = 4D58E75075391F0AD18145BE136EB75088FC61AD664921DD

encrypt("48404D635166546A576E5A72", "DESede", "This is a Testtext") = C20DD2E2CA3D567315A2A3426F2C33E28081BF7861AF2D01

encrypt("48404D635166546A576E5A7234753778", "AES", "This is a Testtext") = 0EBAC45E229B9E31264326B093D42EC6E59D0FF540B8D33B9DA9709469D19C2B

encrypt(Testkey, "Blowfish", "This is a Testtext") = Evaluation error → if there is a “ “ missing

extract_value(inputJSON, JSONpointer)

Can be used to extract a specific value from a JSON-data field (simple structure).

settings and samples

Input: Text as JSON {““:””}

Output: Text of the selected value

Information:

  • inputJSON → data field or text which has a simple JSON-structure {“element”:”value”,”element2”:”value2”}

  • JSONpointer → points to the element of which the value is needed

The JSON-structure has to be in a data field to use. It can not be directly copied into the formula. This will cause an “Syntactic error”.

The JSON-structure has to be simple. For nested JSON and complex structures, please use the formula “extract_value_json(…)”.

Example:

datafield “JSON” with the value “{"ShippingStatus_int":"2","Color_text":"0010345"}”

extract_value(JSON,”Color_text”) = 0010345

datafield “JSON” with the value “{“element”:”value”,”element2”:”value2”}”

extract_value(JSON,”element2”) = value2

datafield “JSON” with the value “testtext”

extract_value(JSON,”element2”) = Evaluation error → the datafield is not a JSON structure

extract_value_json(inputJSON, JSONpointer)

Allows to extract information from nested deep complex JSON structure.

settings and samples

Input: Text as nested JSON-structure

Output: Text

Information:

  • inputJSON → a text in a JSON-structure, please make sure that the JSON is valid and nested correctly

  • JSONpointer → JSON-pointer which can be used to select any point in the JSON, the pointer has to be a / followed be the name of the node you want to select (samples see below)

The JSON-structure has to be in a data field to use it. It can not be directly copied into the formula. This will cause an “Syntactic error”.

This formula is designed for complex JSON-structures. For simple JSON please use the formula “extract_value(…)”.

Example:

sample JSON → {"node1":"value1","node2":{"subnode1":"subvalue1","subnode2":"subvalue2"},"node3":["subvalue3","subvalue4"]}

extract_json_value(json, “/node1”) = value1

extract_json_value(json, “/node2/subnode2“) = subvalue2

extract_json_value(json, “/node2”) = {"subnode1":"subvalue1","subnode2":"subvalue2"}

extract_json_value(json, “/node3”) = [“subvalue3”,”subvalue4”]

extract_json_value(json, "/node3/subvalue3") = EMPTY (would be valid but “subvalue3” has no data)

extract_json_value(json, “node3”) = Evaluation error → wrong notation of a JSON-node

extract_json_value('the json above without data field', “/node3”) = Syntactic error → invalid input because the JSON-structure is not used by a data field

sample JSON with wrong structure (wrong brackets) → ("node1":"value1","node2":{"subnode1":"subvalue1","subnode2":"subvalue2"},"node3":{"subvalue3","subvalue4"})

extract_json_value(json, “/node1”) = Evaluation error → JSON can not be parsed because the structure is invalid

get_deepest_cat_tree(inputCategories, delimiter, separator)

Shows the deepest category tree in a given list of categories with a customized separator.

settings and samples

Input: Text

Output: Text

Information:

  • inputCategories → list of categories, with a custom combination of nested structures

  • delimiter → delimiter which divides the different categories in the list (“cat,cat,cat” would be “,”)

  • separator → Separator which is used to create the nested structure of the categories

There are only some characters which are allowed as delimiter, which are: , ;

There are only some characters which are allowed as separator. This will be: > / - :

Example:

get_deepest_cat_tree("Test,Test > Tree,Test > Tree > Category",",",">") = Test > Tree > Category

get_deepest_cat_tree("Test,Test : Tree,Tree : Test",",",":") = Test : Tree (only the first deepest category will be displayed

get_deepest_cat_tree("Test|Test > Tree|Test > Tree > Category","|",">") = T → no valid delimiter, will lead to an error and a wrong output

get_deepest_cat_tree("Test,Test \ Tree,Test \ Tree \ Category",",",">") = Syntactic error, invalid separator

Related Articles
Did this answer your question?