Try It Out: Define a Validation Rule for Close Date

For our next validation rule, let's ensure that Close Date has a value whenever the Status field is set to Closed - Filled or Closed - Not Approved.

The hardest part of this validation rule is defining the error condition formula. When defining a condition like this, it's sometimes easiest to think about it in logical terms first, and then translate that logic to the functions and operators that are provided in the formula editor. In this case, our error condition is true whenever:

1Close Date is Not Specified
2
3AND
4
5(Status is "Closed - Filled" OR
6           "Closed - Not Approved")

Let's start with the first piece: “Close Date is Not Specified.” To translate this into terms the formula editor understands, we'll need to use the ISBLANK() function again. As you might remember from defining the Days Open custom formula field, ISBLANK() takes a single field or expression and returns true if it doesn't contain a value. So, remembering that we have to use the internal field name of the Close Date field in our formula, Close Date is Not Specified translates to:

1ISBLANK( Close_Date__c )

Next, let's figure out how to translate “Status is 'Closed - Filled'.” To test for picklist values, we'll need to use another function: ISPICKVAL(). ISPICKVAL() takes a picklist field name and value, and returns true whenever that value is selected. So “Status is 'Closed - Filled'” translates to:

1ISPICKVAL( Status__c , "Closed - Filled")

Now we just have to combine these translations, which we can do using a mix of the && and || functions. Both functions evaluate an unlimited number of expressions, but && returns true if all of the expressions are true while || returns true if any of the expressions are true. For example:

1exp1 && exp2 && exp3

returns true when exp1, exp2, and exp3 are all true. Likewise,

1exp1 || exp2 || exp3

returns true when any one of exp1, exp2, or exp3 are true.

Put these functions all together with our other expression translations, and we get our completed error condition formula:

1ISBLANK(Close_Date__c) && 
2    (ISPICKVAL(Status__c , "Closed - Filled") || 
3     ISPICKVAL(Status__c , "Closed - Not Approved"))

Phew! Now we can quickly define our second validation rule using this formula:

1. From Setup, click Create | Objects.
2. Click Position.
3. In the Validation Rules related list, click New.
4. In the Rule Name text box, enter Close_Date_Rule.
5. Select the Active checkbox.
6. In the Description text box, enter Close Date must be specified when Status is set to 'Closed - Filled' or 'Closed - Not Approved.'
7. In the Error Condition Formula area, enter the following formula:
   1ISBLANK(Close_Date__c) && 
   2    (ISPICKVAL(Status__c , "Closed - Filled") || 
   3     ISPICKVAL(Status__c , "Closed - Not Approved"))

8. Click Check Syntax to make sure the format of the formula is correct.
9. In the Error Message text box, enter Close Date must be specified when Status is set to 'Closed.'
10. Next to the Error Location field, select the Field radio button, and then choose Close Date from the drop-down list.
11. Click Save.