Formula Fields in Salesforce
Formula Fields are an out-of-the-box Salesforce feature that enables you to manipulate (get more out of) your existing Salesforce data. Since most of us are familiar with Excel, these fields actually allow us to create simple and complex formulas based on data from a record and from its related records. Your custom formula fields require special attributes. AND( /*competitor field is required, check to see if field is empty */ LEN(Competitor__c) = 0, /* rule only enforced for ABCD record types */ RecordType.Name = “ABCD Value”, /* checking for any closed status, allows for additional closed picklist values in the future */ CONTAINS(TEXT(StageName), “Closed”) ) Math Operators OPERATOR DESCRIPTION + (Add) Calculates the sum of two values. – (Subtract) Calculates the difference of two values. * (Multiply) Multiplies its values. / (Divide) Divides its values. ^ (Exponentiation) Raises a number to the power of a specified number. () (Open Parenthesis and Closed Parenthesis) Specifies that the expressions within the open parenthesis and close parenthesis are evaluated first. All other expressions are evaluated using standard operator precedence. Logical Operators OPERATOR DESCRIPTION = and == (Equal) Evaluates if two values are equivalent. The = and == operators are interchangeable. <> and != (Not Equal) Evaluates if two values aren’t equivalent. < (Less Than) Evaluates if a value is less than the value that follows this symbol. > (Greater Than) Evaluates if a value is greater than the value that follows this symbol. <= (Less Than or Equal) Evaluates if a value is less than or equal to the value that follows this symbol. >= (Greater Than or Equal) Evaluates if a value is greater than or equal to the value that follows this symbol. && (And) Evaluates if two values or expressions are both true. Use this operator as an alternative to the logical function AND. || (Or) Evaluates if at least one of multiple values or expressions is true. Use this operator as an alternative to the logical function OR. Text Operators OPERATOR DESCRIPTION & and + (Concatenate) Connects two or more strings. Date and Time Functions FUNCTION DESCRIPTION ADDMONTHS Returns the date that is the indicated number of months before or after a specified date. If the specified date is the last day of the month, the resulting date is the last day of the resulting month. Otherwise, the result has the same date component as the specified date. DATE Returns a date value from the year, month, and day values that you enter. Salesforce displays an error on the detail page if the value of the DATE function in a formula field is an invalid date, such as February 29 in a non-leap year. DATEVALUE Returns a date value for a date/time or text expression. DATETIMEVALUE Returns a year, month, day, and GMT time value. DAY Returns a day of the month in the form of a number from 1 through 31. DAYOFYEAR Returns the day of the calendar year in the form of a number from 1 through 366. FORMATDURATION Formats the number of seconds with optional days, or the difference between times or dateTimes as HH:MI:SS. HOUR Returns the local time hour value without the date in the form of a number from 1 through 24. ISOWEEK Returns the ISO 8601-week number, from 1 through 53, for the given date, ensuring that the first week starts on a Monday. ISOYEAR Returns the ISO 8601 week-numbering year in 4 digits for the given date, ensuring that the first day is a Monday. MILLISECOND Returns a milliseconds value in the form of a number from 0 through 999. MINUTE Returns a minute value in the form of a number from 0 through 60. MONTH Returns the month, a number between 1 and 12 (December) in number format of a given date. NOW Returns a date/time representing the current moment. SECOND Returns a seconds value in the form of a number from 0 through 60. TIMENOW Returns a time value in GMT representing the current moment. Use this function instead of the NOW function if you only want to track time, without a date. TIMEVALUE Returns the time value without the date, such as business hours. TODAY Returns the current date as a date data type. UNIXTIMESTAMP Returns the number of seconds since 1 Jan 1970 for the given date, or number of seconds in the day for a time. WEEKDAY Returns the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday. YEAR Returns the four-digit year in number format of a given date. Logical Functions FUNCTION DESCRIPTION AND Returns a TRUE response if all values are true, and returns a FALSE response if one or more values are false. BLANKVALUE Determines if an expression has a value, and returns a substitute expression if it doesn’t. If the expression has a value, it returns the value of the expression. CASE Checks a given expression against a series of values. If the expression is equal to a value, it returns the corresponding result. If it isn’t equal to any values, it returns the else_result. IF Determines if expressions are true or false. Returns a given value if true and another value if false. ISBLANK Determines if an expression has a value, and returns TRUE if it doesn’t. If it contains a value, this function returns FALSE. ISCLONE Checks if the record is a clone of another record, and returns TRUE if one item is a clone. Otherwise, returns FALSE. ISNEW Checks if the formula is running during the creation of a new record, and returns TRUE if it is. If an existing record is being updated, this function returns FALSE. ISNULL Determines if an expression is null (blank), and returns TRUE if it is. If it contains a value, this function returns FALSE.You must use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL, but also supports text fields. Salesforce continues to support ISNULL, so