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 return TRUE if it does not and return FALSE if it contains a value. You can use this function in the formula field, validation rule, and workflow.

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

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

When using this function with a numeric field (currency, number, and percent field type) in a formula field, select Treat blank fields as blanks in the Blank Field Handling section. Otherwise, if you select Treat blank fields as zeroes, it will give the blank field value with zero, so none of them will be null. 

ISBLANK() works with the following field 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, for example, LEN(Ini_Long_Text_Area__c) = 0, but not for formula field.

** Long Text Area and Rich Text Area fields are not supported in the Formula field.

You also can use BLANKVALUE(expression, substitute_expression) function to determine if an expression has a value, it will return 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)
You should 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(expressionsubstitute_expression) function, it is similar to BLANKVALUE(), with the 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 field.

No comments:

Post a Comment

Page-level ad