Functions Library
Zoho ERP provides a built-in functions library that includes mathematical, text, and logical functions. You can use these functions in the Formula data type and in HTML PDF Templates for custom modules.
Note: The functions library includes preset functions that are built into Zoho ERP. These functions are different from custom functions written using Deluge for automation.
Each function in the library includes the following elements:
- Function Name: The predefined name of the function followed by parentheses. For example: ABS() or CEILING().
- Arguments: The values that you enter inside the parentheses. Arguments can be numbers, text, fields, or other functions.
- Operators: The symbols that help the function evaluate a calculation or condition. For example: IF(2==2, 1, 0).
Mathematical Functions
Mathematical functions help you perform numeric calculations.
| Function Name | Description | Syntax | Examples |
|---|---|---|---|
| Abs | Returns the absolute value of a number. | ABS(number) | ABS(-42) returns 42. |
| Addmonths | Adds or subtracts months from a date and returns the new date. | ADDMONTHS(date, number) | ADDMONTHS(“2022-10-17”, 3) returns 2023-01-17. |
| Avg | Returns the average value of the given numbers. | AVG(number1, number2, …, number30) | AVG(1, 2, 3) returns 2. |
| Ceiling | Rounds a number up to the nearest integer. | CEILING(number) | CEILING(3.4) returns 4. |
| Date | Combines year, month, and day values and returns a date. | DATE(year, month, day) | DATE(2022, 10, 17) returns 2022/10/17. |
| Floor | Rounds a number down to the nearest integer. | FLOOR(number) | FLOOR(3.8) returns 3. |
| Max | Returns the largest of the given numbers. | MAX(number1, number2, …, number30) | MAX(3, 1, 5, 2) returns 5. |
| Min | Returns the smallest of the given numbers. | MIN(number1, number2, …, number30) | MIN(5, -3, 0, 1) returns -3. |
| Percentage | Returns what percentage number2 is of number1. | PERCENTAGE(number1, number2) | PERCENTAGE(20, 5) returns 25. |
| Product | Multiplies the given numbers. | PRODUCT(number1, number2, …, number30) | PRODUCT(5, 6) returns 30. |
| Round | Rounds a number to the specified number of decimal places. | ROUND(number, decimal_place) | ROUND(123.344, 2) returns 123.34. |
| Series | Generates a sequence of numbers from start to end with an optional step. | SERIES(number, number, number) | SERIES(2, 10, 2) returns 2, 4, 6, 8, 10. |
| Sum | Returns the sum of the given numbers. | SUM(number1, number2, …, number30) | SUM(3, 8, 5) returns 16. |
| Sqrt | Returns the square root of the given number. | SQRT(number) | SQRT(9) returns 3. |
Note: You can use default Zoho ERP fields or custom fields as arguments. You can also nest functions inside other functions.
| Function Name | No. of Arguments Required | Argument Data Type | Output Data Type |
|---|---|---|---|
| Abs | 1 | Number | Number, String |
| Addmonths | 2 | String, Number | Date, String |
| Avg | Multiple | All Number | Number, String |
| Ceiling | 1 | Number | Number, String |
| Date | 3 | Number, Number, Number | Date, String |
| Floor | 1 | Number | Number, String |
| Max | Multiple | All Number | Number, String |
| Min | Multiple | All Number | Number, String |
| Percentage | 2 | Number, Number | Number, String |
| Product | Multiple | All Number | Number, String |
| Round | 2 | Number, Number | Number, String |
| Series | 3 | Number, Number, Number | Number, String |
| Sum | Multiple | All Number | Number, String |
| Sqrt | 1 | Number | Number, String |
Insight: When the output data type is String, it refers to the Text Box (Single Line) custom field.
Text Functions
Text functions help you manipulate and format text.
| Function Name | Description | Syntax | Examples |
|---|---|---|---|
| Char | Returns the character for the given ASCII code. | CHAR(number) | CHAR(97) returns a. |
| Clean | Removes non-printable characters from text. | CLEAN(string) | CLEAN(“H¶ello”) returns Hello. |
| Code | Returns the ASCII code of the first character in the string. | CODE(string) | CODE(“Apple”) returns 65. |
| Concatenate | Combines two strings into a single string. | CONCATENATE(string1, string2) | CONCATENATE(“First”, “Name”) returns FirstName. |
| Exact | Returns true if two strings are identical. | EXACT(string1, string2) | EXACT(“Zoho”, “Zoho”) returns true. |
| Fixed | Rounds a number to a specified number of decimals and formats the output. | FIXED(number, decimal_places(optional), no_commas(optional)) | FIXED(12345678.4367, 3, FALSE) returns 12,345,678.437. |
| Mid | Returns characters from the middle of a string. | MID(string, starting_position, number_of_characters) | MID(“welcome”, 4, 4) returns come. |
| Left | Returns the specified number of characters from the left side of a string. | LEFT(string, number) | LEFT(“Apple”, 3) returns App. |
| Len | Returns the number of characters in a string. | LEN(string) | LEN(“abc”) returns 3. |
| Lower | Converts text to lower case. | LOWER(string) | LOWER(APPLES) returns apples. |
| Proper | Converts the first letter of each word to upper case. | PROPER(string) | PROPER(zoho erp) returns Zoho Erp. |
| Right | Returns the specified number of characters from the right side of a string. | RIGHT(string, number_of_characters) | RIGHT(“Zoho ERP”, 3) returns ERP. |
| Rept | Repeats a value a specified number of times. | REPT(value, number) | REPT(“A”, 6) returns AAAAAA. |
| Replace | Replaces part of a string with different text. | REPLACE(old_text, starting_number, number_of_characters, new_text) | REPLACE(“South Africa”, 1, 5, “North”) returns North Africa. |
| Search | Returns the position of the first occurrence of one string inside another. | SEARCH(string1, string2, number(optional)) | SEARCH(“Elephant”, “e”, 2) returns 3. |
| Substitute | Replaces the specified text in a string. | SUBSTITUTE(string, old_text, new_text, instance_of_occurrence(optional)) | SUBSTITUTE(“Dog in the wall”, “Dog”, “Cat”) returns Cat in the wall. |
| Upper | Converts text to upper case. | UPPER(string) | UPPER(apples) returns APPLES. |
| T | Returns the value if it is text. | T(string) | T(“India”) returns India. |
| Trim | Removes leading and trailing spaces from text. | TRIM(string) | TRIM(" abcd “) returns abcd. |
Note: In the FIXED() function, decimal_places and no_commas are optional. You can also nest text functions.
| Function Name | No. of Arguments Required | Argument Data Type | Output Data Type |
|---|---|---|---|
| Char | 1 | Number | String |
| Clean | 1 | String | String |
| Code | 1 | String | Number |
| Concatenate | 2 | String, String | String |
| Exact | 2 | String, String | Boolean |
| Fixed | 3 | Number, Number, Boolean | Number |
| Mid | 3 | String, Number, Number | String |
| Left | 2 | String, Number | String |
| Len | 1 | String | Number |
| Lower | 1 | String | String |
| Proper | 1 | String | String |
| Right | 2 | String, Number | String |
| Rept | 2 | String, Number | String |
| Replace | 4 | String, Number, Number, String | String |
| Search | 3 | String, Value, Number | Number |
| Substitute | 4 | String, String, String, Number | String |
| Upper | 1 | String | String |
| T | 1 | String | String |
| Trim | 1 | String | String |
Logical Functions
Logical functions help you evaluate conditions and return true or false.
| Function Name | Description | Syntax | Examples |
|---|---|---|---|
| And | Returns true only if all conditions are true. | AND(condition1, condition2, …, condition30) | AND(2>1, 5>3, 7<8) returns true. |
| Count | Returns the count of numeric values among the given values. | COUNT(value1, value2, …, value30) | COUNT(10, 11.5, “string”, -55, “25”) returns 3. |
| If | Returns one value when the condition is true and another when it is false. | IF(condition, value1, value2) | IF(8>7, 1, 0) returns 1. |
| Isblank | Returns true if the value is empty. | ISBLANK(value) | ISBLANK() returns true. |
| Isnumber | Returns true if the value is a number. | ISNUMBER(value) | ISNUMBER(25) returns true. |
| Not | Returns the opposite of a condition. | NOT(condition) | NOT(1==1) returns false. |
| Or | Returns true if any one of the conditions is true. | OR(condition1, condition2, …, condition30) | OR(1<2, 3>5, 7>8) returns true. |
Note: You can use default Zoho ERP fields or custom fields as arguments in logical functions.
| Function Name | No. of Arguments Required | Argument Data Type | Return Type |
|---|---|---|---|
| And | Multiple | Boolean | Boolean |
| Count | Multiple | Generic | Boolean |
| If | 3 | Generic | Boolean |
| Isblank | 1 | Generic | Boolean |
| Isnumber | 1 | Generic | Boolean |
| Not | 1 | Boolean | Boolean |
| Or | Multiple | Boolean | Boolean |
Insight: Generic means any data type, such as numeric, string, or boolean.