Welcome to Portal
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:
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.
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. |
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) |
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
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) |
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:
Let's look into some use cases based on the available operators and functions for the formula field
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
Return type of the formula: Decimal
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)
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
To add 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
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
Subscribe to receive notifications from this article.