How to auto-populate time_worked field for parent project task with time worked from child tasks

MBarrott
Mega Sage

Is there a way for the time_worked values of all child project tasks to be summed and auto-populate the time_worked field on the parent project task?

 

 

1 ACCEPTED SOLUTION

maheshkn
Mega Guru

Write an After Insert and Update Business Rule on the Project Task table with the condition 'Time Worked changes.

 

Use the below script:

 

  var totalTimeworked = 0;
    if (current.parent) {
        var taskTimeWorkedGa = new GlideAggregate("pm_project_task");
        taskTimeWorkedGa.addQuery("parent", current.parent);
        taskTimeWorkedGa.addAggregate("SUM", "time_worked");
        taskTimeWorkedGa.groupBy('parent');
        taskTimeWorkedGa.query();
        if (taskTimeWorkedGa.next()) {
            totalTimeworked = taskTimeWorkedGa.getAggregate("SUM", "time_worked");

        }

        var parentRec = current.parent.getRefRecord();
        parentRec.setValue('time_worked', totalTimeworked);
        parentRec.update();

    }
 
 
Result: Whenever the Time Worked field changes on child tasks, it will update the Time Worked field on the parent task.

 

View solution in original post

6 REPLIES 6

Jake Sadler
Kilo Sage

Hi @MBarrott ,

 

You could use an after business rule to do this.

 

Run it on the project task table and the conditions would be time worked changes.

Write a script like this:

var prjTasks = new GlideRecord("pm_project_task");

prjTasks.addQuery("parent", current.parent);

prjTasks.query();

 

var totalTimeWorked = new GlideDuration();

while(prjTasks.next()){

 

var prjTaskTimeWorked = new GlideDuration(prjTask.time_worked);

 

totalTimeWorked.add(prjTaskTimeWorked );

 

}

 

var parentProject = new GlideRecord("pm_project");

parentProject.get("sys_id",current.parent.sys_id.toString());

 

parentProject.time_worked = totalTimeWorked;

parentProject.update();

 

make sure to test this and modify any field names and table names to match your requirements

 

Thanks

 

Jake

Hi @Jake Sadler

 

Tried this script but not seeing time_worked field update. Only issue I'm seeing with he script is that the parentProject field is a GlideRecord for pm_project rather than pm_project_task (the parent is also a project task). Tried switching to pm_project_task but didn't see it update. 

Jake Sadler
Kilo Sage

Hi @MBarrott ,

 

If the time_worked field sits on the task table you can just use Task as the table. Otherwise, use a gliderecord to get the parent and use getTableName() to get the parent table.

 

Check the field name is time_worked and also check that it can be set in a background script

 

Hi @Jake Sadler

 

I've made some progress and it's writing to the parent project task, however the time is off. Entering 1 hour in the time_worked field of a single child task and closing it out produced only 3 seconds on the parent time_worked. 

 

Feel like it has to be a conversion issue, let me know if you see anything sticking out. 

 

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

	gs.log('entered business rule');
    // Check if the state has changed to 'Closed Complete'
    if (current.state == '3') 
	{
		gs.log('entered if');
        var totalTimeWorked = 0;

        // Query for child project tasks
        var childTasks = new GlideRecord('pm_project_task');
        childTasks.addQuery('parent', current.sys_id);
        childTasks.query();

        // Sum the time_worked field from all child tasks
        while (childTasks.next()) 
		{
            totalTimeWorked += parseFloat(childTasks.time_worked);
        }

        // Log the total time worked for debugging
        gs.log('Total Time Worked new: ' + totalTimeWorked);

        // Update the time_worked field of the current task
        current.time_worked.setDateNumericValue(totalTimeWorked);
        current.update();
    } 
	else 
	{
        gs.log('State did not change to Closed Complete');
    }
})(current, previous);