Calculate total hours and populate related field

[Xavier_Wharton]

Hello, 

I am trying to get the sum of hours entered in the Hours Worked column to populate in the Total Hours field. 

Click the link below to review the video description of the problem.
https://youtu.be/VD1HONCffPo

This is the code that I added to the Calculated Field > Script within the Total Hours dictionary entry. 

(function calculateTotalHours() {
    var totalHours = 0;

    var expenseEntries = new GlideAggregate('x_loasu_contractor_expense_entries');
    expenseEntries.addQuery('parent_expense_report', current.sys_id); // Ensure correct linkage
    expenseEntries.addAggregate('SUM', 'hours_worked');
    expenseEntries.query();

    if (expenseEntries.next()) {
        totalHours = parseFloat(expenseEntries.getAggregate('SUM', 'hours_worked')) || 0;
    }

    return totalHours;
})();

I've attached screenshots of both tables:
Contractor Project Table

Expense Entries Table

 

 

[Medi C · Accepted solution]

Hi @Xavier_Wharton,

Since you are not grouping by any field, you are missing a line of code:

expenseEntries.setGroup(false);

Please try the following:

(function calculateTotalHours() {
    var totalHours = 0;

    var expenseEntries = new GlideAggregate('x_loasu_contractor_expense_entries');
    expenseEntries.addQuery('parent_expense_report', current.sys_id); // Ensure correct linkage
    expenseEntries.setGroup(false);
    expenseEntries.addAggregate('SUM', 'hours_worked');
    expenseEntries.query();

    if (expenseEntries.next()) {
        totalHours = parseFloat(expenseEntries.getAggregate('SUM', 'hours_worked')) || 0;
    }

    return totalHours;
})();

[Xavier_Wharton]

@Medi C this worked!! Thank you so much. I knew it was something small. I'm still trying to get a handle on scripting.