- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2024 07:08 AM
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-21-2024 03:08 AM
Write an After Insert and Update Business Rule on the Project Task table with the condition 'Time Worked changes.
Use the below script:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2024 07:19 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2024 08:19 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2024 09:12 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
07-19-2024 01:38 PM
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);