Formula Tips, Operators and Functions

When creating formulas as part of formula products, workflows, formula fields, reports, etc., you can utilize the following operators and functions. Furthermore, the following tips will come in handy when authoring formulas for the various sections in the app that allows the use of formulas:

1. When applicable, consider combining workflow actions by utilizing the CASE statement to come up with multiple possible branches of actions on a single action. This helps reduce the processing time needed whenever workflows are triggered.

2. Consider the possibility of dealing with null fields or records. For example, using the NVL() function will help handle un-initialized checkbox fields as well as empty number fields that may result to errors with computations if a null value is mathematically added to a numeric value.

In the case of the following formula: {!ActivityObj.SomeCustomNumberField} + {!ActivityObj.Rate}

If for some reason either the SomeCustomNumberField and Rate fields are null, the formula may result to an error. This can be avoided by rephrasing the formula into: NVL({!ActivityObj.SomeCustomNumberField}, 0) + NVL({!ActivityObj.Rate}, 0)

This will ensure that if either of the fields has a null value, it will replace it with 0 and allow for the computation to work.

Another case is when comparing field values. {!SomeEntity.SomeField} <> 'Constant Text' DOES NOT include the scenario where SomeField is null. If the condition needs to be true for all instances when it is not equal to 'Constant Text' including null values, it needs to be rephrased as: {!SomeEntity.SomeField} <> 'Constant Text' OR {!SomeEntity.SomeField IS NULL.

Lastly, if a field/value needs to be checked if it is null or not null, use the IS NULL or IS NOT NULL logical operators.

3. For readability, use the indenting of formula/code blocks when applicable. This can be done by using your favorite text editor to compose the formula and then copy and pasting it into the formula entry field of the app. This will allow you to make your formulas readable for troubleshooting later on if needed. For example:

CASE WHEN {!Collector_FieldsObj.BillingIdentifier} = 'Prop-Pass' 
	THEN TO_DATE({!Collector_FieldsObj.ACTIVITY_DATE}, 'DD/MM/YYYY')
WHEN {!Collector_FieldsObj.BillingIdentifier} = 'Aeronautical'
	THEN TO_DATE({!Collector_FieldsObj.StartDate}, 'DD/MM/YYYY')
ELSE THEN TO_DATE({!Collector_FieldsObj.ToDate}, 'DD/MM/YYYY')
END 

4. Using independent selects can be used to provide additional flexibility for obtaining data that is outside of the context of the object relationship where the formula is used. For instance, you can select data from a custom entity field in a custom entity from a workflow that runs on the Account object even if there are no lookup fields between the Account and custom entity. For example, take the following Formula Value parameter for a formula product:

CASE WHEN {!ActivityObj.InvoiceIdObj.CumulativeInvoiceEnabled} = 1
 	THEN (select {!InvoiceObj.CumulativeInvoiceAmount}
 		where {!InvoiceObj.BillingProfileIdObj.Id} = {!ActivityObj.InvoiceIdObj.BillingProfileIdObj.Id} AND
 		{!InvoiceObj.CumulativeInvoiceProductTypeObj.Id} = {!ActivityObj.InvoiceIdObj.CumulativeInvoiceProductTypeObj.Id} AND
 		{!InvoiceObj.CumulativeCycleYear} = NVL(EXTRACT(YEAR FROM {!ActivityObj.InvoiceIdObj.ActivityEndDate}), 0) AND
 		{!InvoiceObj.CumulativeCycleMonth} = (NVL(EXTRACT(MONTH FROM {!ActivityObj.InvoiceIdObj.ActivityEndDate}), 0) -1)) * -1
ELSE 0
END

The formula above which has its context on the Activity object since it is related to invoice details, will leverage data from an invoice other than that one where the current activity/charge is a part of.

While this allows for flexibility, care must be taken to make sure that the independent select statement returns the expected data structure (e.g. one value instead of a value set). Enclose the select statement with a function that converts the data into the expected format of the formula such as NVL() or TO_CHAR() if needed.

Mathematical Operators

Operator Definition
+ Addition
- Subtraction
/ Division 
* Multiplication 

 

Logical Operators

Operator Definition

NOT NULL

True if value is not null .

NULL

True if value is null.

EQUALS

Logical equivalence comparison, true if both values are equal.

STARTS OR ENDS WITH

True if expression begins or ends with the specified value.

NOT EQUALS

True if expression does not equal specified value.

GREATER THAN

True if expression is greater than specified value. 

LESS THAN

True if expression is less than specified value. 

DOES NOT START OR END WITH

True if expression does not begin or end with the specified value.

NOT IN

True if value is not present in query.

IN

True if value is present in query. 

STARTS WITH

True if expression begins with specified value.

EQUALS NO QUOTES

 Logical equivalence comparison, true if both values are equal. No quotes needed.

NOT EQUALS NO QUOTES

True if expression does not equal specified value. No quotes needed.

JOIN

Used to combine two or more rows from table.

IN LISTAGG

True if expression is contained within the specified aggregated data.

CONTAINS

True if a string is part of a column's value.

Functions

Function Name Function Type Function Description
abs Mathematic ABS function returns the absolute value of a number.
add_months DateTime ADD_MONTHS function returns a date with a specified number of months added.
ceil Mathematic CEIL function returns the smallest integer value that is greater than or equal to a number.
concat Text CONCAT function allows you to concatenate two strings together.
decode Text DECODE function has the functionality of an IF-THEN-ELSE statement.
floor Mathematic FLOOR function returns the largest integer value that is equal to or less than a number.
GetFirstDayLastYear DateTime Get first day of last year Example: {!GetFirstDayLastYear}
GetFirstDayNextYear DateTime Get first day of next year Example: {!GetFirstDayNextYear}
GetFirstDayYear DateTime Get first day of current year Example: {!GetFirstDayYear}
GetFirstMonthDay DateTime Get first day of current month. Example: {!GetFirstMonthDay}
GetLastMonthDay DateTime Get last day of current month. Example: {!GetLastMonthDay}
GetSysDate DateTime Get current date & time. Example: {!GetSysDate}
GetSysTimeStamp DateTime Get current date & time with time zone. Example: {!GetSysTimeStamp}
greatest Mathematic GREATEST function returns the greatest value in a list of expressions.
initcap Text INITCAP function sets the first character in each word to uppercase and the rest to lowercase.
instr Text INSTR function returns the location of a substring in a string.
isNew Text Check what event is create new record
last_day   LAST_DAY function returns the last day of the month based on a date value.
LastRunTime DateTime Returns Time of Last Run Scheduler Job
least Mathematic LEAST function returns the smallest value in a list of expressions.
length Text LENGTH function returns the length of the specified string.
lower Text LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.
lpad Text LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null).
ltrim Text LTRIM function removes all specified characters from the left-hand side of a string.
months_between DateTime MONTHS_BETWEEN function returns the number of months between date1 and date2.
MyAccountId UserInfo Get account id for current user. Example: {!MyAccountId}
myNodeKey UserInfo Return current node key value. For use {!myNodeKey}
myParentNodeKey UserInfo Return current parent node key value. For use {!myParentNodeKey}
MyServicePointId UserInfo Get Service Point Id for current user. Example: {!MyServicePointId}
nvl Text NVL function lets you substitute a value when a null value is encountered.
OldValue Text Return old value for field.
power Mathematic POWER function returns m raised to the nth power.
regexp_instr Text REGEXP_INSTR function is an extension of the INSTR function. It returns the location of a regular expression pattern in a string. This function, introduced in Oracle 11g, will allow you to find a substring in a string using regular expression pattern matching.
regexp_replace Text REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. 
regexp_substr Text REGEXP_SUBSTR function is an extension of the SUBSTR function. This function, introduced in Oracle 11g, will allow you to extract a substring from a string using regular expression pattern matching.
replace Text REPLACE function replaces a sequence of characters in a string with another set of characters.
round Mathematic ROUND function returns a number rounded to a certain number of decimal places.
rpad Text RPAD function pads the right-side of a string with a specific set of characters (when string1 is not null).
rtrim Text RTRIM function removes all specified characters from the right-hand side of a string.
sign Mathematic SIGN function returns a value indicating the sign of a number
substr Text SUBSTR functions allows you to extract a substring from a string
to_char Convert TO_CHAR function converts a number or date to a string
to_date Convert TO_DATE function converts a string to a date
to_number Convert TO_NUMBER function converts a string to a number.
to_timestamp_tz Convert TO_TIMESTAMP_TZ function converts a string to a timestamp with time zone.
translate Text TRANSLATE function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time
trim Text TRIM function removes all specified characters either from the beginning or the ending of a string.
trunc Mathematic This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.
Trunc_YYYY Convert Truncate incoming date value to year
Trunc_YYYY_MM Convert Truncate incoming date value to month
Trunc_YYYY_MM_DD Convert Truncate incoming date value to day
upper Text UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

 

Related

Have more questions? Submit a request

Comments

Powered by Zendesk