Calculation total from other table list

[beycos]

Hello Everyone,  

I have created 2 Tables and the first table impact calculation and second one is overall impact. I would like to calculate total of <10 min field from the first table and then populate in the second table overall impact table in total 10 min field.

In the first table <10 min field is choice and in the second table total 10 min field is integer. 

this is my script 

(function calculatedFieldValue(current) {

    var total = 0;

    // Create a GlideAggregate query to calculate the sum of the "u_10_min" field in the x_1002836_f64_c table

    var aggregate = new GlideAggregate('x_1002836_f64_c');

    total = aggregate.addAggregate('SUM', 'u_10_min'); // Calculate the SUM of the "u_10_min" field

    aggregate.query();

    if (aggregate.next()) {

        total = aggregate.getAggregate('SUM', 'u_10_min');

    }

    return total;

})();   

with this script calculate only the same  biggest  number not all number in  the list view. As you see in you screenshot below. (calculate only "5") 

I would like to calculate total of <10 min field and then populate in the overall impact table.

Can anyone help me out with script or point me in the right direction it would be greatly appreciated.

Thank you 

[Rhodri · Accepted solution]

Hello,

I came across the below post. It appears that if you aren't using "addGroup" with  SUM aggregate, it still tries to group by a value in the background. (It groups and sums the same values together, so all your 1s, 2s, 3s etc if you output these throug a while loop)

https://www.servicenow.com/community/in-other-news/glideaggregate-sum-not-summing-correction-is-it-s...

(function calculatedFieldValue(current) {

    var total = 0;

    // Create a GlideAggregate query to calculate the sum of the "u_10_min" field in the x_1002836_f64_c table

    var aggregate = new GlideAggregate('x_1002836_f64_c');

    aggregate.addAggregate('SUM', 'u_10_min'); // Calculate the SUM of the "u_10_min" field
   
aggregate.setGroup(false);

    aggregate.query();

    if (aggregate.next()) {

        total = aggregate.getAggregate('SUM', 'u_10_min');
       
    }
    return total;

})();   

So to fix it you need to add the line "aggregate.setGroup(false);" into your script

setGroup API 

Thanks 🙂

[beycos]

Hello ,

I currently have two tables. I want to calculate the total of the "< 10 min" field for records with the same reference number in the first table ('x_1002836_f64_c'). I would like to display this total in the 'Total of < 10 min' field in the second table. In the first table with the SAME reference table  total of "< 10 min" should be in the second table in total 10min.

(function calculatedFieldValue(current) {

    var total = 0;

    // Check if the current record exists and has a valid reference number

    if (current && current.reference_number) {

        // Create a GlideAggregate query to calculate the sum of the "u_10_min" field

        var aggregate = new GlideAggregate('x_1002836_f64_c');

        // Group by the reference field

        aggregate.addQuery('reference_number', current.reference_number);

        aggregate.addAggregate('SUM', 'u_10_min'); // Calculate the SUM of the "u_10_min" field

        aggregate.query();

        if (aggregate.next()) {

            // Update total with the sum of "u_10_min" for the same reference field value

            total = aggregate.getAggregate('SUM', 'u_10_min');

        }

    }

    return total;

})();

Unfortunately This script is not working.

I would be grateful if you could assist me.

Thanks in advance.

[beycos]

Huge Thank you Rhodri. Working now. 🙂