Calculated Fields - Supported Functions

The below table explains in detail the functions that can be used to create calculated fields. The functions are divided into 4 categories

1 Numeric Functions
2 String Functions
3 Date Functions
4 Date Functions
5 Logical Functions

Numeric Functions

For parameters required by the functions,

Is Mandatory?: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn’t provided for the parameter, the add-on will throw an error

Is Variable Length?: The value ‘No’ indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

Function Name & Description	Return Type	Examples	Parameters
Function Name & Description	Return Type	Examples	Name: Description	Type	Is Mandatory?	Is Variable Length?
ADD - Returns the sum of all the numbers given as parameters	Numeric	ADD(8, 101.4) = 109.4 ADD(8, 101.4, 10, 20) = 139.4 ADD(NULL, 10, 20, NULL) = 30	value1: Initial Value	Numeric	Yes	No
	Numeric		value2: Value to be added	Numeric	Yes	Yes
SUBTRACT - Returns the difference between provided numbers	Numeric	SUBTRACT(101.4, 1.4) = 100 SUBTRACT(101.4, -8) = 109.4 SUBTRACT(NULL, 10) = -10	value1: Initial value	Numeric	Yes	No
	Numeric		value2: Value to be subtracted	Numeric	Yes	No
MULTIPLY - Returns the product of all the numbers given as parameters	Numeric	MULTIPLY(10, 1.1) = 11 MULTIPLY(NULL, 10) = NULL MULTIPLY(10, 10, 10, 1.1234) = 1123.4	value: Initial value	Numeric	Yes	No
	Numeric		multiplier: Value to be multiplied	Numeric	Yes	Yes
DIVIDE - Returns the quotient after a number is divided by a divisor	Numeric	DIVIDE(15, 3) = 5, DIVIDE(15, 4) = 3.75 DIVIDE(100, 1000) = 0.1 DIVIDE(0, 4) = 0 DIVIDE(15, 0) = NULL DIVIDE(NULL, 4) = NULL DIVIDE(15, NULL) = NULL	value: Initial value	Numeric	Yes	No
	Numeric		divisor: Value to divide by	Numeric	Yes	No
MOD - Returns the remainder after a number is divided by a divisor	Numeric	MOD(15, 3) = 0 MOD(15, 4) = 3 MOD(100, 1000) = 100 MOD(10, 0.5) = 0 MOD(0, 4) = 0 MOD(15, 0) = NULL MOD(NULL, 4) = NULL MOD(15, NULL) = NULL	value: Initial value	Numeric	Yes	No
	Numeric		divisor: Value to divide the initial value by	Numeric	Yes	No
ROUND - Returns the value of number rounded to the nearest Integer	Numeric	ROUND(100) = 100 ROUND(100.495) = 100 ROUND(100.5) = 101 ROUND(100.998) = 101 ROUND(-100.495) = -100 ROUND(-100.5) = -100 ROUND(-100.998) = -101 ROUND(NULL) = NULL	value: Initial value	Numeric	Yes	No
FLOOR - Returns the largest Integer value that is less than or equal to the parameter	Numeric	FLOOR(10) = 10 FLOOR(10.8) = 10 FLOOR(-10.8) = -11 FLOOR(NULL) = NULL	value: Initial value	Numeric	Yes	No
CEILING - Returns the smallest Integer value that is greater than or equal to the parameter	Numeric	CEILING(10) = 10 CEILING(10.8) = 11 CEILING(-10.8) = -10 CEILING(NULL) = NULL	value: Initial value	Numeric	Yes	No
ABS - Returns the absolute value of the parameter	Numeric	ABS(10.1) = 10.1 ABS(-10.1) = 10.1 ABS(0) = 0 ABS(NULL) = NULL	value: Initial value	Numeric	Yes	No
TO NUMBER - Returns Number value represented by the input string	Numeric	TO NUMBER('-10.1') = -10.1 TO NUMBER('10.100') = 10.1 TO NUMBER('10.1.1') = NULL TO NUMBER('1,100.1') = NULL TO NUMBER('10.1a') = NULL TO NUMBER('invalid') = NULL TO NUMBER(NULL) = NULL	value: Initial value	Numeric	Yes	No
LESS THAN - Returns TRUE if value1 is less than value2. Otherwise returns FALSE	Boolean	LESS THAN(10.0, 10) = FALSE LESS THAN(10.0, 9.9) = FALSE LESS THAN(10.0, 10.01) = TRUE LESS THAN(-10.0, -9.9) = TRUE LESS THAN(-10.0, -10.01) = FALSE LESS THAN(NULL, 10) = NULL	value1: Initial value	Numeric	Yes	No
	Boolean		value2: Value to be compared against	Numeric	Yes	No
LESS THAN OR EQUALS - Returns TRUE if value1 is less than or equal to value2. Otherwise returns FALSE	Boolean	LESS THAN OR EQUALS(10.0, 10) = TRUE LESS THAN OR EQUALS(10.0, 9.9) = FALSE LESS THAN OR EQUALS(10.0, 10.01) = TRUE LESS THAN OR EQUALS(NULL, 10) = NULL	value1: Initial value	Numeric	Yes	No
	Boolean		value2: Value to be compared against	Numeric	Yes	No
GREATER THAN - Returns TRUE if value1 is greater than value2. Otherwise returns FALSE	Boolean	GREATER THAN(10.0, 10) = FALSE GREATER THAN(10.0, 9.9) = TRUE GREATER THAN(10.0, 10.01) = FALSE GREATER THAN(-10.0, -9.9) = FALSE GREATER THAN(-10.0, -10.01) = TRUE GREATER THAN(NULL, 10) = NULL	value1: Initial value	Numeric	Yes	No
	Boolean		value2: Value to be compared against	Numeric	Yes	No
GREATER THAN OR EQUALS - Returns TRUE if value1 is greater than or equal to value2. Otherwise returns FALSE	Boolean	GREATER THAN OR EQUALS(10.0, 10) = TRUE GREATER THAN OR EQUALS(10.0, 9.9) = TRUE GREATER THAN OR EQUALS(10.0, 10.01) = FALSE GREATER THAN OR EQUALS(NULL, 10) = NULL	value1: Initial value	Numeric	Yes	No
	Boolean		value2: Value to be compared against	Numeric	Yes	No

String Functions

For parameters required by the functions,

Is Mandatory?: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn’t provided for the parameter, the add-on will throw an error

Is Variable Length?: The value ‘No’ indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

Function Name & Description	Return Type	Examples	Parameters
Function Name & Description	Return Type	Examples	Name: Description	Type	Is Mandatory?	Is Variable Length?
CONCAT - Concatenates the provided string values into a single string	String	CONCAT('Join', ' ', 'these', ' ', 'words') = 'Join these words' CONCAT('Join', NULL, 'these', NULL, 'words') = 'Jointhesewords' CONCAT(NULL, NULL) = ''	value1: Initial value	String	Yes	No
	String		value2: Value to be concatenated to the initial value	String	Yes	Yes
SUBSTRING - Returns the substring of the value. Substring starting at startPos, till endPos (if provided, otherwise, till end of the value) is returned. Note: The position index starts with zero. Value of 0, will point to the first character on the left. If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right.	String	SUBSTRING('The fox jumped over the fence', 0) = 'The fox jumped over the fence' SUBSTRING('The fox jumped over the fence', 0, 10) = 'The fox ju' SUBSTRING('The fox jumped over the fence', 10, 20) = 'mped over ' SUBSTRING('The fox jumped over the fence', 10, 100) = 'mped over the fence' SUBSTRING('The fox jumped over the fence', -10) = ' the fence' SUBSTRING('The fox jumped over the fence', -10, 25) = ' the f' SUBSTRING('The fox jumped over the fence', 0, -10) = 'The fox jumped over' SUBSTRING('The fox jumped over the fence', 30, 40) = '' SUBSTRING(NULL, 30, 40) = NULL	value: Initial value	String	Yes	No
			startPos: Character position which will be start of extracted substring	Numeric	Yes	No
			endPos: Character position till which the substring will be extracted. If not provided, then substring till the end of the initial value will be extracted	Numeric	No	No
UPPER - Returns the value with all alphabets converted to Upper Case	String	UPPER('this is all lower') = 'THIS IS ALL LOWER' UPPER('tHis IS MixEd Case') = 'THIS IS MIXED CASE' UPPER('') = '' UPPER(NULL) = NULL	value: Initial value	String	Yes	No
LOWER - Returns the value with all alphabets converted to Lower Case	String	LOWER('THIS IS ALL UPPER') = 'this is all upper' LOWER('tHis IS MixEd Case') = 'this is mixed case' LOWER('') = '' LOWER(NULL) = NULL	value: Initial value	String	Yes	No
TO STRING - Returns the String value of passed parameter. Useful for converting Numeric, Date or Boolean value to String. Note: Parameter of type ANY indicates, that function can take any argument type (String, Numeric, Date OR Boolean).	String	TO STRING(100.01) = '100.01' TO STRING(TRUE) = 'true' TO STRING(FALSE) = 'false' TO STRING(NULL) = NULL TO STRING('This is a string value') = 'This is a string value'	value: Initial value	ANY	Yes	No
LENGTH - Returns the length of provided string value	Numeric	LENGTH('This is a string value') = 22 LENGTH('') = 0 LENGTH(NULL) = 0	value: Initial value	String	Yes	No
POSITION - Returns the position of first occurrence of searchString in the value. Return value of -1, indicates that searchString was not found in the initial value. If the startPos is provided, then search will be started from startPos, otherwise search will be done from start of the value. Note: The position index starts with zero. Value of 0, will point to the first character on the left. If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right.	Numeric	POSITION('This is a string value', 'This') = 0 POSITION('This is a string value', 'This', 1) = -1 POSITION('This is a string value', 'value', -10) = 17 POSITION('This is a string value', 'is') = 2 POSITION('This is a string value', 'is', NULL) = 2 POSITION('This is a string value', 'numeric') = -1 POSITION('This is a string value', NULL) = -1 POSITION(NULL, 'This is a string value') = -1	value: Initial value	String	Yes	No
			searchString: String to be searched in the initial value	String	Yes	No
			startPos: Character position from which search will be performed. If not provided, the search will be performed from the starting of the initial value.	Numeric	No	No
CONTAINS - Returns TRUE if the searchString is contained in the value. Otherwise returns FALSE	Boolean	CONTAINS('This is a string value', 'is') = TRUE CONTAINS('This is a string value', 'numeric') = FALSE CONTAINS('This is a string value', NULL) = FALSE CONTAINS(NULL, 'This is a string value') = FALSE	value: Initial value	String	Yes	No
	Boolean		searchString: String to be searched in the initial value	String	Yes	No
IN - Returns TRUE if the value is contained in the List. Otherwise returns FALSE	Boolean	IN('OPEN', 'TO DO', 'OPEN', 'IN PROGRESS') = TRUE IN('DONE', 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE IN(NULL, 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE IN('DONE', '', NULL) = FALSE