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 ,

Have a nice evening!

Below is code that updates a date field in the parent record based on dates from related child records. It finds the furthest date from child records (excluding "canceled" ones) and sets it as the value in the parent record's date field. The script ensures the parent's date field reflects the furthest date found among its children, excluding canceled child records.

var childTable = 'child_table_name'; // Replace 'child_table_name' with the actual child table name
var parentField = 'parent_field_name'; // Replace 'parent_field_name' with the actual reference field on the child table pointing to the parent table
var childDateField = 'date_field'; // Replace 'date_field' with the actual date field on the child table

// Check if the current record is in the canceled state
if (current.state === 'canceled') {
    return;
}

// Query the child records related to the parent record
var grChild = new GlideRecord(childTable);
grChild.addQuery(parentField, current.parent.sys_id);
grChild.query();

var furthestDate = current.parent.date_field; // Initialize the furthest date with the parent's current date field value

while (grChild.next()) {
    // Check if the child record is not in the canceled state and has a date greater than the current furthest date
    if (grChild.state !== 'canceled' && grChild.date_field > furthestDate) {
        furthestDate = grChild.date_field;
    }
}

// Update the parent record with the furthest date
current.parent.date_field = furthestDate;
current.parent.update();

You utilize the above code in server-side scripts.

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 ,

Thank you for your reply, the date field on the parent table is newly created field and doesn't contain any value. The child records contains the date field value and i need to set the parent's newly created date field with furthest date by querying the child records(by excluding cancelled ones)  . I will be showing the parent date field value only in the list view of Parent. Please let me know if there are any changes required with the above script. Thanks!

Regards,

Sagar

[Community Alums]

Hi @Sagar11 ,

Please proceed with the above code. No changes are needed!

Kindly replace the child table name, parent table, and date field in the parent and child table in the script.

Let me know:)

regards,

Prasad

[Sagar11]

Hi @Community Alums ,

I am using the script in after insert/update BR on the child table, it is not working, please let me know if am missing anything here in the below code.

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

var childTable = 'u_remediation_action'; 
var parentField = 'parent'; 
var childDateField = 'u_due_date'; 

// Check if the current record is in the canceled state
if (current.state == '7') {
return;
}

// Query the child records related to the parent record
var grChild = new GlideRecord(childTable);
grChild.addQuery(parentField, current.parent.sys_id);
grChild.query();

var furthestDate = current.parent.childDateField ; // Initialize the furthest date with the parent's current date field value

while (grChild.next()) {
// Check if the child record is not in the canceled state and has a date greater than the current furthest date
if (grChild.state != '7' && grChild.childDateField > furthestDate) {
furthestDate = grChild.childDateField;
}
}

// Update the parent record with the furthest date
current.parent.u_remediation_due_date = furthestDate;
current.parent.update();

})(current, previous);