Welcome to Portal

?Unknown\pull-down

Welcome to Zoho Cares

Bienvenido a Soporte de Zoho

Search our knowledge base, ask the community or submit a request.

Creating Formula Fields

AlertFormula 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.


Info 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')
Expression:
Lower(CustomerEmail)

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


  1. Go to Setup > Customization > Layouts and Fields.
  2. Select the desired layout and department.



  3. Open the create or edit layout page.
  4. From the Add Field tray, drag and drop the Formula field in the desired section.
    The Add Field window will appear.



  5. 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.
  6. Click Save.
  7. Click Save and Close to close the layout



Accessing a formula field 

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


  1. Go to the desired module and layout.
  2. Add a new record.
  3. Open the record in its detail view.
  4. The formula field and its computed value will be visible.

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


  1. Open the layout in which the formula field is added.
  2. Click on the Field Settings icon beside the formula field.
  3. Select Remove Field.
    In the Unused Fields tray, delete the formula field permanently.



Helpful?00
Updated: 13 days ago
Share :