In this Salesforce tutorial, we will learn about the Salesforce NULLVALUE function with its use cases. The NULLVALUE function is executable in the formulas of the both Salesforce Lightning and Classic editions.
What is NULLVALUE() function in Salesforce
In Salesforce, the NULLVALUE() function is used to handle null values in formula expressions. It allows us to specify a default value that should be used in place of a null value.
The NULLVALUE() function is useful in cases where we have to handle potential null values in a formula field or expression. It helps prevent errors that may occur when working with null values, allowing you to provide a default value or a substitute value in such cases.
Syntax for the NULLVALUE() function
The syntax of the NULLVALUE function is as follows.
NULLVALUE (expression, substitute expression)
In the above syntax replace the expression with the expression you want to evaluate; replace substitute_expression with the value you want to replace any blank values. This substitute expression can be a blank string ” “.
- The NULLVALUE function should be avoided in determining the null values in Text fields because they are never null when they are blank.
- This function should be used with the Number, Date, Picklist, and Currency fields.
- Choose the feature to treat the blank field as blank because if you refer blank field as zero for the field number, percent, or currency field in a NULLVALUE function, they will get zero as value so none of them will be null.
- In the expression and substitute expression use the same data type.
Use the NULLVALUE function in Salesforce Lightning
In the below example, we will create a validation rule using the NULLVALUE function in Salesforce Lightning.
We’ll create a validation rule for the case object to ensure that, If the due date is within the next 10 days or if there is no due date specified (null), the formula will return true, indicating that the case is not resolved. Otherwise, it will return false.
To create a validation rule using the NULLVALUE function in Salesforce Lightning, follow the below steps.
1. Navigate to the Setup page of Salesforce Lightning and for that click on the Settings icon that is on the upper right side of the screen then in the dropdown select the Setup option.
2. In the Setup of Lighting, click on the Object Manager tab.
3. In the Object Manager window, we can view all the standard and custom objects. Here, select the Cases object.
4. In the setup window of the Case object, go to the left sidebar and click on the Validation rule then in the next window click on the New button.
5. In this window, we have to define the validation and foe that first enters the label for the Validation Rule in the Rule Name field.
6. To define the error condition for the validation rule enter the below formula in the formula editor.
NULLVALUE( Due_Date__c , Start_Date__c + 10) <= DATE(2023,12,30)
The condition that we have applied in the above formula is as follows.
- The function NULLVALUE is used to handle cases where a field might be null. It returns a specified value if the target field is null.
- Due_Date__c is a custom date field representing the due date for the Case record.
- Start_Date__c + 10 is a substitute expression that will add 10 days to the start date if the due date and take it as a due date.
- <= DATE(2023,12,30) comparison checks if the result of the NULLVALUE function (either the actual due date or Start_Date__c + 10) is less than or equal to the date, December 30, 2023.
After entering the formula click on the button Check Syntax for validation and check errors.
7. In the Error Message field, enter the message that will appear on the screen when the condition defined in the formula is True. At last click on the Save button.
Now I will open a Case record and leave the field Due Date as null and if the substitute expression of this field is less than the 30th of December then the error condition for the validation rule will be true.
As we can that in the above image when the field Due Date is left blank (null), and the substitute expression Start Date is 15 of December and according to the formula condition, adding 10 days will be 25 which is less than 30 December hence the error condition became true.
In this way, we have successfully executed the NULLVALUE function in Salesforce by creating a validation rule.
Use NULLVALUE function in Salesforce Classic
In this example, we will create a validation rule for the Product object using the NULLVALUE function in Salesforce Classic, the validation rule will make sure that the product dispatch quantity is more than 25 if the field is null and the substitute field is also giving number less than 25 the formula condition will be true and an error message will appear on the screen.
To create a validation rule in Salesforce Classic follow the below steps.
1. Navigate to the Setup page of Salesforce Classis, click on the Setup tab click on the Setup tab that is on the upper left side of the window.
2. In the setup of Salesforce Classic, go to the Build section on the left sidebar, and in the Customize dropdown select the Product object. Then select the Validation Rule in the object dropdown.
3. In this window click on the New button to create a validation rule.
4. In this window, enter the name for the validation rule in the Rule Name field. In this example, I have entered the name as Product quantity validation.
5. In the section Error Condition Formula enter the below formula in the formula editor.
NULLVALUE(Expected_dispatch_quantity__c , Total_quantity__c + 10) <= 25
We have defined the above formula in the following way.
- NULLVALUE is used to check if the expression is null, if it is null then it takes substitute expression as value.
- NULLVALUE( Expected_dispatch_quantity__c , Total_quantity__c + 10) function returns the value of the first parameter if it is not null, else it returns the value of the second parameter.
- If Expected_dispatch_quantity__c is null, the formula uses the value of Total_quantity__c + 10 as a substitute.
- <= 25 checks if the result obtained from the NULLVALUE function is less than or equal to 25.
6. After entering the formula, click on the button Check Syntax to validate the formula.
7. In the field Error Message, enter the message that will appear on the screen when the error condition is true. After entering the error message click on the Save button.
Now to check the validation rule, open the Product record and leave the field Expected dispatch quantity as null (blank).
In the above image, we can see that when the field Expected dispatch quantity is left null then according to the formula the null value is substituted by Total quantity + 10, Since the total quantity was 10 so the substitute value returns 20, which is less than 25.
In this case, the error condition for the validation rule returns true and an error message is displayed on the screen.
In this way, we have successfully executed the NULLVALUE function in Salesforce Classic by creating a validation rule for the Products object.
In this Salesforce tutorial, we have learned about the NULLVALUE function with its use cases in Salesforce. In the above examples, we have successfully created the validation rule in Salesforce Lightning and Salesforce Classic with the help of the NULLVALUE function.
Along with this, we have also learned about the syntax and the considerations that we should be aware of while using the NULLVALUE function in Salesforce.
You may like to read:
- Salesforce MILLISECOND Function
- Salesforce ISNULL Function
- Salesforce FormatDuration Function
- Salesforce ISOYEAR Function
- OR Function in Salesforce
- CASE Function in Salesforce
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.