Help Document
Configuring Magentrix Entity Formula Fields

    CONFIGURING ENTITY FORMULA FIELDS

    Magentrix Entities can include Formula Fields - customized fields that can return special values depending on the provided formula or function.

    Requirements

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

    • Administrator System Role

    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.
    3. If you selected a numeric data type, 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:
      1. In the Insert Field dropdown list or with Field Picker, add field values to the formula.
      2. In the Insert Operator dropdown list, add mathematical or logical operators to the formula.
      3. In the Functions list, add functions to the formula. The following functions are available:

        Date & Time Functions

        ADDDAY(date, days)
        Adds value of days to the day timepart of the DateTime value date; returns the resulting DateTime value
        ADDHOUR(date, hours)
        Adds value of hours to the hour timepart of the DateTime value date; returns the resulting DateTime value
        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
        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
        DATE(year,month,day)
        Returns a DateTime value with the specified year, month, and day values.
        DATETIME(year,month,day)
        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.
        DAYOFYEAR(date)
        Returns the day timepart of the DateTime value date as a number from 1 to 356
        HOUR(date)
        Returns the hour timepart of the DateTime value date
        MINUTE(date)
        Returns the minute timepart of the DateTime value date
        SECOND(date)
        Returns the second timepart of the DateTime value date
        YEAR(date)
        Returns the year timepart of the DateTime value date
        TODAY()
        Returns the current date as a DateTime value
        NOW()
        Returns the current time and date as a DateTime value

        Logical Functions

        IF(logicalTest,trueValue,falseValue)
        Evaluates the expression logicalTest; if logicalTest evaluates to True, returns trueValue, else falseValue.
        ISNULL(expr)
        Evaluates the expression expr; if expr evaluates to null, returns True, else False
        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)
        NULLVALUE(expr,subExpr)
        Returns the output of expression expr if the value is not null, else the output of subExpr

        Math Functions

        ABS(num)
        Returns the absolute value of the number num

        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
        LEN(text)
        Returns the character length of string text
        LOWER(text)
        Returns the string text with all characters converted 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
        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 specified by length
        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")
        TRIM(text)
        Returns the string text with all leading and trailing blanks removed
        UPPER(text)
        Returns the string text with all characters converted to uppercase
    5. In the Blank Field Handling section, specify how the field handles blank entries for the field.
    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.

    FORMULA FIELDS RESTRICTION AND ERRORS

    Restriction

    When performing operations on Text Fields, only the first 4000 characters of the field will be used.

    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:

    • When performing Divisions or Mod operations, consider if the formula is divided by zero.
    • When performing Date Functions, consider if the correct Day and Month are passed.

    << Custom Fields Overview | Rollup Summary Fields >>