2 Replies Latest reply on Oct 16, 2017 2:22 PM by MarkLarvo

    Validation Rule - Evaluating a Null field? - Change - Risk Level

    MarkLarvo Specialist

      Looking for some pointers on a validation rule and the evaluation of Null fields.


      Our Change Control Request (CCR) has a CABReviewDate that is only required for Medium and High risk changes. When required that date must be greater than today. This check that the CABReviewDate is greater than today should only occur when the CCR is in the Logged status. (We check this before Submission. After submission the date would be older than the current date as it works it way through the process.)


      Here is the validation rule:


      $(Status == "Logged" && RiskLevel in ("Medium",  "High") &&   CABReviewDate >= CurrentDate()

      || Status == "Logged" && RiskLevel not in ("Medium",  "High")

      || Status != "Logged")


      1. This rule fails if the Risk Level has not been calculated, as in the sql field states "null". (Risk Level is not required for a save in Logged status.)
      2. The rule works with the "Null" Risk Level on the first Save (creation).
      3. After the first save any edits cannot be saved until the Risk Level questions are answered which populates the Risk Level field (Low, Medium, High)


      I tried adding:

      1. || Status == "Logged" && RiskLevel == null
      2. || Status == "Logged" && IsNull(RiskLevel)


      and several other iterations which I cannot recall due to the late hour.


      Any special tricks I'm missing? Most appreciative for any advice! Thanks - Mark.

        • 1. Re: Validation Rule - Evaluating a Null field? - Change - Risk Level
          AlasdairRobertson ITSMMVPGroup

          The null test are as follows:


          if field in (null,"")  - will test if a field is null or blank.


          Another option is to set a value for the field if null nvl(field,"replacement value")


          When designing fields a default value for example unknown or n/a can help which will then be treated like text.  We see this a lot with check boxes which are null by default and ideally should be initialised with a value.  I understand Risk Level is a system field and the above functions should help.  Let me know if not and I will have a play.

          • 2. Re: Validation Rule - Evaluating a Null field? - Change - Risk Level
            MarkLarvo Specialist

            Thank you Alasdair!


            You gave me enough to go on to keep trying. In the end I started testing each expression before putting them together. The trick seemed to be to have the null evaluation first, of course I may not have had the syntax exactly spot on either!?!


            Here is our final Validation Rule:


            $(Status == "Logged" && IsNull(RiskLevel) ||

            Status != "Logged" ||

            Status == "Logged" && RiskLevel not in ("Medium",  "High") ||

            Status == "Logged" && RiskLevel in ("Medium",  "High") &&   CABReviewDate >= CurrentDate())


            We are still testing but this order appears to let everything through that we want. Again, thanks for your help!