Blank and null handling in formula fields
Key Takeaway
Use BLANKVALUE to return a substitute value for a blank field. Use ISBLANK when you only need to check whether a field has a value. To detect truly blank number, currency, or percent fields, choose "Treat blank fields as blanks".
Formula fields often reference fields that might be blank, and a blank field can make a formula return the wrong result or fail. Salesforce gives you functions that test for a blank value (ISBLANK, ISNULL) and functions that swap in a substitute value (BLANKVALUE, NULLVALUE). The Blank Field Handling setting also decides whether blank number, currency, and percent fields count as blank or as zero.
Checking for a blank value or replacing it
Pick the function by your goal.
| Function | What it returns | Use it when |
|---|---|---|
ISBLANK(expression) | TRUE when the expression has no value, and FALSE when it has one | You only need to check whether the field has a value |
BLANKVALUE(expression, substitute_expression) | The expression's value if it has one, or the substitute if it doesn't | You want a substitute value returned for a blank field |
ISBLANK answers a yes/no question, so it usually sits inside IF.
IF(ISBLANK(Amount), 0, 1)This formula returns 0 when Amount is blank and 1 when Amount has a value.
BLANKVALUE returns a value, not a yes/no answer.
BLANKVALUE(Payment_Due_Date__c, StartDate + 5)This formula returns the value of Payment_Due_Date__c if it has one. When Payment_Due_Date__c is blank, the formula falls back to five days after the start date.