Creating Formula Fields
Formula Field is currently available on Early Access. Kindly fill out this Registration Form to request early access.Managers and support agents often require quick access to key metrics, such as the time remaining before a ticket becomes overdue or the calculated costs associated with services. Manually extracting and calculating these metrics can be time-consuming and prone to errors. Formula fields solve this by automating calculations and data analysis directly within the ticketing system.
Formula fields perform calculations based on data from existing fields, eliminating the need for manual input. They can compute various types of values, including numeric, string, and date values, or more complex formulas involving multiple fields. Once set up, the formula field updates automatically with any changes in the data, offering real-time insights to assist in decision-making.
For example, imagine a customer support team in an e-commerce company that deals with a high volume of return and refund requests. The team needs to quickly determine how long each return ticket has been open to prioritize processing times, ensuring customers receive their refunds promptly. Additionally, they need to calculate the total cost associated with each return, including shipping fees and restocking charges, to assess the impact on revenue.
Using Formula Fields, the team can automate these calculations:
- Ticket age calculation: A formula field calculates the number of days since the ticket was created, indicating how long a return request has been pending. This helps prioritize tickets that have been open the longest.
- Total cost calculation: Another formula field computes the total cost of processing each return. This might include the original item's price, the shipping fees for the return, and any applicable restocking fees.
Whenever a new return ticket is created or updated, these formula fields automatically update, providing the support team with real-time information to make quick and informed decisions. This not only improves efficiency but also enhances customer satisfaction by ensuring that refunds are processed swiftly and accurately.
Permission Required Users with the Manage Layouts and Fields permission profile can create formula field.
Check Feature Availability and Limits
Operators used in the formula
Operators are symbols used in formulas to perform specific mathematical, logical, or comparison operations. They are used in computing data and returning results based on the given inputs. Operators are categorized into arithmetic, boolean, and formula builders.
Category | Operator | Output |
Arithmetic | + | Total time spent (3 hours) + Time on hold (2 hours) = 5 hours Total time including hold time. |
- | Due date (48 hours) - Elapsed time (30 hours) = 18 hours - Remaining time before the ticket is overdue. | |
* | Hourly rate (50 USD) * Total time spent (3 hours) = 150 USD - Total cost based on hourly rate. | |
/ | Total cost (150 USD) / Number of agents (3) = 50 USD - Cost per agent. | |
^ | Raises one value to the power of another For example, Escalation rate (2) ^ Growth factor (3 hours) = 8 escalations after 3 hours of exponential growth. | |
% | Total tickets (20) % Unresolved tickets (3) = 2 - Remainder when dividing the tickets. | |
Boolean | & | Returns TRUE if both the values are true; otherwise, returns FALSE. For example, SLA breached (TRUE) & Priority (High) = TRUE - Both SLA is breached and priority is high. |
| | Used to combine or evaluate multiple conditions. For example, SLA breached (TRUE) | Priority (Low) = TRUE - Since SLA Breached is TRUE, the result is TRUE, regardless of the value of Priority. | |
! | Reverses the boolean value of an operand. For example, !SLA breached (TRUE) = FALSE | |
== | Compares two values for equality. For example, Priority (High) == urgency (High) = TRUE - Both values are equal, so the result is TRUE. | |
!= | Compares two values for inequality. For example, Priority (High) != urgency (Low) = TRUE - Values are not equal, so the result is TRUE. | |
> | Time spent (4 hours) > Estimated time (3 hours) = TRUE - Since Time spent is greater than estimated time. | |
>= | Time remaining (5 hours) >= Time left (5 hours) = TRUE. | |
< | Tickets resolved (8) < Total tickets (10) = TRUE. | |
<= | Overdue time (2 hours) <= Maximum allowable overdue Time (3 hours) = TRUE. | |
Formula Builders | ( | (Total Time (3 hours) + Time On Hold (2 hours)) = 5 hours - Begins a sub-expression; groups operations so that the sum of Total Time and Time On Hold is calculated first. |
) | (Total Time (3 hours) + Time On Hold (2 hours)) = 5 hours - Ends a sub-expression; closes the grouping of operations and finalizes the result. "High Priority" = High Priority - Denotes a string literal; the value within quotes is treated as a string. | |
" | High Priority - Denotes a string literal; the value within quotes is treated as a string. |
Functions used in the formula
A function is a predefined operation used to perform specific calculations, compute values, and derive new data from existing fields. Functions are essential for simplifying complex operations in formulas. There are four different data types for functions, and they are,
1. Number and decimal functions
These functions allow users to perform any arithmetic and number computations within formulas. They can perform tasks such as rounding off, finding the absolute value, and logarithmic operations to calculate response time differences, SLA breaches, rounding customer ratings for reporting and more.
Function | Description | Usage | Examples |
Abs | Returns the absolute value of the Number. | Abs(number) | Abs(TicketResponseTime - SLATime) |
Ceil | Returns the smallest integer greater than or equal to the input. | Ceil(number) | Ceil(AverageTicketRating) |
Floor | Returns the largest integer less than or equal to the input. | Floor(number) | Floor(AverageTicketRating) |
Naturallog | Returns the natural logarithm of a number. | Naturallog(number) | Naturallog(TimeToResolve) |
Base10log | Returns the base 10 logarithm of the input number. | Base10log(number) | Base10log(TicketVolume) |
Max | Returns the maximum value from the specified list of numbers. | Max(value1, value2,...) | Max(ResponseTime1, ResponseTime2, ResponseTime3) |
Min | Returns the minimum value from the specified list of numbers. | Min(value1, value2,...) | Min(ResponseTime1, ResponseTime2, ResponseTime3) |
Sqrt | Returns the square root of the input number. | Sqrt(number) | Sqrt(TimeToResolveInHours) |
Tostring | Converts any argument to a string. | Tostring(generic argument) | Tostring(TicketID) |
2. String functions
String functions can be used to measure the length, extract relevant portions, replace words, and identify matching text. These functions are valuable for extracting key information from ticket descriptions, identifying specific keywords in customer inquiries, and formatting ticket details for more efficient reporting and analysis.
Function | Description | Usage | Examples |
Len | Returns the number of characters in a text string. | Len(string) | Expression: Len(CustomerMessage) Len('Issue with the product') returns 20 (to evaluate whether a message length is detailed or concise). |
Find | Finds the nth occurrence of a string. | Find('string','search string',number) | Expression: Find(TicketDescription, 'error', 1) Find('System error in login', 'error', 1) returns 7 (to returns the position of the first match, here, the word "error" starts at position 7) |
Equals | Returns true if given string matches the other string; otherwise, returns false. This is case-sensitive. | Equals('string',match string') | Expression: Equals(TicketPriority, 'High') Equals('High', 'high') returns false (useful for matching exact priority labels) |
EqualsIgnoreCase | Returns true if given string matches the other string irrespective of the upper or lower case, otherwise returns false. | EqualsIgnoreCase('string',match string') | Expression: EqualsIgnoreCase(TicketPriority, 'high') EqualsIgnoreCase('High', 'high') returns true (for flexible priority matching) |
IsEmpty | Returns true if given string is empty; otherwise, returns false. | IsEmpty(string) | Expression: IsEmpty(AssignedAgent) IsEmpty('John Doe') returns false (to flag unassigned tickets) |
Concat | Returns the concatenation of all the strings. | Concat('string','string',...) | Expression: Concat('Ticket ID: ', Tostring(TicketID), ' - Priority: ', TicketPriority) Concat('Ticket ID: ', '1234', ' - Priority: ', 'High') returns "Ticket ID: 1234 - Priority: High" |
Contains | Returns true if the search string is found in the other string; otherwise, returns false. | Contains('string','search string') | Expression: Contains(TicketDescription, 'refund') Contains('Request for a refund', 'refund') returns true (to filter tickets with specific keywords) |
Startswith | Returns true if the string begins with the search string; otherwise, returns false. | Startswith('string','search string') | Expression: Startswith(TicketStatus, 'Pending') Startswith('Pending Review', 'Pending') returns true (for filtering pending tickets) |
Endswith | Returns true if the string ends with the search string; otherwise, returns false. | Endswith('string','search string') | Expression: Endswith(TicketStatus, 'Resolved') Endswith('Case Resolved', 'Resolved') returns true (to find resolved tickets) |
Lower | Converts all characters in a string to lower case. | Lower('string') | |
Upper | Converts all characters in a string to upper case. | Upper('string') | Expression: Upper(CustomerName) Upper('john') returns "JOHN" |
Trim | Returns string with the leading and trailing white space characters removed. | Trim('string') | Expression: Trim(TicketSubject) Trim(' Login Issue ') returns "Login Issue" |
Substring | Returns a portion of an input string, from a start position in the string to the specified length. | Substring('string',n1,n2) | Expression: Substring(CustomerMessage, 0, 10) Substring('Login issue encountered', 0, 10) returns "Login issu" |
Replace | Replaces each occurrence of the search string in the input string with the corresponding replace string. | Replace('string','search string','replace string') | Expression: Replace(CustomerMessage, 'error', 'issue') Replace('System error occurred', 'error', 'issue') returns "System issue occurred" |
Tonumber | Converts the input string into a numeric format | Tonumber(generic) | Expression: Tonumber(TicketResponseTime) Tonumber('48') returns 48 |
Points to remember
- String constants must always be enclosed in single quotes (').
- Field labels do not require single quotes (').
- String values can be used with functions like Equals, EqualsIgnoreCase, and Not(EqualsIgnoreCase) within conditional functions such as If(), Or(), And(), and Not(). Example: If(Equals(String1, 'abc'), ${returnValue1}, ${returnValue2}).
- Generic data types (Number, String, DateTime, Boolean) are accepted in the functions.
- The return type will depend on the selected data type. For example: If the input is a number, the return type will be numeric. It cannot be a string or boolean.
3. Boolean / logical functions
Boolean and logical functions help evaluate conditions and return true or false based on specific criteria.
Function | Description | Usage | Examples |
If | Returns one of two values, depending on the value of a given logical condition. If the Boolean test is true, The condition returns the true value; otherwise, returns the false value. | If(Boolean,Generic, Generic)* | Expression: If(TicketPriority = 'High', 'Escalate', 'Normal Process') If('High' = 'High', 'Escalate', 'Normal Process') returns "Escalate" |
And | Returns a true response if all expressions are true; returns a false value even if one of the expressions is false. | And(boolean,boolean ...) | Expression: And(TicketPriority = 'High', SLAExceeded = true) And('High' = 'High', true) returns true (both conditions must be true for escalation) |
Or | Returns true if atleast one expression is true. Returns false if all expressions are false. | Or(boolean,...) | Expression: Or(TicketStatus = 'Open', TicketPriority = 'Critical') Or('Open' = 'Open', 'Critical' = 'Low') returns true (as one condition is true) |
Not | Returns the logical negation of the given expression(If the expression is true, returns false). | Not(boolean) | Expression: Not(TicketClosed) Not(false) returns true (to indicate the ticket is not closed) |
4. Date / DateTime functions
Date and DateTime functions allow the computation of date and time values within formulas. These functions help calculate ticket aging, compare timestamps (e.g., ticket creation vs. resolution), and manage deadlines.
Function | Description | Usage | Examples |
Newdate | Creates a date from the year, month, day, and time. | Newdate(year,month,day, hour,minute,'string') | Expression: Newdate(2023, 01, 18, 09, 30, 'AM') Newdate(2007,12,21,06,30,'AM') returns quot;21/12/2007 06:30 AM"1 |
Datepart | Returns the date value for the date time expression. | Datepart(datetime argument) | Expression: Datepart(TicketCreationDate) Datepart(Newdate(2007,12,21,06,30,'AM')) returns "21/12/2007"1 |
Timepart | Returns the time value for the date time expression. | Timepart(datetime argument) | Expression: Timepart(TicketCreationDate) Timepart(Newdate(2007,12,21,06,30,'AM')) returns "06.30 AM" |
Adddate | Returns the date obtained by adding the specified value to the given date. The value must be specified using the ISO8061 format 'PnYnMnDTnHnMnS', where n should be replaced by a value concerning the period/time notation that follows it. For example, the first n should be filled in with year values. So P2Y means a Period of two Years. Similarly, P2Y3M28DT8H30M22S represents a Period of 2 Years 3 Months 28 Days and Time 8 Hours 30 Mins 22 Seconds. | Adddate(datetime, string) | Expression: Adddate(Newdatetime, 'P2Y') Adddate(Newdatetime(2007,12,21,06,30,'AM'),'P2Y') gives result as 21/12/2009 06:30 AM |
Subdate | Returns the date obtained by subtracting the specified value to the given date. The value must be specified using the ISO8061 format 'PnYnMnDTnHnMnS', where n should be replaced by a value concerning the period/time notation that follows it. For example, the first n should be filled in with year values. So P2Y means a Period of two Years. Similarly, P2Y3M28DT8H30M22S represents a Period of 2 Years 3 Months 28 Days and Time 8 Hours 30 Mins 22 Seconds | Subdate(datetime, string) | Expression: Subdate(Newdatetime, 'P2Y') Subdate(Newdatetime(2007,12,21,06,30,'AM'),'P2Y') gives result as 21/12/2005 06:30 AM |
Now | Returns a date/time representing the current moment. | Now() | Expression: Now() Now() returns "19/05/2009 10:52 AM" |
Datecomp | Compares two dates and returns the difference of days in minutes. | Datecomp(Datetime, Datetime) | Expression: Datecomp(TicketCreated, TicketResolved) Datecomp(Newdate(2009,05,19,11,30,'AM'), Newdate(2009,05,19,12,30,'AM')) returns 660.0 |
Dayofmonth | Returns the day of the month for the given date. | Dayofmonth(Datetime) | Expression: Dayofmonth(TicketCreationDate) Dayofmonth(Newdate(2009,05,19,11,30,'AM')) returns "19.0" |
Hour | Returns the hour corresponding to the given date. | Hour(Datetime) | Expression: Hour(TicketCreationDate) Hour(Newdate(2009,05,19,11,30,'AM')) returns "11.0" |
Minute | Returns the minute corresponding to the given date. | Minute(Datetime) | Expression: Minute(TicketCreationDate) Minute(Newdate(2009,05,19,11,30,'AM')) returns "30.0" |
Month | Returns the month corresponding to the given date. | Month(Datetime) | Expression: Month(TicketCreationDate) Month(Newdate(2009,05,19,11,30,'AM')) returns "5.0" |
Year | Returns the year corresponding to the given date. | Year(Datetime) | Expression: Year(TicketCreationDate) Year(Newdate(2009,05,19,11,30,'AM')) returns "2009.0" |
Weekday | Returns the day of the week (1-7) corresponding to the input date, where 1 is Sunday, 2 is Monday and so on. | Weekday(Datetime) | Expression: Weekday(TicketCreationDate) Weekday(Newdate(2009,05,19,11,30,'AM')) returns "3.0". (This is because 19th May is Tuesday) |
Note:
- The input DateTime argument format should always be YYYY,MM,DD,HH,MM,AM/PM but the output will be displayed as per the selected Country Locale.
- The string data type (YEAR/DAY/MONTH/HOUR/MINUTE) should be in UPPERCASE.
- The resulting return value for the Datecomp function is always displayed in minutes.
- If the date value is "0", the function returns null.
Formula Syntax Reference
1. Numeric Functions
2. String Functions
3. DateTime Functions
4. Boolean Functions
Let's look into some use cases based on the available operators and functions for the formula field
Use case 1: Calculating region wise shipping cost
The total cost for shipping an item using product base rate, shipping charges, and GST.
Return type of the formula: Currency
Formula: (Item_Base_Rate + Shipping_Charges) + ((Item_Base_Rate + Shipping_Charges) * (GST / 100))
Use case 2: Calculate the employees years of experience
To determine an employee's total years of experience based on their date of joining the organization.
Return type of the formula: Decimal
Formula: Year(Now())-Year(${Date_Of_Joining})
Use case 3: Calculate pending stock for raw materials
The inventory team wants to track the number of pending stocks for raw materials by determining the difference between the total ordered quantity and the quantity received.
Return type of the formula: Decimal
Formula: (Total_Ordered) - (Quantity_Received)
Adding a formula field
A formula field can be added to a record's layout while creating or editing a layout. Users need to navigate to the layout page of a record by selecting the desired department within a module.
Points to remember
- When selecting multiple fields for computations, the return type should be one of the selected field types. For example, if one field is text and another is a number, the return type should be chosen based on the operation being performed, either as a number or text.
- The number of formula fields available includes the return type of the field. For example, if you have 10 formula fields and have reached the limit of 100 string fields in your account, you won't be able to create another formula field that returns a string value.
- Once a formula field is created in one department, the same formula field can be applied across multiple layouts, and it will appear in the unused field tray of other layouts.
- Expressions (formulas written in the formula field editor) can contain up to 1,500 characters, including functions and operators.
- The value in a formula field cannot be edited by the user and is updated automatically when related fields or the record are updated. The formula can be edited the same way as when it was created, but the return type cannot be changed.
- The formula field can be used in table views, custom views, and reports, but it can't be used in automation.
To add a formula field
- Go to Setup > Customization > Layouts and Fields.
- Select the desired layout and department.
- Open the create or edit layout page.
- From the Add Field tray, drag and drop the Formula field in the desired section.
The Add Field window will appear. - In the Add Field window, do the following:
- Enter the Formula Field Label name.
- Select the formula Return Type.
- Under the Expression editor, enter the desired computations as expressions.
- You can use both the operators and functions that are available.
- Use Check Syntax to check the syntax of the expression.
- Enable the Show Tooltip option to provide guidance for filling out the field with the correct data.
- Check the Display for End User option to allow end users to view the value in the field.
- Click Save.
- Click Save and Close to close the layout
Accessing a formula field
Web
Android
Web
The formula field is accessible in the record’s detail view page but will not appear on the record’s creation or edit form. The formula calculation occurs only after the record is created. For instance, when a new ticket is added, the formula field and its computed value will be visible in the ticket's detail view. Additionally, if any field values in the ticket's detail view are updated, the formula field will recalculate, and the result will be updated after editing the field.
To access the formula field
- Go to the desired module and layout.
- Add a new record.
- Open the record in its detail view.
- The formula field and its computed value will be visible.
Android
In the mobile app, users can only view the fields. Once a formula field is created, it will be reflected in the mobile app only after refreshing the portal metadata.
To refresh the portal metadata, open the Zoho Desk app > Hamburger Menu 
> Settings 
> Refresh Portal Metadata.
The computed data will be visible in the following pages in six different data types (Decimal, Boolean, Currency, String, Date, Datetime):
- Ticket properties
- Activities (Events and Tasks),
- Contacts
- Accounts
It will not be visible while creating or editing a ticket.
Deleting a formula field
The formula field can be removed and moved to the unused field tray, from where it can be permanently deleted.
To delete a formula field
- Open the layout in which the formula field is added.
- Click on the Field Settings icon beside the formula field.
- Select Remove Field.
In the Unused Fields tray, delete the formula field permanently.
Access your files securely from anywhere
Zoho CRM Training Programs
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
Zoho DataPrep Personalized Demo
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
- Zoho Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
New to Zoho Writer?
Create. Review. Publish.
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
Related Articles
Creating Custom Lookup Fields in Zoho Desk
The ability to establish connections between different sets of data is crucial in any business. For example, in the hospitality industry, departments like sales, marketing, and bookings can derive essential insights from customer data if it is linked ...Creating nested picklist fields
Record Creation forms are used to collect and display data. The more organized, clean, and streamlined these forms are,the more efficient the data collection process will be. A nested picklist is a type of picklist field that can help you build ...Viewing Formula Field in Android
Formula field is currently available only on Early Access. Kindly fill out this Registration Form to request early access. Formula field is a custom field that automatically derives a value by computing the values in the different fields. The output ...Working with Custom Fields
In Zoho Desk, custom fields help you capture and organize information that may not be covered by default system fields. Because custom fields are user-defined, they can be tailored to meet the specific needs of your business and industry. For ...Understanding Layouts and Layout Rules
Creating Layouts Layouts control the organization of fields and related sections on the module page of a department. Each department can have its own layout that consists of both default and custom fields. They also determine which fields are ...
New to Zoho LandingPage?
Zoho LandingPage Resources