Sync Change Request start & end date with Change Tasks planned start & planned end date

[georgimavrodiev]

Hello all,

I am trying to sync the start & end date of a Change Request record with the planned start & planned end date of the corresponding Change Tasks, related to it (the Change Request record).
My goal is the following:

- let's imagine that I have a Change Request which contains 5 Change Tasks. I would like the start_date of the Change Request to be the value of the earliest planned_start_date of the five Change Tasks, while the end_date of the Change Request to be the value of the latest planned_end_date of the five Change Tasks.
Saying this, if I log into one of the Change Tasks and change its planned_start_date to even an earlier one -> then, this change should appear in the start_date of the Change Request and like-versa if I change the planned_end_date of a Change Task to later date -> the later date should be updated into the end_date of the Change Request.

NOTE: in my instance I did not have Planned Start / End Date fields in the Change Task table, thus I created custom ones: u_planned_start_date & u_planned_end_date, while in the Change Request table I have start_date & end_date;

To achieve it, I created a Business Rule against Change Task (change_task) table, which runs after Insert & Update when Planned start date or Planned end date changes (these are the filter conditions). However, I am not able to find the right function which I need to use and thus my Business Rule uses 'get.NumericValue()'... I believe this is what breaks my code, but in all cases I will appreciate if somebody may check and confirm my code. It follows below in blue:

(function executeRule(current, previous /*null when async*/) {

// This Business Rule ensures that a Change Request will have the earliest Planned Start Date of all its Change Tasks as Start Date & the latest Planned End Date of all its Change Tasks;

var dct1 = new GlideDateTime(current.u_planned_start_date);

dct1 = dct1.getNumericValue();

var dct2 = new GlideDateTime(current.u_planned_end_date);

dct2 = dct2.getNumericValue();

var cr = current.change_request;

var ct = new GlideRecord('change_task');

ct.addQuery('change_request', cr);

ct.addQuery('state', 'active');

ct.query();

var tempstart = dct1;

var tempend = dct2;

while(ct.next()){

var start = new GlideDateTime(ct.getDisplayValue('u_planned_start_date'));

start = start.getNumericValue();

var end = new GlideDateTime(ct.getDisplayValue('u_planned_end_date'));

end = end.getNumericValue();

if(tempstart <= start){

tempstart = tempstart;

}

else {

tempstart = start;

}

if(tempend >= end){

tempend = tempend;

}

else {

tempend = end;

}

}

var nov = new GlideRecord('change_request');

nov.addQuery('change_request', current.change_request);

nov.query();

while(nov.next()){

nov.start_date = tempstart;

nov.end_date = tempend;

nov.update();

}

})(current, previous);

Thank you in advance!

Cheers, Georgi

[varads_kulkarni · Accepted solution]

Hi Georgi,

What is the data type of the custom field that you have created on change_task form? If it is date/time field you need not get the numeric value and all. You can directly copy the date/time values like change_request.start_date = current.u_planned_start_date.

A sample query for planned start date would be

var cr = new GlideRecord('change_request');

cr.addQuery('sys_id',current.getvalue('change_request');

cr.query();

{

if(current.u_planned_start_date <= cr.start_date)

cr.start_date = current.u_planned_start_date;

cr.update();

}

In case if you have created the custom fields of only date field, then while assigning the value, you will have to do something like

cr.start_date = current.u_planned_start_date + "23:00:00";

cr.update();

I suggest you to have date/time fields on both the forms as that would ease your entire trouble.

Best Regards,

Varad

[georgimavrodiev]

Hi Varad,

I configured the custom fields (u_planned_start_date & u_planned_end_date) of the change_task table to be the same type as the default ones (start_date & end_date) of the change_request table: Date/Time.

About your example, above, I would like to ask the following:
- would it be able to query all the Change Tasks of the Change Request record and populate the start_date with the earliest u_planned_start_date every time a change is made?
- I guess I simply may add a second if statement after yours for the end_date and u_planned_end_date by using the same logic?

Regards, Georgi

[varads_kulkarni]

Hi Georgi,

Yes you can do that for all the change_tasks related to the change request. In that case your script might look like

var g1 = 1;

var g2 = 1;

var cr = new GlideRecord('change_request');

cr.addQuery('sys_id',current.getvalue('change_request');

cr.query();

if(cr.next())

{

var ct = new GlideRecord('change_task');

ct.addQuery('change_request', current.change_request;);

ct.addQuery('state', 'active');

ct.query();

while(ct.next())

{

if(ct.sys_id != current.sys_id)

{

if(current.u_planned_start_date <= cr.start_date)

cr.start_date = current.u_planned_start_date;

cr.update

ct.u_planned_start_date = current.u_planned_start_date;

ct.update();

}

else

g1 = 1;

}

}

if(g1 == 1 || g2 == 1)

abort;

Please make sure that in the else part of both the if's set two variables to 1 and 2. and after the entire loop like shown above

Best Regards,

Varad

[varads_kulkarni]

Hi Georgi,

In the else of the both part, you need to abort, else it will take new value in the task.

Best Regards,

Varad