Functions Library
Zoho Expense 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 inside your scripts.
Note: You can use functions when configuring criteria for workflow rules. Learn how to use functions in workflow rules.
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 | Description | Syntax | Example |
|---|---|---|---|
| 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 2, 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, …, number30) | 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, …, number30) | 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, …, number30) | 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 Expense fields or custom fields as arguments. For example, Addmonths(date, 3) adds three months to the value of a 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, for example, SQRT(ABS(-9)).
The table below lists the constraints for mathematical functions:
| Function | Number of Arguments | 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. It does not apply to email, URL, phone, auto-generate number, or dropdown fields.
Text Functions
Text functions let you manipulate or format text.
The table below lists the available text functions with their syntax and examples:
| Function | Description | Syntax | Example |
|---|---|---|---|
| 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, string2) | 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, 4) 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 expense) returns Zoho Expense, PROPER(GadGet) returns Gadget. |
| Right | Returns the specified number of characters from the end of a string. | RIGHT(string, number_of_characters) | RIGHT(“Zoho Expense”, 7) returns Expense. |
| 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 Expense fields or custom fields as arguments. For example, upper(description) converts the value of the Description field 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 | Number of Arguments | 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 | Description | Syntax | Example |
|---|---|---|---|
| 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 Expense fields or custom fields as arguments. For example, isblank(description) returns true if the Description 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 | Number of Arguments | Argument Data Type | Output Data 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. The return type matches the input data type.