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 if it contains a value, this function returns FALSE. 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) for 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's 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 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.
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 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 fields.

No comments:

Post a Comment

Page-level ad