Formula Field: Overview
The Formula Field is a versatile tool that allows you to perform numerical calculations using a variety of provided functions and available fields, enabling you to derive dynamic data. It serves as a custom field within your system and can be used to populate results based on specified inputs.This feature is especially helpful for businesses that work with numbers and make decisions based on them.
Consider a real estate company like Zylker Real Estate. They can leverage the Formula Field to automate and calculate property taxes for each property in their account. By creating custom fields such as "Assessed Property Value" and "Local Tax Rate," Zylker can set up a Formula Field named Property Tax that calculates property tax based on the provided values. For example, the formula could be:
Property Tax = Assessed Property Value * Local Tax Rate
Once this formula is configured, the `Property Tax` field will automatically calculate property tax for each property whenever the `Assessed Property Value` or `Local Tax Rate` fields are updated. This automation allows Zylker Real Estate to efficiently manage their property portfolio, estimate expenses, and make informed decisions about buying, selling, or renting properties. You can configure similar formulas for your business needs and use them accordingly.
You can create the field like how you create other fields. Navigate to Settings > Fields > Customize Fields > +Custom Field, provide a field name, and choose Formula as the type.
Once you select the field type as a formula, the next step is to choose the Output Type for the field. The output type defines the kind of data your formula will produce. You can choose from the following options:
- Decimal: For formulas that calculate whole numbers and numbers with decimal places, such as percentages or averages.
- Currency: For formulas that calculate monetary values, allowing you to include currency symbols.
- String: For formulas that return text or combine multiple fields into a single string.
- Date: For formulas that calculate dates, such as calculating a future date based on a starting point.
- Date and Time: For formulas that calculate both dates and times, allowing you to work with precise time stamps.
- Boolean: For formulas that return either `true` or `false`, such as for logical comparisons.
The Output Type is important because it specifies the format and kind of data you will receive from your formula's calculations. Once you have made your selection, you cannot change the Output Type.
Formula Expression
The formula field comprises three key components that enable you to create dynamic and customized calculations:
- Functions: Predefined mathematical or logical operations that you can use in your formula. When you access the Functions tab, you'll see a pop-up list of available functions on the left side. These functions range from arithmetic operations (like addition and multiplication) to conditional statements (such as IF) and text manipulation functions. You can add functions to your formula by simply clicking on them. When you hover over a function, it will show you its syntax, a brief description of its purpose, and examples of how it can be used, providing helpful context for your calculations.
- Fields: In the formula field, you can use data from existing fields in your system as inputs for your calculations. These fields can include numbers, text, dates, or other types of data depending on your needs. By incorporating field values into your formula, you can create calculations that adapt to changes in the underlying data.
- Operators: Allow you to perform operations on the fields and functions in your formula. Common operators include arithmetic operators (such as +, -, *, and /), comparison operators (such as == and !=), and logical operators (such as AND and OR). These operators enable you to create more complex calculations and conditional statements.
Below table provides details about each function mentioned above
Numeric functions
FUNCTION | DESCRIPTION | SYNTAX | EXAMPLES |
Abs | Calculates the absolute value of a number. | Abs(number) | Abs(-5.5) returns 5.5; Abs(10) returns 10 |
Ceil | Rounds up to the nearest integer or higher. | Ceil(number) | Ceil(3.2) returns 4; Ceil(-2.8) returns -2 |
Floor | Rounds down to the nearest integer or lower. | Floor(number) | Floor(3.8) returns 3; Floor(-4.5) returns -5 |
Naturallog | Computes the natural logarithm of a number. | Naturallog(number) | Naturallog(10) returns 2.302 |
Base10log | Calculates the base 10 logarithm of a number. | Base10log(number) | Base10log(100) returns 2.0 |
Max | Finds the maximum value from a list of numbers. | Max(value1, value2,...) | Max(3,7,5,9) returns 9; Max(-2,0,-5,1) returns 1 |
Min | Finds the minimum value from a list of numbers. | Min(value1, value2,...) | Min(4,2,8,1) returns 1; Min(0.5,1,0.1,0.2) returns 0.1 |
Sqrt | Calculates the square root of a number. | Sqrt(number) | Sqrt(16) returns 4; Sqrt(25) returns 5 |
ToNumber | Converts the input to a number. | ToNumber(value) | ToNumber("42") returns 42; ToNumber("3.14") returns 3.14 |
| Round | Rounds a number to the nearest value based on the specified precision. | Round(number), Round(number, precision) | Round(7.6) returns 8; Round(-2.3) returns -2; Round(5.678, 2) returns 5.68 |
IsPositive | Checks if a given number is positive. | IsPositive(number) | IsPositive(456) # Returns True; IsPositive(-789) # Returns False |
IsNegative | Checks if a given number is negative. | IsNegative(number) | IsNegative(-345) # Returns True; IsNegative(123) # Returns False |
String Functions
FUNCTION | DESCRIPTION | USAGE | EXAMPLES |
Len | Returns the number of characters in a specified text string. | Len(string) | Len('hello') returns 5; Len(' open ai ') returns 9 |
Find | Returns the index of the nth occurrence of a substring within another string. | Find('string', 'search string', number) | Find('bookshelf', 'o', 2) returns 6 |
Concat | Concatenates multiple strings into a single string. | Concat('string', 'string', ...) | Concat('First', 'Name') returns FirstName |
Contains | Checks if a string contains another string. | Contains('string', 'search string') | Contains('programming', 'gram') returns true |
Startswith | Checks if a string starts with another string. | Startswith('string', 'search string') | Startswith('hello', 'hi') returns false<br>Startswith('hello', 'hel') returns true |
Endswith | Checks if a string ends with another string. | Endswith('string', 'search string') | Endswith('apple', 'le') returns true<br>Endswith('apple', 'pl') returns false |
Lower | Converts all characters in a string to lowercase. | Lower('string') | Lower('HELLO') returns "hello"<br>Lower('HeLLo') returns "hello" |
Upper | Converts all characters in a string to uppercase. | Upper('string') | Upper('hello') returns "HELLO"<br>Upper('HeLLo') returns "HELLO" |
Trim | Removes leading and trailing whitespace from a string. | Trim('string') | Trim(' hello ') returns "hello" |
Substring | Returns a portion of a string starting from a specified position and with a specified length. | Substring('string', n1, n2) | Substring('programming', 5, 6) returns "am" |
Replace | Replaces occurrences of a substring within a string with another substring. | Replace('string', 'search string', 'replace string') | Replace('hello world', 'world', 'universe') returns "hello universe" |
Tostring | Converts any argument to a string data type. | Tostring(generic argument) | Tostring(3.14) returns "3.14"<br>Tostring('Hello') returns "Hello" |
IsEmpty | Checks whether a value is empty or not. | IsEmpty(generic) | IsEmpty('') returns true<br>IsEmpty('GPT') returns false<br>IsEmpty(${Product.Price}) returns true if Price is not entered |
CaseInsensitiveEquals | Compares two strings without considering case differences. | CaseInsensitiveEquals(string,string) | CaseInsensitiveEquals('hello', 'HeLLo') returns true; CaseInsensitiveEquals('world', 'worLd') returns true; CaseInsensitiveEquals('hello', 'world') returns false. |
DateTime Functions
FUNCTION | DESCRIPTION | USAGE | EXAMPLES |
Newdate | Creates a date from the provided year, month, day, hour, minute, and time indicator. | Newdate(year, month, day, hour, minute, 'string') | Newdate(2024, 5, 14, 9, 30, 'AM') returns "14/05/2024 09:30 AM" |
Datepart | Retrieves the date value from the given date-time expression. | Datepart(datetime argument) | Datepart(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "14/05/2024" |
Timepart | Retrieves the time value from the given date-time expression. | Timepart(datetime argument) | Timepart(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "09:30 AM" |
Adddate | Calculates a new date by adding a specified number of years, days, months, hours, or minutes to the given date. | Adddate(datetime, number, 'string') | Adddate(Newdate(2024, 5, 14, 9, 30, 'AM'), 3, 'DAY') returns "17/05/2024 09:30 AM" |
Subdate | Calculates a new date by subtracting a specified number of years, days, months, hours, or minutes from the given date. | Subdate(datetime, number, 'string') | Subdate(Newdate(2024, 5, 14, 9, 30, 'AM'), 2, 'YEAR') returns "14/05/2022 09:30 AM" |
Now | Returns the current date and time. | Now() | Now() returns the current date and time, e.g., "14/05/2024 10:15 AM" |
Datecomp | Compares two dates and returns the difference in minutes. | Datecomp(datetime, datetime) | Datecomp(Newdate(2024, 5, 14, 10, 0, 'AM'), Newdate(2024, 5, 14, 11, 0, 'AM')) returns 60.0 |
Dayofmonth | Retrieves the day of the month from the given date. | Dayofmonth(datetime) | Dayofmonth(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "14.0" |
Hour | Retrieves the hour from the given date. | Hour(datetime) | Hour(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "9.0" |
Minute | Retrieves the minute from the given date. | Minute(datetime) | Minute(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "30.0" |
Month | Retrieves the month from the given date. | Month(datetime) | Month(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "5.0" |
Year | Retrieves the year from the given date. | Year(datetime) | Year(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "2024.0" |
Weekday | Retrieves the day of the week (1-7) corresponding to the input date, where 1 is Sunday, 2 is Monday, and so on. | Weekday(datetime) | Weekday(Newdate(2024, 5, 14, 9, 30, 'AM')) returns "3.0". (This is because May 14th, 2024, is a Tuesday) |
DateBetween | Calculates the difference between two dates in the specified unit of time, which can be years, months, weeks, days, hours, or minutes. | DateBetween(date-time,date-time,string) | DateBetween(NewDate(2022, 02, 10, 11, 30, 'AM'), NewDate(2023, 02, 19, 11, 30, 'AM'), 'years') returns 1 |
FromTimestamp | Converts a given timestamp into a readable date and time format. | FromTimestamp(number) | FromTimestamp(1627843200) in IST returns "Aug 1, 2021, 12:00 PM". Note: The above example is for the IST timezone. The values will change based on the timezone of the user. |
Timestamp | Convert a specific date and time into a timestamp value. | Timestamp(date-time) | Timestamp(Newdate(2023,07,15,08,45,'PM')) in the IST timezone returns 1,255,403,500. Note: The above example is for the IST timezone. The values will change based on the timezone of the user. |
| Dayofweek | Returns the day of the week for a specified date | Dayofweek(date-time) | Dayofweek(Newdate(2024,07,07,06,15,'PM')) returns Sunday. |
Dayofyear | Calculates and returns the ordinal number of the day in the year for a given date | Dayofyear(date-time) | Dayofyear(Newdate(2023,12,31,08,15,'PM')) returns "365". |
Boolean Functions
FUNCTION | DESCRIPTION | USAGE | EXAMPLES |
If | Returns one of two values based on a logical condition. If the condition is true, returns the first value; otherwise, returns the second value. | If(Boolean, Generic, Generic) | If(5 > 3, "Yes", "No") returns "Yes"<br>If(5 < 3, "Yes", "No") returns "No" |
And | Returns true if all provided expressions are true; otherwise, returns false. | And(boolean, boolean ...) | And(2 > 1, 5 > 3, 7 < 8) returns "true"<br>And(2 > 1, 5 > 3, 7 > 8) returns "false" |
Or | Returns true if at least one of the provided expressions is true; otherwise, returns false. | Or(boolean, ...) | Or(2 > 1, 3 > 5, 7 > 8) returns "true"<br>Or(1 > 2, 3 > 5, 7 > 8) returns "false" |
Not | Returns the logical negation of the given expression. If the expression is true, returns false; if false, returns true. | Not(boolean) | Not(false) returns "true"<br>Not(true) returns "false" |
The formula editor provides shortcuts for quick and efficient access to various components of your formula. By using these shortcuts, you can save time and work more efficiently when creating formulas in the formula editor.
- Use '@' to insert functions: Typing the '@' symbol in the formula editor will open a list of available functions for you to choose from. These functions can include arithmetic operations, text manipulation, logical comparisons, and more. This shortcut allows you to find quickly and insert functions into your formula.
- Use '#' to add fields: Typing the '#' symbol in the formula editor will bring up a list of available fields in your system. You can select fields that you want to use as inputs in your formula. This shortcut makes it easy to incorporate existing data from your system directly into your calculations, enabling you to create formulas.
- Use '~' to add operators: Typing the '~' symbol in the formula editor will display a list of operators that you can use in your formula. These operators include arithmetic operators (e.g., +, -, *, /), and comparison operators (e.g., ==, !=). This shortcut helps you quickly add the appropriate operators to create complex calculations and conditional statements.
Syntax checking
The formula editor has a built-in validation feature that ensures your formula is error-free and accurate. By clicking the Check Syntax button, the editor reviews your formula for issues like incorrect functions, fields, operators, or syntax errors. You can only save the formula once it's error-free, preventing you from saving formulas that might produce incorrect or unexpected results due to errors.
Tooltip
In addition to creating a formula field, you can add a tooltip to provide extra information and guidance. The tooltip appears next to the formula field in detail view, offering users helpful context and explanations. It can describe the formula's purpose, usage instructions, and key aspects of the calculation. Including examples or scenarios can help users understand how the formula works and how to apply the output. With Tooltips, you can enhance user experience by providing clarity and reducing errors when using the formula field.
Blank Value Preference
You can configure a preference for a blank value in the result field of a formula, which allows you to set a default value for the field to use when no specific value is returned by the formula. This preference can be useful in a variety of scenarios, such as when a calculation doesn't yield a specific result or when the data used in the formula is incomplete.
For numerical return types, you can set the default value to zero (0) to maintain consistency in calculations and data analysis.
For string return types, you can set the default value to an empty string ("") to handle cases where the formula does not produce a specific text result. This avoids the field being left blank or causing confusion when no data is returned.
Once all the details are provided, you can save your formula. The formula can only be saved if the syntax is verified.
Blank Value Computation
When you create a formula field, you can specify how to handle blank values in the fields used in the formula.
You can find this option under Blank Value Preference, where you can choose one of the following:
- Consider blank values as empty for strings: This option is selected by default when you create a new formula field.
- Consider blank values as 0 for integers and decimals: This option interprets the blank value as 0 depending on the data type.
Example:
Profit is the formula field, calculated as: Revenue - Cost.
The fields used to generate the profit field are:
- Revenue
- Cost
If the Profit field is blank:
- If you select blank values as empty for the Profit field:
- Revenue is seen as empty.
- Therefore, the formula doesn't produce a result, leaving Profit blank or empty.
- If you select blank values as 0 for the Profit field:
- Revenue is treated as 0.
- The formula calculates Profit as based on this value.
Permissions
Both administrators and users with field-level permissions can access this feature.
Availability
- This feature is exclusively available for Premier Edition and Bigin 360 users.
- The limitation is set to 10 per module.
Click this document to learn more about use cases for Formula Fields.