Total sum count to be mapped to another table as single record

[Vijay Baokar]

Hi All,

I am getting the count of string column (u_new_call_amount) for records(155) in the table and then need to create a SINGLE record in another custom table for same 'Supplier' (792d9fc82b956690f90cf760ce91bf88) and set the total call count and total sum count as per below given result

Note: I don't want to create 155 records in another table that is only ask i have, only one record needs to be created

var grfile = new GlideRecord('u_payfile');

grfile.addQuery('u_supplier', '792d9fc82b956690f90cf760ce91bf88');

grfile.addNotNullQuery('u_new_call_amount');

grfile.query();

var sum = 0;

while (grfile.next()) {

    //sum += parseInt(grfile.u_new_call_amount);

    sum = sum+parseInt(grfile.u_new_call_amount);

}

var totalcalls = grfile.getRowCount();

gs.print('Total calls are: ' + totalcalls);

gs.print('Total sum of calls are:'+ sum);

 var grsum = new GlideRecord('u_payfile_summary');

    grsum.initialize();

    grsum.u_partner_name=grfile.u_supplier;

    grsum.u_total_amount_payable=sum;

    grsum.u_total_no_of_calls = totalcalls;

    grsum.insert();

I am getting result like below:

*** Script: Total calls are: 155
*** Script: Sum of all calls are: 11267

[Vijay Baokar]

Hi @Ankur Bawiskar thanks for the response, i am executing your code just to print to get total calls and total sum but i am getting below result only 1 call and its sum however i have 155 calls / record and the sum is 11267 its not matching with your result

*** Script: Total calls: 1
*** Script: Total sum: 828

[Vijay Baokar]

var supplierId = '792d9fc82b956690f90cf760ce91bf88';

// Aggregate data directly using GlideAggregate

var ga = new GlideAggregate('u_hp_payfile');

ga.addQuery('u_supplier', supplierId);

//ga.addNotNullQuery('u_new_call_amount');

ga.addAggregate('COUNT', 'sys_id'); // Total records are 155

ga.addAggregate('SUM', 'u_new_call_amount'); // Sum of amounts is 11267

ga.query();

var totalCalls = 0;

var totalSum = 0;

if (ga.next()) {

    totalCalls = ga.getAggregate('COUNT', 'sys_id');

    totalSum = parseInt(ga.getAggregate('SUM', 'u_new_call_amount'));

}

gs.info('Total calls: ' + totalCalls);

gs.info('Total sum: ' + totalSum);

its giving 

*** Script: Total calls: 1
*** Script: Total sum: 828

[Ankur Bawiskar]

@Vijay Baokar 

try this

var supplierId = '792d9fc82b956690f90cf760ce91bf88';

// Use GlideAggregate for sum and count
var ga = new GlideAggregate('u_payfile');
ga.addQuery('u_supplier', supplierId);
ga.addNotNullQuery('u_new_call_amount');
ga.addAggregate('COUNT'); // Count all records
ga.addAggregate('SUM', 'u_new_call_amount'); // Sum of amounts
ga.query();

var totalCalls = 0;
var totalSum = 0;

if (ga.next()) {
    totalCalls = parseInt(ga.getAggregate('COUNT')); // Correct: count of all records
    totalSum = parseInt(ga.getAggregate('SUM', 'u_new_call_amount'));
}

gs.info('Total calls: ' + totalCalls);
gs.info('Total sum: ' + totalSum);

// Create/update summary record
var grSummary = new GlideRecord('u_payfile_summary');
grSummary.addQuery('u_partner_name', supplierId); // Ensure field matches reference
grSummary.query();

if (grSummary.next()) {
    // Update existing record
    grSummary.u_total_amount_payable = totalSum;
    grSummary.u_total_no_of_calls = totalCalls;
    grSummary.update();
} else {
    // Insert new record
    grSummary.initialize();
    grSummary.u_partner_name = supplierId;
    grSummary.u_total_amount_payable = totalSum;
    grSummary.u_total_no_of_calls = totalCalls;
    grSummary.insert();
}

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@Vijay Baokar 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Vijay Baokar]

Hi @Ankur Bawiskar  Thanks for all the responses so far but i am still getting the same response while using GlideAggregate, with below script i am getting expected result but i am doing

getRowCount which i don't want:

var supplierId = '792d9fc82b956690f90cf760ce91bf88';

var grfile = new GlideRecord('u_payfile');

    grfile.addQuery('u_supplier',supplierId);

    grfile.addNotNullQuery('u_new_call_amount');

    grfile.query();

    var sum = 0;

    while (grfile.next())

        sum = sum+parseInt(grfile.u_new_call_amount);

        var totalcalls = grfile.getRowCount();

        gs.info("Sum of calls are: " + sum);

        gs.info("Total calls are: " + totalcalls);

   Result:

*** Script: Sum of calls are: 11267
*** Script: Total calls are: 155

 But with your script i am getting below:

*** Script: Total calls: 60
*** Script: Total sum: 0