ISBLANK
|
ISNULL
|
ISBLANK determines if an expression has a value then returns TRUE if it does not. If an expression contains a value, then this function returns FALSE.
|
ISNULL determines if an expression is null (blank) then returns TRUE if it is. If the expression contains a value, then this function returns FALSE.
|
Syntax: ISBLANK(expression)
|
Syntax: ISNULL(expression)
|
It checks whether an expression is blank and returns TRUE or FALSE
|
It checks whether an expression is null and returns TRUE or FALSE
|
ISBLANK supports text fields.
|
Text fields are never null, so using ISNULL function with a text field always returns false result.
|
For Numeric fields, ISBLANK function only returns TRUE if the field has no value and not configured to treat blank fields as 0s.
|
ISNULL supports Numeric fields.
|
Note: Instead of using ISNULL, use ISBLANK in new formulas. Both functions have same functionality, but ISBLANK supports text fields. Salesforce will continue to support ISNULL, so you do not need to worry and change any existing formulas.
The below tests does not have any value present in both text and number field including whitespace.
S NO
|
Formula function
|
Formula option
|
Field type
|
Result
|
1
|
ISBLANK()
|
Treat blank fields as zeroes
|
Text
|
True
|
2
|
ISBLANK()
|
Treat blank fields as blanks
|
Text
|
True
|
3
|
ISBLANK()
|
Treat blank fields as zeroes
|
Number
|
False
|
4
|
ISBLANK()
|
Treat blank fields as blanks
|
Number
|
True
|
5
|
ISNULL()
|
Treat blank fields as zeroes
|
Text
|
False
|
6
|
ISNULL()
|
Treat blank fields as blanks
|
Text
|
False
|
7
|
ISNULL()
|
Treat blank fields as zeroes
|
Number
|
False
|
8
|
ISNULL()
|
Treat blank fields as blanks
|
Number
|
True
|
From the output, it's clear that both the formula functions are not same and they behave differently for different field types.
ISBLANK:
Description: Determines if an expression has a value and returns TRUE if it does not. If ISBLANK contains a value, then this function returns FALSE.
Use: ISBLANK(expression) and replace the expression with the expression you want to evaluate.
Example:
(IF(ISBLANK(Maint_Amount__c), 0, 1) +
IF(ISBLANK(Services_Amount__c), 0,1) +
IF(ISBLANK(Discount_Percent__c), 0, 1) +
IF(ISBLANK(Amount), 0, 1) +
IF(ISBLANK(Timeline__c), 0, 1)) / 5
This formula takes a group of fields and calculates what percent of them are being used by your personnel. Above formula field checks five fields to see if they are blank or not. If so, a zero is counted for that field. A "1" is counted for any field that contains a value and this total is divided by five (the number of fields evaluated). Keep in mind that above formula requires you select the Treat blank fields as blanks option under Blank Field Handling while the Advanced Formula subtab is showing.
Below are the Tips for ISBLANK:
- Instead of using ISNULL, use ISBLANK in new formulas. Both functions have same functionality, but ISBLANK supports text fields. Salesforce will continue to support ISNULL, so you do not need to worry and change any existing formulas.
- A field contains character the its not empty, blank space, or zero. For example, a field that contains a space inserted with the spacebar is not an empty field.
- If the field does not have a value, then use the BLANKVALUE function to return a specified string ; use the ISBLANK () function if you only want to check if the field has a value or not.
- If you use ISBLANK () function with a numeric field, the function only returns TRUE if the field has no value. ISBLANK () function is not configured to treat blank fields as zeroes.
- If you want to use this function with a picklist datatype, then use ISBLANK(TEXT(<picklist>)) to convert the picklist items into a text value.
Description: ISNULL determines if an expression is null (blank) then returns TRUE if it is. If the expression contains a value, then this function returns FALSE.
Use: ISNULL(expression) and replace the expression with the expression you want to evaluate.
Example:
(IF(ISNULL(Maint_Amount__c), 0, 1) +
IF(ISNULL(Services_Amount__c), 0,1) +
IF(ISNULL(Discount_Percent__c), 0, 1) +
IF(ISNULL(Amount), 0, 1) +
IF(ISNULL(Timeline__c), 0, 1)) / 5
Above formula takes a group of fields and calculates what percent of them are being used by your personnel. Above formula field checks 5 fields to see if they are blank. If so, a 0 is counted for that field. 1 is counted for any field that contains a value and the total is divided by 5 (as we are evaluating 5 fields, i.e. the number of fields evaluated). Note that above formula requires you select the Treat blank fields as blanks option under Blank Field Handling while the Advanced Formula subtab is showing.
Validation Rule Example:
AND(ISPICKVAL(StageName, "Closed Won"), ISNULL(Project_Start_Date__c))
This validation rule makes the Project Start Date custom date field conditionally required whenever the stage is "Closed Won"
Below are the Tips for ISNULL:
- Text fields are never null, so using ISNULL () function with a text field always returns false. For example, the formula field IF(ISNULL(new__c) 1, 0) is always 0 regardless of the value in the New field. For text fields, you must use the ISBLANK function instead of ISNULL.
- Multi select picklist fields are never null in s-controls which are deprecated, buttons, and email templates, so using ISNULL function with a multi select picklist field in those contexts always returns false.
- Date & date/time fields which are Empty, then this function always return true when referenced in ISNULL functions.
- Do not use ISNULL function for date/time fields.
- Choose Treat blank fields as blanks for your formula when referencing a number field, percent field, or currency field in an ISNULL () function. Choosing Treat blank fields as 0s gives blank fields the value of 0 so none of them will be null.
- Merge fields can be handled as blanks, which can affect the results of components like s-controls ( which are deprecated ) because they can call ISNULL function.
Enjoy! If you have any questions, comments etc. please feel free to let me know. As always, please feel free to get in touch me as I would be more than happy to assist you with any of your Salesforce development needs.
No comments:
Post a Comment