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

[Mark Manders]

var ga = new GlideAggregate('u_payfile');
ga.addQuery('u_supplier', '792d9fc82b956690f90cf760ce91bf88');
ga.addNotNullQuery('u_new_call_amount');
ga.addAggregate('COUNT');
ga.addAggregate('SUM', 'u_new_call_amount');
ga.query();

if (ga.next()) {
    var count = ga.getAggregate('COUNT');
    var sum = ga.getAggregate('SUM', 'u_new_call_amount');

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

    var grsum = new GlideRecord('u_payfile_summary');
    grsum.initialize();
    grsum.u_partner_name = '792d9fc82b956690f90cf760ce91bf88';
    grsum.u_total_amount_payable = sum;
    grsum.u_total_no_of_calls = count;
    grsum.insert();
}

[Vijay Baokar]

Hi @Mark Manders  thanks for the response, with your cope i am getting 

Total calls are: 60
Total sum of calls are: 0

but i have 155 total calls and sum of calls are 11267, this is expected.

currently its giving count of 60 records where the value is 0 for each records

[pavani_paluri]

Hi Vijay,

Could you Please try with below code:

var grfile = new GlideRecord('u_payfile');
grfile.addQuery('u_supplier', '792d9fc82b956690f90cf760ce91bf88');
grfile.addNotNullQuery('u_new_call_amount');
grfile.query();

// Capture total count before iteration
var totalcalls = grfile.getRowCount();

var sum = 0;
var supplier = null;

while (grfile.next()) {
var amount = parseFloat(grfile.u_new_call_amount);
if (!isNaN(amount)) {
sum += amount;
}
// Save supplier from first record
if (!supplier) {
supplier = grfile.u_supplier;
}
}

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

// Insert summary only once
if (totalcalls > 0 && supplier) {
var grsum = new GlideRecord('u_payfile_summary');
grsum.initialize();
grsum.u_partner_name = supplier;
grsum.u_total_amount_payable = sum;
grsum.u_total_no_of_calls = totalcalls;
grsum.insert();
}

Mark it helpful if this helps you to understand. Accept solution if this give you the answer you're looking for
Kind Regards,
Pavani P

[Ankur Bawiskar]

@Vijay Baokar 

try this -> use GlideAggregate and also check if record is already there for supplier before inserting

// Supplier sys_id (replace with dynamic value if needed)
var supplierId = '792d9fc82b956690f90cf760ce91bf88';

// Aggregate data directly using GlideAggregate
var ga = new GlideAggregate('u_payfile');
ga.addQuery('u_supplier', supplierId);
ga.addNotNullQuery('u_new_call_amount');
ga.addAggregate('COUNT', 'sys_id'); // Total records
ga.addAggregate('SUM', 'u_new_call_amount'); // Sum of amounts
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);

// 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.