Formula Data Type
Formula data type in a custom field enables you to build a formula that performs calculations using predefined functions, fields, and operators. This formula will then be executed and the output will be displayed in the custom field.
Scenario: Zylker Courier is a courier and package distribution company operating in India. They calculate an item’s weight in kilograms. They have customers in Canada where weight is calculated in pounds. Zylker Courier uses the formula data type in custom fields to convert the weight from kilogram to pound.
The formula data type consists of three elements:
- Function: A function performs a specific action based on the values that you provide as input and generates a new value as output. To create a function, enter the function name followed by a set of parenthesis. For example: Abs(), Ceiling(), etc.
- Argument: An argument is the value given within the parenthesis of a function. It can be a number, text, or other function. If there is more than one argument, you can separate them using commas. For example: Max(20, 5, 45), etc.
- Operator: An operator is the mathematical symbol using which arguments in the function perform calculations. For example: If(2==2), etc.
We will look at each of these elements in detail below.
Functions in Formula Data Type
A function is a predefined formula that you can use to perform mathematical, text, or logical operations. To create a function, you should enter the function name followed by the required arguments inside a set of parenthesis. The functions supported by the formula data type are:
Mathematical Functions
Mathematical functions are used to perform mathematical calculations.
The following table contains the list of mathematical functions available in formula data type and examples of how they can be used.
| Syntax | Examples | No. of Arguments Required | Argument Data Type | Output Data Type |
|---|---|---|---|---|
| Abs(number) | ABS(-42) returns 42 | 1 | Number | Number |
| ADDMONTHS(date, number) | ADDMONTHS(“2025-10-17”, 3) returns 2026-01-17, ADDMONTHS(“2025-10-17”, -3) returns 2025-07-17 | 2 | String, Number | Date |
| AVG(number1, number2, …, number30) | AVG(1, 2, 3) returns 6. | Multiple | All Number | Number |
| CEILING(number) | CEILING(3.4) returns 4 | 1 | Number | Number |
| DATE(year, month, day) | DATE(2025, 10, 17) returns 2025/10/17. | 3 | Number, Number, Number | Date |
| FLOOR(number) | FLOOR(3.8) returns 3 | 1 | Number | Number |
| MAX(number1, number2, …, number 30) | MAX(3, 1, 5, 2) returns 5 | Multiple | All Number | Number |
| MIN(number1, number2, …, number 30) | MIN(5, -3, 0, 1) returns -3 | Multiple | All Number | Number |
| PERCENTAGE(number1, number2) | PERCENTAGE(20,5) returns 25 | 2 | Number, Number | Number |
| PRODUCT(number1, number2, …, number30) | PRODUCT(5, 6) returns 30 | Multiple | All Number | Number |
| ROUND(number, decimal place) | ROUND(123.344, 2) returns 123.34 | 2 | Number, Number | Number |
| SUM(number1, number2, …, number 30) | SUM(3, 8, 5) returns 16 | Multiple | All Number | Number, String |
| SQRT(number) | SQRT(9) returns 3 | 1 | Number | Number,String |
Insight: Here, the output data type string refers to Text Box (Single Line).
Text Functions
Text functions are used to manipulate or edit text in any form.
The following table contains the list of text functions available in formula data type and examples of how they can be used.
| Function Name | Description | Syntax | Examples | No. of Arguments Required | Argument Data Type | Output Data Type |
|---|---|---|---|---|---|---|
| Char | Returns the character equivalent of the given number. | CHAR(number) | CHAR(97) returns a. | 1 | Number | String |
| Clean | Removes the non-printable characters from the text and returns the printable characters. | CLEAN(string) | CLEAN(“H¶ello”) returns Hello. | 1 | String | String |
| Code | Returns the numeric value of the first character of the string. | CODE(string) | CODE(“Apple”) returns 65. | 1 | String | Number |
| Concatenate | Combines two strings into a single string. | CONCATENATE(string1, string2) | CONCATENATE(“FirstName”, “LastName”) returns FirstNameLastName. | 2 | String, String | String |
| Exact | Returns true if the two strings are identical. | EXACT(string1, string 2) | EXACT(“Zoho”, “Zoho”) returns true. | 2 | String, String | Boolean |
| Fixed | Rounds the number to the specified number of decimal places, formats the number by adding commas, and returns the result. | FIXED(number, decimal_places(optional), no_commas(optional)) | FIXED(12345678.4367, 3, FALSE) returns 12,345,678.437. | 3 | Number, Number, Boolean | Number |
| Mid | Returns the specific number of characters from the string, starting from the position that you specify up to the number of characters that you specify. | MID(string, starting_position, number_of_characters) | MID(“welcome”, 4, 7) returns come. | 3 | String, Number, Number | String |
| Left | Returns the number of characters that you specify from the left side of the string. | LEFT(string) LEFT(“Apple”, 3) returns App | 2 | String, Number | String | |
| Lower | Converts the given string to lower case. | LOWER(string) | LOWER(APPLES) returns apples. | 1 | String | String |
| Proper | Converts the first letter of each word in the string to upper case. Converts any other character to lower case. | PROPER(string) | PROPER(zoho spend) returns Zoho Spend. | 1 | String | String |
| Right | Returns the number of characters specified from the right side of the string. | RIGHT(string, number_of_characters) | RIGHT(“Zoho Spend, 5) returns Spend. | 2 | String, Number | String |
| Rept | Repeats the given value a specified number of times. | REPT(value, number) | REPT(“A”, 6) returns AAAAAA. | 2 | String, Number | String |
| 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. | 4 | String, Number, Number, String | String |
| Search | Returns the position of the first occurrence of string2 inside string1. | SEARCH(string1, string2, number(optional)) | SEARCH(“Elephant”, “e”, 2) returns 3. | 3 | String, Value, Number | Number |
| Substitute | Replaces the specified text in the string with the text you specify. | 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. | 4 | String, String, String, Number | String |
| Upper | Converts the given string to upper case. | UPPER(string) | Upper(apples) returns APPLES. | 1 | String | String |
| T | Returns the text if the value provided is a text. Returns a blank space if the value provided in the string is not a text. | T(string) | T(“India”) returns India. | 1 | String | String |
| Trim | Removes the leading and trailing spaces from the string. | TRIM(string) | TRIM(” abcd “) returns abcd. | 1 | String | String |
Insight: Here, the output data type string refers to Text Box (Single Line).
Logical Functions
Logical functions return one of the two values, true or false, as output based on the values given as input.
The following table contains the list of logical functions available in formula data type and examples of how they can be used.
| Function Name | Description | Syntax | Examples | No. of Arguments Required | Argument Data Type | Output Data Type |
|---|---|---|---|---|---|---|
| And | Returns true if all the conditions are true, else it returns false. | AND(condition1, condition2, …, condition30) | AND(2>1, 5>3, 7<8) returns true. | Multiple | Boolean | Boolean |
| Count | Returns the number of numerical values present in the values provided. | COUNT(value1, value2, …, value30) | COUNT(10, 11.5, “string”, -55, “25”) returns 3. | Multiple | Generic | Boolean |
| If | Returns value1 if the condition is true, else it returns value2. | IF(condition, value1, value2) | IF(8>7, 1, 0) returns 1. | 3 | Generic, Boolean, Boolean | Boolean |
| Isblank | Returns true if no input is provided. | Returns false if input is provided. | ISBLANK(value) | ISBLANK() returns true. | 1 | Generic |
| Isnumber | Returns true if the value entered is a number. | Returns false if the value entered is not a number. | ISNUMBER(value) | ISNUMBER(25) returns true. | 1 | Generic |
| Not | Returns the logical negation. Returns false if the condition is true. Returns true if the condition is false. | NOT(condition) | NOT(1==1) returns false. | 1 | Boolean | Boolean |
| OR | Returns true if anyone of the conditions is true. | Returns false if none of the conditions are true. | OR(condition1, condition2, …, condition30) | OR(1<2, 3>5, 7>8) returns true. | Multiple | Boolean |
Insight: Here generic implies any data type such as numeric, string, or Boolean.
Create a Custom Field Using the Formula Data Type
Note: Custom fields created using the formula data type will be displayed only on the module’s details page and not on the module’s creation or edit page.
Here’s how you can create a custom field using the formula data type:
- Go to Settings on the left sidebar.
(OR)
Click Settings in the top right corner of the page. - Select the module for which you want to create a new custom field.
- Go to the Field Customization or Fields tab of the respective module.
- Click + New Field in the top right corner of the page.
- Enter the Label Name and select Formula from the dropdown next to Data Type.
- Select the data type in which you want to store and display the output of the formula from the dropdown next to Output Data Type.
- Construct the formula by clicking the + button next to the respective Functions, Fields, and Operators or by double clicking them. You can also enter the formula in the Formula field.
- After constructing the formula, click Check Syntax next to the Formula field to check if the formula that you have entered is valid.
- Click Save.