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

[MBarrott]

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?

[Jake Sadler]

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

[MBarrott]

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]

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

[MBarrott]

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);