Configuring Magentrix Entity Formula Fields

Magentrix Entities can include Formula Fields - customized fields that can return special values depending on the provided formula or function. Formula fields are read-only fields that automatically calculate their values based on other fields, expressions, or functions you define.

Before You Begin

Requirements

To configure Entity Formula Fields, users must be assigned a security role with the following permissions:

• Administrator System Role

Understanding Formula Fields

Formula fields are powerful tools that allow you to:

• Perform calculations using values from other fields
• Display derived information without storing redundant data
• Concatenate text from multiple fields
• Apply conditional logic to display different values
• Perform date and time calculations

Formula fields automatically recalculate when any of their source fields change, ensuring the displayed value is always current.

Configuring Magentrix Entity Formula Fields

To configure Magentrix entity formula fields:

1. Create a new or edit an existing Formula Field.
2. In the Formula Return Type section, select the data type for the returned value of the Formula Field. The data type specified in this section must correspond with the evaluated output of the Formula Field.

Available return types:

• Checkbox: Returns True or False
• Currency: Returns a monetary amount
• Date: Returns a date value
• DateTime: Returns a date and time value
• Number: Returns a numeric value
• Percent: Returns a percentage value
• Text: Returns text or string values

3. If you selected a numeric data type (Number, Currency, or Percent), in the Decimal Places field, specify the maximum amount of decimal digits returned.

4. In the Enter Formula section, customize the formula of the Formula Field:

   a. In the Insert Field dropdown list or with Field Picker, add field values to the formula. Click on a field to insert it into the formula at the cursor position.

   b. In the Insert Operator dropdown list, add mathematical or logical operators to the formula:

   • Mathematical operators: +, -, *, / (addition, subtraction, multiplication, division)
   • Logical operators: =, !=, <, >, <=, >=, AND, OR, NOT
   • Text operator: & (concatenation)

   c. In the Functions list, add functions to the formula. Click on a function to insert it into the formula.

5. In the Blank Field Handling section, specify how the field handles blank entries:

   • Treat blank fields as zeroes: Blank numeric fields are treated as 0 in calculations
   • Treat blank fields as blanks: Blank fields remain blank and may result in blank formula results

6. If you want to provide an icon offering information on hover, enter a help message in the Inline Help text field.

7. If you want to provide a description of the field, enter a description in the Description text field.

8. If you want to include this field in Entity search results, check Searchable.

9. Click Save.

Available Functions

Formula fields support a comprehensive set of functions organized by category:

Date & Time Functions

ADDDAY(date, days)

• Adds value of days to the day timepart of the DateTime value date; returns the resulting DateTime value
• Example: ADDDAY(CloseDate, 30) adds 30 days to the close date

ADDHOUR(date, hours)

• Adds value of hours to the hour timepart of the DateTime value date; returns the resulting DateTime value
• Example: ADDHOUR(NOW(), 2) adds 2 hours to the current time

ADDMINUTE(date, minutes)

• Adds value of minutes to the minute timepart of the DateTime value date; returns the resulting DateTime value

ADDMONTH(date, months)

• Adds value of months to the month timepart of the DateTime value date; returns the resulting DateTime value
• Example: ADDMONTH(StartDate, 3) adds 3 months to the start date

ADDSECOND(date, seconds)

• Adds value of seconds to the second timepart of the DateTime value date; returns the resulting DateTime value

ADDYEAR(date, years)

• Adds value of years to the year timepart of the DateTime value date; returns the resulting DateTime value
• Example: ADDYEAR(BirthDate, 18) adds 18 years to birth date

DATE(year, month, day)

• Returns a DateTime value with the specified year, month, and day values
• Example: DATE(2025, 12, 31) creates a date for December 31, 2025

DATETIME(year, month, day, hour, minute, second)

• Returns a DateTime value with the specified year, month, day, hour, minute, and second values

DAY(date)

• Returns the day timepart of the DateTime value date as a number of 1 to 31
• Example: DAY(CreatedDate) returns the day of the month

DAYOFYEAR(date)

• Returns the day timepart of the DateTime value date as a number from 1 to 366

HOUR(date)

• Returns the hour timepart of the DateTime value date

MINUTE(date)

• Returns the minute timepart of the DateTime value date

MONTH(date)

• Returns the month timepart of the DateTime value date as a number from 1 to 12

SECOND(date)

• Returns the second timepart of the DateTime value date

YEAR(date)

• Returns the year timepart of the DateTime value date
• Example: YEAR(CreatedDate) returns the year the record was created

TODAY()

• Returns the current date as a DateTime value
• Example: TODAY() - CloseDate calculates days until close date

NOW()

• Returns the current time and date as a DateTime value
• Example: NOW() returns the current date and time

Logical Functions

IF(logicalTest, trueValue, falseValue)

• Evaluates the expression logicalTest; if logicalTest evaluates to True, returns trueValue, else falseValue
• Example: IF(Amount > 10000, "Large", "Small") returns "Large" if amount exceeds 10000

ISNULL(expr)

• Evaluates the expression expr; if expr evaluates to null, returns True, else False
• Example: ISNULL(CloseDate) returns True if close date is blank

ISNUMERIC(expr)

• Evaluates the expression expr; if expr evaluates to a numeric value, returns True, else False

NOT(bool)

• Returns the opposite value of the boolean bool (i.e. if bool is True, returns False; if False, returns True)
• Example: NOT(IsActive) reverses the checkbox value

NULLVALUE(expr, subExpr)

• Returns the output of expression expr if the value is not null, else the output of subExpr
• Example: NULLVALUE(CustomValue, 0) returns CustomValue or 0 if blank

Math Functions

ABS(num)

• Returns the absolute value of the number num
• Example: ABS(Amount) returns the positive value regardless of sign

Text Functions

INDEX(exprToFind, exprToSearch, startLocation)

• Searches the string exprToSearch for the string exprToFind, starting from the character specified by startLocation; if found, returns the starting position of the string
• Example: INDEX("@", Email, 1) finds the position of @ in an email address

LEN(text)

• Returns the character length of string text
• Example: LEN(Description) returns the number of characters

LOWER(text)

• Returns the string text with all characters converted to lowercase
• Example: LOWER(Name) converts name to lowercase

LTRIM(text)

• Returns the string text with all leading blanks removed

REPLACE(expr, pattern, subExpr)

• Searches the string expr for the string pattern; if found, returns expr with pattern replaced by subExpr
• Example: REPLACE(Phone, "-", "") removes dashes from phone numbers

REVERSE(text)

• Returns the string text with all characters in reverse order (i.e. the last character appears first)

RTRIM(text)

• Returns the string text with all trailing blanks removed

SUBSTRING(expr, start, length)

• Returns a substring of the string expr starting at the position specified by start with the character length specified by length
• Example: SUBSTRING(Name, 1, 3) returns the first 3 characters

TEXT(value)

• Returns any value specified in value as a string with the same display characters (i.e. decimal value 52 would return as string value "52")
• Example: TEXT(Amount) converts a number to text

TRIM(text)

• Returns the string text with all leading and trailing blanks removed
• Example: TRIM(Name) removes extra spaces

UPPER(text)

• Returns the string text with all characters converted to uppercase
• Example: UPPER(Status) converts status to uppercase

Formula Field Examples

Example 1: Full Name (Text)

FirstName & " " & LastName

Concatenates first name and last name with a space between.

Example 2: Days Until Close (Number)

CloseDate - TODAY()

Calculates the number of days between today and the close date.

Example 3: Discount Amount (Currency)

Amount * (DiscountPercent / 100)

Calculates the discount amount based on percentage.

Example 4: Is Overdue (Checkbox)

IF(AND(NOT(ISNULL(DueDate)), DueDate < TODAY(), Status != "Closed"), TRUE, FALSE)

Returns True if the due date has passed and status is not closed.

Example 5: Account Age in Years (Number)

(TODAY() - CreatedDate) / 365

Calculates how many years since the account was created.

Example 6: Priority Label (Text)

IF(Priority = "High", "🔴 HIGH PRIORITY", IF(Priority = "Medium", "🟡 MEDIUM", "🟢 LOW"))

Creates a formatted priority label with visual indicators.

Example 7: Next Anniversary Date (Date)

DATE(YEAR(TODAY()) + 1, MONTH(StartDate), DAY(StartDate))

Calculates the next anniversary of a start date.

Example 8: Formatted Phone (Text)

IF(LEN(Phone) = 10, "(" & SUBSTRING(Phone, 1, 3) & ") " & SUBSTRING(Phone, 4, 3) & "-" & SUBSTRING(Phone, 7, 4), Phone)

Formats a 10-digit phone number as (555) 123-4567.

Blank Field Handling

Understanding how formula fields handle blank (null) values is critical for creating accurate formulas:

Treat Blank Fields as Zeroes

When this option is selected:

• Blank numeric fields are treated as 0 in calculations
• Useful for sum and count operations where you want blanks to not affect the calculation
• Example: Field1 + Field2 returns 5 if Field1 = 5 and Field2 is blank

Treat Blank Fields as Blanks

When this option is selected:

• Blank fields remain blank in calculations
• If any field in a calculation is blank, the entire formula result may be blank
• More accurate for calculations where blanks indicate missing data
• Example: Field1 + Field2 returns blank if either field is blank

Best Practice: Choose "Treat blank fields as blanks" unless you specifically need blanks to be treated as zero.

Formula Field Restrictions and Errors

Restrictions

Text Field Operations: When performing operations on Text Fields, only the first 4,000 characters of the field will be used in the formula calculation.

Errors

The message #Error! will appear when an error occurs in the calculation of a formula. Check your formula for the following issues to resolve the error:

Division Errors:

• When performing Divisions or Mod operations, ensure the formula is not dividing by zero
• Use the ISNULL or IF function to check for zero denominators
• Example: IF(Denominator = 0, 0, Numerator / Denominator)

Date Function Errors:

• When performing Date Functions, ensure the correct Day and Month values are passed
• Month values must be between 1 and 12
• Day values must be valid for the specified month
• Example: DATE(2025, 2, 30) will error because February doesn't have 30 days

Type Mismatch Errors:

• Ensure the formula return type matches the actual output
• If the formula returns text, the return type must be Text
• If the formula returns a number, the return type must be Number, Currency, or Percent

Field Reference Errors:

• Ensure all referenced fields exist and are spelled correctly
• Field names are case-sensitive in formulas
• Use the Field Picker to avoid spelling errors

Best Practices and Recommendations

• Test formulas thoroughly: Create test records with various data scenarios including blank fields, zero values, and edge cases.
• Use meaningful field names: When referencing fields in formulas, clear field names make formulas easier to read and maintain.
• Handle blank values explicitly: Use ISNULL or NULLVALUE functions to handle blank fields appropriately.
• Avoid circular references: Don't create formulas that reference fields that reference back to the formula field.
• Document complex formulas: Add detailed descriptions to complex formula fields explaining the business logic.
• Consider performance: Complex formulas with many nested IF statements can impact performance. Simplify when possible.
• Use helper formulas: Break complex calculations into multiple formula fields for easier maintenance.
• Validate return types: Ensure your formula return type matches what the formula actually returns.
• Use TEXT() for mixed operations: When combining numbers and text, convert numbers to text using TEXT() function.
• Plan for internationalization: Be aware that date and number formats may vary by locale.

Troubleshooting Tips

Issue: Formula displays #Error!

Solution: Check for division by zero, invalid date values, or type mismatches. Review the error types listed in the Restrictions and Errors section.

Issue: Formula returns blank when it shouldn't.

Solution: Check your Blank Field Handling setting. If set to "Treat blank fields as blanks", any blank input field will result in a blank output. Change to "Treat blank fields as zeroes" or use NULLVALUE() to provide default values.

Issue: Text concatenation not working.

Solution: Ensure you're using the & operator for text concatenation. Use TEXT() to convert numbers to text before concatenating.

Issue: Date calculation returns unexpected results.

Solution: Verify that date fields are not blank. Use ISNULL() to check for blank dates before performing calculations.

Issue: Formula field not updating.

Solution: Formula fields update when their source fields change. If a source field is updated through an API or bulk operation, ensure the record is saved to trigger recalculation.

Issue: Cannot save formula field.

Solution: Check that the formula syntax is correct, all referenced fields exist, and the return type matches the formula output. Look for missing parentheses or incorrect function names.

Issue: Formula references don't appear in formula editor.

Solution: Use the Field Picker or Insert Field dropdown to select fields rather than typing field names manually. This ensures correct field API names are used.

Issue: Number formula returns too many decimal places.

Solution: Set the Decimal Places field to limit the number of decimals displayed. The calculation will still use full precision internally.

See Also

• Managing Entity Fields
• Custom Entity Fields Overview
• Rollup Summary Fields
• Master Detail Fields and Lookup Fields

Jump to Magentrix Entity Checklist

<< Custom Entity Fields Overview | Rollup Summary Fields >>