Pages

Monday, August 5, 2013

Salesforce: ISBLANK() or ISNULL()

To determine if an expression has a value or not, you can use ISBLANK() function in Salesforce. It will  returns TRUE if it does not and if it contains a value, this function returns FALSE. You can use this function in formula field, validation rule, and workflow.

Syntax: ISBLANK(expression) -- replace expression with the expression you want evaluated, sample: IF(ISBLANK(Main_Amount__c), 0, 1)

A field is not considered as empty if it contains a character, blank space, or zero. For example, a field that contains a space inserted with the spacebar is not empty.

When using this function with a numeric field (currency, number, and percent field type) for a formula field, choose Treat blank fields as blanks in the Blank Field Handling section. Otherwise, if you choose Treat blank fields as zeroes, it will gives the blank field value with zero, so none of them will be null. While it will work in validation rule and workflow without exception as in Formula field.


ISBLANK() work with following fields type: Text, Email, Text Area, Picklist (Multi-Select)NumberCurrency, and Percent.
But, it does not work directly with fields type: Picklist, Long Text Area, and Rich Text Area.

For Picklist field, we can use TEXT() function before ISBLANK(), example: ISBLANK( TEXT(Ini_Picklist__c) ) or ISPICKVAL(Ini_Picklist__c, "").

For Long Text Area and Rich Text Area field, instead of using ISBLANK(), use LEN() function, example: LEN(Ini_Long_Text_Area__c) = 0, but not for formula field.

** Long Text Area and Rich Text Area field is not supported in Formula field.


You also can use BLANKVALUE() function to determine if an expression has a value and returns a substitute expression if it does not. If the expression has a value, returns the value of the expression.
sample:
BLANKVALUE(Payment_Due_Date__c, StartDate +5)
Use the same data type for both the expression and substitute_expression.

How about ISNULL() ?
Use ISBLANK instead of ISNULL in new formulas. ISBLANK has the same functionality as ISNULL, but also supports text fields. Salesforce will continue to support ISNULL, so you do not need to change any existing formulas.

The same goes for NULLVALUE(), it is similar with BLANKVALUE(), with exception:
  • Avoid using NULLVALUE with text fields because they are never null even when they are blank. Instead, use the BLANKVALUE function to determine if a text field is blank.
  • Don’t use NULLVALUE for date/time fields.