Arrays not calculating total values correctly

[SNAdmin47]

I'm trying to write a script which will trigger when a project is saved, which will then lookup any associated change requests & project tasks and collate the total duration from a bespoke field (total effort) of these records, along with the duration on the project form. This means project managers/team managers can get a realistic time of effort spent on changes and tasks, not just how long they take to get done. 

As such, this Business Rule does a series of record queries and gets the numerical values of the field, pushes them into an array and then uses a loop to calculate the total value of the sums in the arrays... this does it for change firstly, then project tasks (2nd one) and then gets the value of the project total effort. These 3 totals are then placed into an array to calculate the total of all things to do with the project.  

The record queries are working fine and the arrays are collating the data correctly on each test, but it looks like the loops for project tasks and overall calculation are consistently incorrect. Is anybody able to identify what it is I'm doing wrong on the loops to calculate the totals of the arrays?

=================

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

    var projectSysId = current.getUniqueValue();

    var totalChangeEffort = 0;

    var changeArray = [];  

    var totalTaskEffort = 0;

    var taskArray = [];

    //CHANGE - SEEMS TO WORK FINE

    var ChangeRecords = new GlideRecord('change_request');

    ChangeRecords.addQuery('u_project', projectSysId);

    ChangeRecords.query();

    while (ChangeRecords.next()) {

        changeArray.push(ChangeRecords.u_total_duration.getGlideObject().getNumericValue());

    }

    for (i = 0; i <= changeArray.length; i++) {

        totalChangeEffort += parseInt(changeArray);

    }

    gs.log('The value of changeArray is: ' + changeArray);

    gs.log('The total value of totalChangeEffort is: ' + totalChangeEffort);

    //PROJECT TASK - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY

    var ProjectTasks = new GlideRecord('pm_project_task');

    ProjectTasks.addQuery('parent', projectSysId);

    ProjectTasks.query();

    while (ProjectTasks.next()) {

        taskArray.push(ProjectTasks.u_total_duration.getGlideObject().getNumericValue());

    }

    for (i = 0; i <= taskArray.length; i++) {

        totalTaskEffort += parseInt(taskArray);

    }

    gs.log('The total value of taskArray is: ' + taskArray);

    gs.log('The total value of totalTaskEffort is: ' + totalTaskEffort);

    //COLLATE ALL - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY

    var totalAll = 0;

    var totalArrayEffort = [];

    var totalEffortProject = current.u_total_duration.getGlideObject().getNumericValue();

    gs.log('The total effort on the project form is: ' + totalEffortProject);

    totalArrayEffort.push(totalChangeEffort);

    totalArrayEffort.push(totalTaskEffort);

    totalArrayEffort.push(totalEffortProject);

    gs.log('The value of totalArrayEffort is: ' + totalArrayEffort);

    for (i = 0; i <= totalArrayEffort.length; i++) {

        totalAll += parseInt(totalArrayEffort);

    }

    gs.log('The total effort for EVERYTHING is: ' + totalAll);

})(current, previous);

=====================

As examples of how this isn't calculating correctly, I can see that the arrays are collating the numerical values of the record queries correctly.... but when it comes to totalling those sums correctly the maths simply doesn't add up:

CHANGE
changeArray : 300000,300000,600000 - CORRECT
totalEffortChange : 1200000 - 20 mins - CORRECT

PROJECT TASK
taskArray: 600000,900000,1200000 - Total of 2700000 - 20 mins - CORRECT
totalTaskEffort: 2400000 - Should be 2700000 as per total sum of the above array - INCORRECT

PROJECT FORM:
totalEffortProject : 10800000 - 3 HOURS - CORRECT

COLLATING ALL
totalArrayEffort:1200000,2400000,10800000 (Although the 2400000 integer should be 2700000) - CORRECT
totalAll: 4800000 - INCORRECT, THE SUM OF THE ABOVE SHOULD BE 14400000

I've tried changing elements of the code repeatedly and I've exhausted my efforts, using different structures to the code, using the = operator on the loop as well as <, many, many different attempts... if anyone has the time and effort available to point out what I'm doing wrong that would be hugely appreciated. Many thanks in advance. 

[Ivan Betev]

Hello there,

Can you change it like this:

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

    var projectSysId = current.getUniqueValue();
    var totalChangeEffort = 0;
    var changeArray = [];  
    var totalTaskEffort = 0;
    var taskArray = [];

    //CHANGE - SEEMS TO WORK FINE
    var ChangeRecords = new GlideRecord('change_request');
    ChangeRecords.addQuery('u_project', projectSysId);
    ChangeRecords.query();
    while (ChangeRecords.next()) {
        changeArray.push(ChangeRecords.u_total_duration.getGlideObject().getNumericValue());
    }
    for (var i = 0; i < changeArray.length; i++) {
        totalChangeEffort += parseInt(changeArray);
    }
    gs.log('The value of changeArray is: ' + changeArray);
    gs.log('The total value of totalChangeEffort is: ' + totalChangeEffort);

    //PROJECT TASK - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY
    var ProjectTasks = new GlideRecord('pm_project_task');
    ProjectTasks.addQuery('parent', projectSysId);
    ProjectTasks.query();
    while (ProjectTasks.next()) {
        taskArray.push(ProjectTasks.u_total_duration.getGlideObject().getNumericValue());
    }
    for (var i = 0; i < taskArray.length; i++) {
        totalTaskEffort += parseInt(taskArray);
    }
    gs.log('The total value of taskArray is: ' + taskArray);
    gs.log('The total value of totalTaskEffort is: ' + totalTaskEffort);

    //COLLATE ALL - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY
    var totalAll = 0;
    var totalArrayEffort = [];
    var totalEffortProject = current.u_total_duration.getGlideObject().getNumericValue();
    gs.log('The total effort on the project form is: ' + totalEffortProject);
    totalArrayEffort.push(totalChangeEffort);
    totalArrayEffort.push(totalTaskEffort);
    totalArrayEffort.push(totalEffortProject);
    gs.log('The value of totalArrayEffort is: ' + totalArrayEffort);

    for (var i = 0; i < totalArrayEffort.length; i++) {
        totalAll += parseInt(totalArrayEffort);
    }

    gs.log('The total effort for EVERYTHING is: ' + totalAll);

})(current, previous);

Regards, Ivan

[SNAdmin47]

Hi @Ivan Betev ,

Thanks for the suggestion but without the = operator on the loops I'm now seeing the change calculations start to be incorrect too:

CHANGE
changeArray: 300000,300000,600000,300000 - CORRECT
totalEffortChange : 1200000 - 25 mins - INCORRECT

PROJECT TASK
taskArray: 600000,900000,1200000 - Total of 2700000 - 20 mins - CORRECT
totalTaskEffort: 1800000 - Should be 2700000 as per total sum of the above array - INCORRECT

PROJECT FORM
totalEffortProject: 11580000 - 3 Hours 13 Minutes - CORRECT

COLLATING ALL
totalArrayEffort: 1200000,1800000,11580000 - First 2 are incorrect due to the previous arrays not caculating correctly, but the values are correct depending on the previous arrays calculating properly. 
totalAll: 3600000 - The sum of the above should be 14580000 - INCORRECT

If you have any other suggestions I'm all ears! 🙂

[Ivan Betev · Accepted solution]

Hello @SNAdmin47 ,

Try this:

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

    var projectSysId = current.getUniqueValue();
    var totalChangeEffort = 0;
    var changeArray = [];  
    var totalTaskEffort = 0;
    var taskArray = [];

    //CHANGE - SEEMS TO WORK FINE
    var changeRecordsGr = new GlideRecord('change_request');
    changeRecordsGr.addQuery('u_project', projectSysId);
    changeRecordsGr.query();
    while (changeRecordsGr.next()) {
        changeArray.push(changeRecordsGr.u_total_duration.getGlideObject().getNumericValue());
    }
    for (var i = 0; i < changeArray.length; i++) {
        totalChangeEffort += parseInt(changeArray[i]);
    }
    gs.log('The value of changeArray is: ' + changeArray);
    gs.log('The total value of totalChangeEffort is: ' + totalChangeEffort);

    //PROJECT TASK - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY
    var projectTasksGr = new GlideRecord('pm_project_task');
    projectTasksGr.addQuery('parent', projectSysId);
    projectTasksGr.query();
    while (projectTasksGr.next()) {
        taskArray.push(projectTasksGr.u_total_duration.getGlideObject().getNumericValue());
    }
    for (var i = 0; i < taskArray.length; i++) {
        totalTaskEffort += parseInt(taskArray[i]);
    }
    gs.log('The total value of taskArray is: ' + taskArray);
    gs.log('The total value of totalTaskEffort is: ' + totalTaskEffort);

    //COLLATE ALL - WON'T COLLATE THE VALUES OF THE ARRAY CORRECTLY
    var totalAll = 0;
    var totalArrayEffort = [];
    var totalEffortProject = current.u_total_duration.getGlideObject().getNumericValue();
    gs.log('The total effort on the project form is: ' + totalEffortProject);
    totalArrayEffort.push(totalChangeEffort);
    totalArrayEffort.push(totalTaskEffort);
    totalArrayEffort.push(totalEffortProject);
    gs.log('The value of totalArrayEffort is: ' + totalArrayEffort);

    for (var i = 0; i < totalArrayEffort.length; i++) {
        totalAll += parseInt(totalArrayEffort[i]);
    }

    gs.log('The total effort for EVERYTHING is: ' + totalAll);

})(current, previous);

Regards, Ivan

[SNAdmin47]

Hi @Ivan Betev ,

Thanks, from the first test I've done that seems to be calculating them correctly (yay woop). Thanks... I'm convinced I had previously tried using the [i] to force the array values but it seems to be working now, that's the main thing. I'm going to do some more testing today and if they continue to calculate properly I'll be back later to mark your answer as successful. Thanks again for your effort on this, really appreciate it. I'd been pulling my hair out with that! 😁