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

    CHANGE - Calculate CAB review date from Change Start Date & Time

    MarkLarvo Apprentice

      Our CAB Review meeting is on Tuesdays at 3 pm CT prior to the Start Date of a Change.

       

      Has anyone figured out logic to calculate their CAB Review Date?

       

      I found script on the internet for the way to do it in ServiceNow which uses variables and case statements. I am not yet familiar enough to know if SM has this flexibility. Any insight into functions I should look at would be appreciated.

       

       

      SERVICENOW script

      https://community.servicenow.com/thread/244975

        • 1. Re: CHANGE - Calculate CAB review date from Change Start Date & Time
          MarkLarvo Apprentice

          So I was able to come up with a Business Rule to calculate everything we want except if the change is for the Tuesday when we have the CAB meeting. Code share below:

           

          What I wanted was any submission for a Change that starts prior to 3 pm on a Tuesday could be reviewed in that days meeting. If the Change was to start on or after 3 pm on a Tuesday then it would have to be reviewed in the next weeks meeting. (Of course this would be accompanied with a rule that wouldn't allow a CAB review date to be less than the current date.)

           

          WORKING - The negative number set the time to 3 PM

          $(if LocalDayOfWeek(ScheduledStartDate) == "1"  then AddHours(-105,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "2" then AddHours(-129,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "3" then AddHours(-153,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "4" then AddHours(-9,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "5" then AddHours(-33,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "6" then AddHours(-57,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "7" then AddHours(-81, BeginningOfDay(0,  ScheduledStartDate))

          else NULL)

           

          NOT WORKING and what we really wanted was to keep it on the current day at 3 pm if before 3 pm and push out a week if on or after 3 pm. I got warnings on the operators below and it did not work. :-(

          else if LocalDayOfWeek(ScheduledStartDate) == "3" && LocalHour(ScheduledStartDate) < AddHours(15,  BeginningOfDay(0,  ScheduledStartDate)) then AddHours(15,  BeginningOfDay(0,  ScheduledStartDate))

          else if LocalDayOfWeek(ScheduledStartDate) == "3" && LocalHour(ScheduledStartDate) >= AddHours(15,  BeginningOfDay(0,  ScheduledStartDate)) then AddHours(-153,  BeginningOfDay(0,  ScheduledStartDate))

           

          Any help appreciated. Thanks - Mark.

          • 2. Re: CHANGE - Calculate CAB review date from Change Start Date & Time
            MarkLarvo Apprentice

            Here is the final Business Rule for CHANGE.CABReviewDate [Notes in square brackets]

             

            Our meeting is at 3 pm on Tuesday. Any Change scheduled for a Tuesday and after 3:59 pm can be reviewed on that Tuesday. Anything earlier must be scheduled for the week prior.

             

            $(if LocalDayOfWeek(ScheduledStartDate) == "1" [If the ScheduledStartDate Day is a Sunday, then subtract 105 hours from 12 am]

                 then AddHours(-105,  BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "2"

                 then AddHours(-129,  BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "3" && LocalHour(ScheduledStartDate) > "15" [If the ScheduledStartDate is a Tuesday AND the hour of the scheduled time is greater than 3 pm, then add 15 hours to 12 am]

                 then AddHours(15,  BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "3" && LocalHour(ScheduledStartDate) <= "15"

                 then AddHours(-153,  BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "4"

                 then AddHours(-9,  BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "5"

                 then AddHours(-33, BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "6"

                 then AddHours(-57, BeginningOfDay(0,  ScheduledStartDate))

            else if LocalDayOfWeek(ScheduledStartDate) == "7"

                 then AddHours(-81, BeginningOfDay(0,  ScheduledStartDate))

            else null)

             

            This works in concert with a Before Save rule to make sure the CABReviewDate is greater than or equal to today when the CHANGE.Status = "Logged".

             

            Hope this can help someone.