How to set parent date field with child's farthest date field

[Sagar11]

Hi,

If there is three or more tasks in the child table having three different dates for three tasks, in that two are in (in progress, pending or completed) state and one is in cancelled state. I need to set the furthest date of the child date field to the parent table which is having date field( cancelled state date should not be considered even if it is higher date).

[Community Alums]

Hi @Sagar11 ,

In the below line, please replace childDateField with the "u_remediation_due_date", as we are comparing 'furthestDate' variable with the childDate. 

var furthestDate = current.parent.childDateField ;

with this -->

var furthestDate = current.parent.u_remediation_due_date;

Sagar, I would suggest validating the above in the background script for one record from the 'parent' table. As I don't have your custom tables in my PDI, I cannot try this scenario. Let me know, if you need help this in testing in "Scripts - Background".

regards,

Prasad

[Sagar11]

Hi @Community Alums ,

The code didn't worked after making the above change. I tried to test for one record in "scripts - Background",  below is the code I'm trying to test in scripts-background but not got the expected result.

var childTable = 'u_remediation_action';
var parentField = 'parent';
var childDateField = 'u_due_date';
if (current.state == '7') {
return;
}
var grChild = new GlideRecord(childTable);
grChild.addQuery(parentField, 'f00871b41b8529507890ca292a4bcba4');
grChild.query();
var furthestDate = current.parent.u_remediation_due_date;
while (grChild.next()) {
if (grChild.state != '7' && grChild.childDateField > furthestDate) {
furthestDate = grChild.childDateField;
}
}
current.parent.u_remediation_due_date = furthestDate;
current.parent.update();

[Community Alums]

Hello @Sagar11 ,

Please use the below code:

#Can be used in Business rule with minimal change:

var childTable = 'u_remediation_action';
var parentField = 'parent';
var furthestDateFormated = new GlideDateTime('1900-01-01 00:00:00'); // Set an initial date far in the past

var grChild = new GlideRecord(childTable);
grChild.addEncodedQuery('parent=' + current.sys_id + '^state!=7');
grChild.query();

while (grChild.next()) {
    var childDate = new GlideDateTime(grChild.u_leave_end_date);

    if (childDate.compareTo(furthestDateFormated) > 0) {
        furthestDateFormated = childDate; // Update the furthestDateFormated with the new childDate
    }
}

var finalFarthestDate = furthestDateFormated.toString().split(' ')[0];
current.u_remediation_due_date = furthestDateFormated;
gs.info("Farthest Date updated: " + finalFarthestDate);
current.update();

# Can be used in Script - Background: 

var childTable = 'u_remediation_action';
var parentField = 'parent';
var furthestDateFormated = new GlideDateTime('1900-01-01 00:00:00'); // Set an initial date far in the past

var grChild = new GlideRecord(childTable);
grChild.addEncodedQuery('parent=<<replace sys_id of parent>>^state!=7');
grChild.query();

while (grChild.next()) {
    var childDate = new GlideDateTime(grChild.u_leave_end_date);

    if (childDate.compareTo(furthestDateFormated) > 0) {
        furthestDateFormated = childDate; // Update the furthestDateFormated with the new childDate
    }
}

gs.addInfoMessage("Farthest Date: " + furthestDateFormated);
var datePart = furthestDateFormated.toString().split(' ')[0];

gs.addInfoMessage("Final Furthest Date: " + datePart);

If this helped you in any way, please hit the like button/mark it helpful. Also, don't forget to accept it as a solution. So it will help others to get the correct solution.

thanks,

Prasad

[Sagar11]

Hi @Community Alums ,

I took a reference of the below link and got the expected result. thank you for your guidance. 

Solved: Re: Update Parent Task End Date with Child Task En... - Page 2 - ServiceNow Community

Regards,

Sagar