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

[Ankur Bawiskar · Accepted solution]

@Vijay Baokar 

are you using correct table, field?

use COUNT and SUM correctly

u_new_call_amount -> should be integer and not string

Ensure no empty data is there

try this

var supplierId = '792d9fc82b956690f90cf760ce91bf88';

// Use the correct table name
var ga = new GlideAggregate('u_hp_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()) {
    // COUNT returns the number of records
    totalCalls = parseInt(ga.getAggregate('COUNT'));
    // SUM may return null/empty if the field is not numeric
    var sumValue = ga.getAggregate('SUM', 'u_new_call_amount');
    totalSum = sumValue ? parseInt(sumValue) : 0;
}

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

// Create/update summary record in u_payfile_summary
var grSummary = new GlideRecord('u_payfile_summary');
grSummary.addQuery('u_partner_name', supplierId);
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.

[Vijay Baokar]

Hi @Ankur Bawiskar yes i updated table that is correct however "u_new_call_amount" is a sting field but none of the records has empty value, initially there were few but i updated 0 to those records.

[Ankur Bawiskar]

@Vijay Baokar 

for GlideAggregate to work that field should be Integer and not String.

Change the field type and then run the script

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