Working with calculated fields
Starting with NexJ CRM version 23.05, in addition to regular fields and collection fields, you can now include calculated fields in a report. These fields are identified by the Calculated Field icon 
- references to existing report fields
- common arithmetic and comparison operators
- functions that perform calculations and manipulations on numeric, text, date, timestamp, and Boolean data
- conditional logic and logical operators
To include a calculated field in the report, click Add Calculated Field below the Fields section on the Detail tab, and use the Add Calculated Field dialog to specify the new field name and the formula that should be used to calculate its value.
The dialog contains the following fields and sections:
| Field or Section | Description | Required |
|---|---|---|
| Field Name | The name of the calculated field, which is displayed in the report. | Yes |
| Field Description | The description of the calculated field. | No |
| Field Type | The field type or format is determined based on the results of the formula. It is not displayed as you are defining your formula. Click Validate after completing the formula to display the field type. The type is displayed until you start editing the formula again. | n/a |
| Formula | The formula you are using to define the calculated field. You can type directly into the field, or use the operator buttons, and field and function lists below to specify the formula. | n/a |
| Operators | Arithmetic, comparison, and logical operators which can be used in the formula. | n/a |
| Fields | Refer to any existing field included in the selected report subject area. Use the search field above the list to find a specific field without scrolling. Hover over the name of the field to display available actions.
| n/a |
| Functions | Select a supported function to be included in the formula. The list of functions is grouped by function type. Use the search field above the list to find a specific function without scrolling. Hover over the name of the function to display available actions.
| n/a |
Within the Formula field, the order of operations is as follows:
- Grouping or parentheses ()
- Exponent ^
- Division and multiplication / *
- Addition and subtraction + -
- Logical NOT
- Equality and comparison = != < <= > >=
- Logical AND
- Logical OR
Arithmetic, comparison, and logical operators
| Operator type | Operator | Description | Example | Input and return types |
|---|---|---|---|---|
| + | Adds two numbers together. | 'Open Days' + 7
| Input: Numeric, date, timestamp Return: Return type matches input type | |
| - | ||||
| * | ||||
| / | ||||
| ^ | ||||
| Division, multiplication, exponent * / ^ | *, /, ^: Supported with numeric values only. | x + y + z | Input: Numeric Return: Numeric | |
| Addition and subtraction + - | +, - : Supported with numbers, date and timestamps values. | |||
| Comparison operations: =, !=, >, >=, <, <= | =, !=: Used to determine (in)equality between two values of the same type. >, >=, <, <=: Supported with numbers, date and timestamps values. | x >= y | Input: Numeric Return: Boolean | |
| Comparison: =, != | TRUE if the two values are equal, FALSE otherwise. Supported types: text, date, timestamp, number. TRUE if the two values are unequal, FALSE otherwise. Supported types: text, date, timestamp, number. Not sure what an example here would look like or if we need one? Reusing the copy in other comparison operations. | string1 = string2 string1 != string2 | string1: String string2: String Return: Boolean | |
| Comparison operations: =, !=, >, >=, <, <= | Supported types: text, date, timestamp, number. For = and != is Boolean supported as well? Supported types: date, timestamp, number. TRUE if the first value is greater than or equals the second value, FALSE otherwise. Supported types: date, timestamp, number. TRUE if the first value is less than the second value, FALSE otherwise. Supported types: date, timestamp, number. TRUE if the first value is less than or equals the second value, FALSE otherwise. Supported types: date, timestamp, number. See description under "Basic arithmetic" | value1 = value2 value1 != value2 | value1: Date / Timestamp value2: Date / Timestamp Return: Boolean | |
| Comparison operations: , , , , , Currently currency values are treated as numbers. No currency conversion (based on the currency unit) is performed. | See description under "Basic arithmetic" | |||
| Comparison | = | Returns TRUE if the two values are equal, FALSE otherwise. | value1 = value2 | value1: String, Boolean, date, timestamp, number value2: The same as value1 Return: Boolean |
| != | Returns TRUE if the two values are unequal, FALSE otherwise. | value1 != value2 | value1: String, Boolean, date, timestamp, number value2: The same as value1 Return: Boolean | |
| > | TRUE if the first value is greater than the second value, FALSE otherwise. | va | ||
| >= | ||||
| < | ||||
| <= | ||||
| Logical | AND | Logical AND. Returns TRUE if both arguments value1 and value2 are TRUE. | value1 AND value2 | value1: Boolean value2: Boolean Return: Boolean |
| OR | Logical OR. Returns TRUE if at least one of arguments value1 and value2 is TRUE. | value1 OR value2 | value1: Boolean value2: Boolean Return: Boolean | |
| NOT | Logical NOT. Returns TRUE if the argument value is FALSE. Returns FALSE if the argument value is TRUE. | NOT value | value: Boolean Return: Boolean | |
| Parentheses | () | Used to define the order of evaluation. | value1 AND (value2 OR value3) NOT (value1 OR value2) | n/a |
Numeric functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| ABS (number) | Returns absolute value of the number. | ABS('Actual Amount'-'Expected Amount') | number: Numeric Return: Numeric |
| CEILING (number) | Rounds the number up to the nearest integer. | CEILING('Actual Amount') | number: Numeric Return: Numeric (integer) |
| FLOOR (number) | Rounds the number down to the nearest integer. | FLOOR('Actual Amount') | number: Numeric Return: Numeric (integer) |
| ROUND (number, precisionNumber) | Rounds the number to the precisionNumber of digits. | ROUND('List Price',2) | number: Numeric precisionNumber: Numeric (integer) Return: Numeric |
| SQRT (number) | Calculates the positive square root of the number. | SQRT(25) | number: Numeric Return: Numeric |
String functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
TEXT (value) | Converts a number, currency, Boolean, date, or timestamp value to a text. | TEXT('Date of Birth') | value: Numeric, currency, Boolean, date, timestamp Return: String |
| CONCAT (text1, text2, ...) | Combines multiple text values into a single text. | CONCAT("Dear ",'Full Name') | text1, text2, ...: String Return: String |
| CONTAINS (text, searchText) | Returns TRUE if text contains searchText, FALSE otherwise. | CONTAINS('Full Name', "Lamont") | text: String searchText: String Return: Boolean |
| LEFT (text, number) | Returns the specified number of leftmost characters from the text. | LEFT('Home Address : Postal Code/Zip', 3) | text: String number: Numeric (integer) Return: String |
| RIGHT(text, number) | Returns the specified number of rightmost characters from the text. | RIGHT(TEXT('Start Time'), 2) | text: String number: Numeric (integer) Return: String |
| LOWER (text) | Coverts all letters in the specified text to lower case. | LOWER ('Home Address : Postal Code/Zip') | text: String Return: String |
| UPPER (text) | Converts all letters in the specified text to upper case. | UPPER ('Home Address : Postal Code/Zip') | text: String Return: String |
| LENGTH (text) | Returns the number of characters in the specified text. | LENGTH('Description') | text: String Return: Numeric (integer) |
| TRIM (text) | Returns the specified text with leading and trailing spaces removed. | TRIM('Description') | text: String Return: String |
Date and timestamp functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| DATE (text) | Converts text in the YYYY-MM-DD format to the equivalent date value. | DATE("2022-11-23") | text: String Return: Date |
| TIMESTAMP (text) | Converts text in the YYYY-MM-DD HH:mm:ss.SSS format to the equivalent timestamp value. | TIMESTAMP("2022-11-23 17:12:33.147") | text: String Return: Timestamp |
| TODAY() | Return today's date as a date value. | TODAY() | Return: Date |
| NOW() | Returns current date and time as a timestamp value. | NOW() | Return: Timestamp |
| YEAR (value) | Returns the year of the specified date or timestamp value as a number. | YEAR(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| QUARTER (value) | Returns the calendar quarter of the specified date or timestamp value as a number. | QUARTER(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| MONTH (value) | Returns the month of the specified date or timestamp value as a number. | MONTH(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| DAY (value) | Returns the day of the specified date or timestamp value as a number. | DAY(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| HOUR (value) | Returns the hour of the specified timestamp value as a number. For date values, zero is returned. | HOUR(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| MINUTE (value) | Returns the minute of the specified timestamp value as a number. For date values, zero is returned. | MINUTE(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| SECOND (value) | Returns the second of the specified timestamp value as a number. For date values, zero is returned. | SECOND(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| DATEDIFF (interval, value1, value2) | Calculates the difference between the two date or timestamp values as the number of elapsed intervals, by subtracting value2 from value1. Supported intervals: "day", "hour", "minute", "second", "millisecond". | DATEDIFF ("minute", 'End Time', 'Start Time') | interval: String value1: Date, timestamp value2: Date, timestamp Return: Numeric (integer) |
| DATEADD (interval, number, value) | Adds the specified number of intervals to a date or timestamp value. Use a negative number to subtract the intervals instead. Supported intervals: "year", "month", "week", "day", "hour", "minute", "second", "millisecond". | DATEADD("month", 1, 'Expected Close Date') | interval: String number: Numeric (integer) value: Date, timestamp Return: Date, timestamp |
Currency functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| CURRENCY (number) | Converts the specified number to a currency value, using the system currency units. | CURRENCY('Expected Amount' * 0.15) | number: Numeric Returns: Currency |
Conditional logic
| Function | Description | Example | Input and return types |
|---|---|---|---|
| IF (condition, value1, value2) | Returns value1 if condition is TRUE and value2 otherwise. The data types of value1 and value2 must be the same. | IF(DATEDIFF("day", 'Expected Close', 'Actual Close') >= 0, "On time", "Late") | condition: Boolean value1: Boolean, string, numeric, date, timestamp value2: The same as value1 |
| ISNULL (value) | Returns TRUE if value is NULL and FALSE otherwise. | ISNULL('Company : Coverage Parent') | value: Any Returns: Boolean |