Functions Library
Zoho Books provides a built-in functions library, a collection of preset mathematical, text, and logical functions that you can use to perform calculations, manipulate text, and evaluate conditions. These functions are available in both the formula data type for custom fields and HTML PDF templates.
Note: The functions library contains preset functions built into Zoho Books. These are different from Functions, which are custom scripts that you write using Deluge to automate workflows.
Each function in the library performs a specific action based on the values you provide as input and generates a new value as output. A function consists of:
- Function Name: The predefined name of the function, followed by parentheses. For example:
ABS(),CEILING(). - Arguments: The values provided inside the parentheses. Arguments can be numbers, text, or other functions. Multiple arguments are separated by commas. For example:
MAX(20, 5, 45). - Operators: Mathematical symbols used within arguments to perform calculations. For example:
IF(2==2).
Mathematical Functions
Mathematical functions perform numeric calculations.
The table below lists the available mathematical functions with their syntax and examples:
| Function Name | Description | Syntax | Examples |
|---|---|---|---|
| Abs | Returns the absolute (non-negative) value of a number. | ABS(number) | ABS(-42) returns 42, ABS(+33) returns 33. |
| 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, ADDMONTHS(“2022-10-17”, -5) returns 2022-05-17. |
| Avg | Returns the average value of the given numbers. | AVG(number1, number2, …, number30) | AVG(1,2,3) returns 6, AVG(5,6,7,-9) returns 2.25. |
| Ceiling | Rounds up to the nearest integer. | CEILING(number) | CEILING(3.4) returns 4, CEILING(-3.4) returns -3. |
| Date | Converts the given year, month, and day values into a date in yyyy/mm/dd format. | DATE(year, month, day) | DATE(2022,10,17) returns 2022/10/17. |
| Floor | Rounds down to the nearest integer. | FLOOR(number) | FLOOR(3.8) returns 3, FLOOR(-3.4) returns -4. |
| Max | Returns the largest of the given numbers. | MAX(number1, number2, …, number 30) | MAX(3,1,5,2) returns 5, MAX(0,-4,-3,-2) returns 0. |
| Min | Returns the smallest of the given numbers. | MIN(number1, number2, …, number 30) | MIN(5,-3,0,1) returns -3, MIN(0,4,1) returns 0. |
| 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, PRODUCT(5,-6) returns -30, 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, ROUND(123.345, 2) returns 123.35. |
| Series | Generates a sequence of numbers from start to end with an optional step. | SERIES(number, number, number) | SERIES(5), SERIES(3,7), SERIES(2,10,2). |
| Sum | Returns the sum of all the given numbers. | SUM(number1, number2, …, number 30) | SUM(3,8,5) returns 16, SUM(5,6,-3,0) returns 8. |
| Sqrt | Returns the square root of the given number. | SQRT(number) | SQRT(9) returns 3, SQRT(ABS(-9)) returns 3. |
Note:
- You can use default Zoho Books fields or custom fields as arguments. For example, Addmonths(date, 3) adds three months to the Sales Order Date field and returns the new date.
- You can nest functions inside other functions. For example, Sum(Max(10,15), Min(2,5)) returns 17.
- The Sqrt() function does not support negative numbers. Use Abs() inside Sqrt() to handle negative values, e.g., SQRT(ABS(-9)).
The table below lists the constraints for mathematical 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 |
| 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) field type only, not email, URL, phone, auto-generate number, or dropdown.
Text Functions
Text functions let you manipulate or format text.
The table below lists the available text functions with their syntax and examples:
| Function Name | Description | Syntax | Examples |
|---|---|---|---|
| Char | Returns the character for the given ASCII code. | CHAR(number) | CHAR(97) returns a, CHAR(65) returns A. |
| Clean | Removes non-printable characters from the 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, CODE(“apple”) returns 97, CODE(“B”) returns 98, CODE(“n”) returns 110. |
| Concatenate | Combines two strings into a single string. | CONCATENATE(string1, string2) | CONCATENATE(“FirstName”, “LastName”) returns FirstNameLastName. |
| Exact | Returns true if the two strings are identical. | EXACT(string1, string 2) | EXACT(“Zoho”, “Zoho”) returns true, EXACT(“Zoho”, “zoho”) returns false. |
| Fixed | Rounds a number to the specified decimals and optionally adds commas. | FIXED(number, decimal_places(optional), no_commas(optional)) | FIXED(12345678.4367,3,FALSE) returns 12,345,678.437, FIXED(12345678.437,3,TRUE) returns 12345678.437, FIXED(12345678.4367) returns 12345678.44. |
| Mid | Extracts characters from the middle of a string, starting at a given position. | MID(string, starting_position, number_of_characters) | MID(“welcome”, 4, 7) returns come. |
| Left | Returns the specified number of characters from the start of a string. | LEFT(string, number) | LEFT(“Apple”, 3) returns App, LEFT(“Chess”, 2) returns Ch. |
| Len | Returns the number of characters in a string, including spaces. | LEN(string) | LEN(“abc”) returns 3, LEN(" abc “) returns 5. |
| Lower | Converts a string to lowercase. | LOWER(string) | LOWER(APPLES) returns apples, LOWER(Apples) returns apples. |
| Proper | Capitalizes the first letter of each word and lowercases the rest. | PROPER(string) | PROPER(zoho books) returns Zoho Books, PROPER(Zoho books) returns Zoho Books, PROPER(GadGet) returns Gadget. |
| Right | Returns the specified number of characters from the end of a string. | RIGHT(string, number_of_characters) | RIGHT(“Zoho Books”, 5) returns Books. |
| Rept | Repeats a value a specified number of times. | REPT(value, number) | REPT(“A”, 6) returns AAAAAA, REPT(“The”, 2) returns TheThe, REPT(“1”, 3) returns 111. |
| Replace | Replaces a part of the string with a different text. | REPLACE(old_text, starting_number, number_of_characters, new_text) | REPLACE(“South Africa”, 1, 5, “North”) returns North Africa, REPLACE(“abcdef”, 4, 3, “xyz”) returns abcxyz. |
| Search | Returns the position where string2 first appears in string1. | SEARCH(string1, string2, number(optional)) | SEARCH(“Elephant”, “e”, 2) returns 3, SEARCH(“Elephant”, “E”) returns 1, SEARCH(“Antarctica”, “a”, 3) returns 4. |
| Substitute | Replaces occurrences of specified text in a string. | SUBSTITUTE(string, old text, new text, instance_of_occurrence(optional)) | SUBSTITUTE(“Dog in the wall, and Dog in the street”, “Dog”, “Cat”) returns Cat in the wall, and Cat in the street, SUBSTITUTE(“Dog in the wall, and Dog in the street”, “Dog”, “Cat”, 2) returns Dog in the wall, and Cat in the street. |
| Upper | Converts a string to uppercase. | UPPER(string) | UPPER(apples) returns APPLES, UPPER(APPles) returns APPLES. |
| T | Returns the value if it is text; returns blank otherwise. | T(string) | T(“India”) returns India, T(1) returns blank, T(True) returns blank. |
| Trim | Removes leading and trailing spaces from a string. | TRIM(string) | TRIM(” abcd “) returns abcd. |
Note:
- You can use default Zoho Books fields or custom fields as arguments. For example, upper(notes) converts the Notes field value to uppercase.
- You can nest functions inside other functions. For example, len(mid(“Finance”,4,3)) returns 3.
- In the fixed() function, decimal_places and no_commas are optional. Without these arguments, the function rounds to two decimal places and omits commas. If no_commas is true, commas are omitted.
The table below lists the constraints for 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 | String |
| 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 |
Insight: The T() function returns blank if the input is not a string.
Logical Functions
Logical functions evaluate conditions and return true or false.
The table below lists the available logical functions with their syntax and examples:
| 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, AND(2>1, 5>3, 7>8) returns false. |
| 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 value1 if the condition is true; otherwise returns value2. | IF(condition, value1, value2) | IF(8>7,1,0) returns 1, IF(8<7, “Valid”, “Invalid”) returns Invalid. |
| Isblank | Returns true if the value is empty; false otherwise. | ISBLANK(value) | ISBLANK() returns true, ISBLANK(197) returns false. |
| Isnumber | Returns true if the value is a number; false otherwise. | ISNUMBER(value) | ISNUMBER(25) returns true, ISNUMBER(apple) returns false. |
| Not | Returns the opposite of the condition: false if true, true if false. | NOT(condition) | NOT(1==1) returns false, NOT(1==2) returns true. |
| Or | Returns true if any one of the conditions is true. | OR(condition1, condition2, …, condition30) | OR(1<2,3>5,7>8) returns true, OR(1>2,3>5,7>8) returns false. |
Note:
- You can use default Zoho Books fields or custom fields as arguments. For example, isblank(notes) returns true if the Notes field is empty, and false if it has a value.
- You can nest functions inside other functions. For example, not(and(8<9,1>0)==or(10>9,100>99)) returns false.
The table below lists the constraints for 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: numeric, string, or boolean. The return type matches the input data type.
Yes
No
Thank you for your feedback!