Convert total sum value into words

[Vijay Baokar]

Hi Folks,

Since i am working on some calculation use case where i am getting a sum of integer column (u_new_call_amount) and storing the value in another field. however i also need to convert the total sum value into words and store in another string field.

Below is the script i am using:

    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 = sum+parseInt(grfile.u_new_call_amount);

        var totalcalls = grfile.getRowCount();

        gs.log("Total sum of calls are : " + sum);

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

        var GST = sum * 0.18;

       gs.log("GST on total sum is: " + GST);

        var netAmt = sum + GST;

        gs.log("Net amount is :" + netAmt);

    }

    // Update the sum and total calls in payfile summary table

    var grsum = new GlideRecord('u_payfile_summary');

    grsum.initialize();

    grsum.u_partner_name=grfile.u_supplier;

    grsum.u_month=grfile.u_month;

    grsum.u_total_amount_payable_to_partner=sum;

    grsum.u_total_no_of_calls_closed_by_partner = totalcalls;

    grsum.u_gst_at_the_rate_18=GST;

    grsum.u_net_amount=netAmt;

    grsum.insert();

}

I need to convert the value from "sum" into words and store in another string field.

[Ankur Bawiskar]

@Vijay Baokar 

update as this

I already answered your query by sharing the working script, you just need to use the correct function and debug if it doesn't work

processBusinessRules();

function numberToWords(num) {
    var a = [
        '', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine', 'ten',
        'eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'
    ];
    var b = [
        '', '', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety'
    ];

    if ((num = num.toString()).length > 9) return 'overflow';
    var n = ('000000000' + num).substr(-9).match(/^(\d{2})(\d{2})(\d{2})(\d{1})(\d{2})$/);
    if (!n) return;
    var str = '';
    str += (n[1] != 0) ? (a[Number(n[1])] || b[n[1][0]] + ' ' + a[n[1][1]]) + ' crore ' : '';
    str += (n[2] != 0) ? (a[Number(n[2])] || b[n[2][0]] + ' ' + a[n[2][1]]) + ' lakh ' : '';
    str += (n[3] != 0) ? (a[Number(n[3])] || b[n[3][0]] + ' ' + a[n[3][1]]) + ' thousand ' : '';
    str += (n[4] != 0) ? (a[Number(n[4])] || b[n[4][0]] + ' ' + a[n[4][1]]) + ' hundred ' : '';
    str += (n[5] != 0) ? ((str != '') ? 'and ' : '') + (a[Number(n[5])] || b[n[5][0]] + ' ' + a[n[5][1]]) + ' ' : '';
    return str.trim();
}

function processBusinessRules() {
    var uniqueIds = [];
    var excludeIds = [];
    var br = new GlideRecord("u_business_rules");
    br.addEncodedQuery("u_active=true^u_operation_type=exclusions^u_status=resolution code");
    br.query();
    gs.info("BR count : " + br.getRowCount());
    while (br.next()) {
        updateExclusionRecords(br.u_exclude_state, br.u_country, br.u_supplier.legal_name, excludeIds);
        addUniqueSysIds(br.u_exclude_state, br.u_country, br.u_supplier.legal_name, uniqueIds);
    }
    gs.info("Unique IDs count: " + uniqueIds.length);
    gs.info("Exclude IDs count: " + excludeIds.length);

    // Remove excludeIds from uniqueIds
    var filteredUniqueIds = removeExcludesFromUnique(uniqueIds, excludeIds);
    gs.info("Filtered Unique IDs count: " + filteredUniqueIds.length);


    applyStatusExclusions(filteredUniqueIds);

    payfileSummary();
}

function updateExclusionRecords(excludeState, country, supplier, excludeIds) {
    var actualData = new GlideRecord("u_cdax_data_real");
    actualData.addEncodedQuery("u_caseresolutioncode=" + excludeState + "^u_supplierLIKE" + supplier);
    actualData.query();
    gs.info("update count " + actualData.getRowCount());
    while (actualData.next()) {
        gs.info("Exclusion partnumber is: " + actualData.u_partnumber);
        var sysId = actualData.sys_id.toString();
        if (!excludeIds.includes(sysId)) {
            excludeIds.push(sysId);
            actualData.u_exclusion_reason = "Resolution Code Exclusion Applied";
            actualData.u_is_excluded = true;
            actualData.update();
        }
    }
}

function addUniqueSysIds(excludeState, country, supplier, uniqueIds) {
    var cdaxData = new GlideRecord("u_cdax_data_real");
    cdaxData.addEncodedQuery("u_caseresolutioncode!=" + excludeState + "^ORu_caseresolutioncodeISEMPTY^u_supplierLIKE" + supplier);
    cdaxData.query();
    gs.info("Exclude state: " + excludeState);
    while (cdaxData.next()) {
        if (!uniqueIds.includes(cdaxData.sys_id.toString())) {
            uniqueIds.push(cdaxData.sys_id.toString());
            gs.info("Part number is: " + cdaxData.u_partnumber);
        }
    }
}

function removeExcludesFromUnique(uniqueIds, excludeIds) {
    var filteredUniqueIds = [];
    for (var i = 0; i < uniqueIds.length; i++) {
        if (excludeIds.indexOf(uniqueIds[i]) === -1) {
            filteredUniqueIds.push(uniqueIds[i]);
        }
    }
    return filteredUniqueIds;
}

function applyStatusExclusions(arr) {
    var br2 = new GlideRecord("u_business_rules");
    br2.addEncodedQuery("u_active=true^u_operation_type=exclusions^u_status=status");
    br2.query();
    if (br2.next()) {
        for (var i = 0; i < arr.length; i++) {
            processStatusExclusion(br2.u_exclude_state, arr[i]);
        }
    }
}

function processStatusExclusion(excludeState, sysId) {
    var actualData1 = new GlideRecord("u_cdax_data_real");
    // actualData1.addEncodedQuery("u_wo_system_status_code=^ORu_wo_system_status_codeNOT LIKE" + excludeState + "^sys_id=" + sysId);
    actualData1.addQuery("sys_id", sysId);
    actualData1.query();
    while (actualData1.next()) {
        if (actualData1.u_wo_system_status_code != excludeState) {
            insertPayfileRecord(actualData1);
        } else {
            actualData1.u_is_excluded = true;
            actualData1.u_exclusion_reason = "WO System Status Exclusion(Closed - Canceled) is Applied";
            actualData1.update();
        }
    }



}

function insertPayfileRecord(actualData1) {
    var pl = actualData1.u_productlinecode;
    var plCode = pl.substring(1, 3);

    var pf = new GlideRecord("u_payfile");
    pf.initialize();
    pf.u_name = "India-NCR-Mphasis-25May2025-24May2025";
    pf.u_month = "May";
    // pf.u_country.setDisplayValue(actualData1.u_country);
    pf.u_country = "bec64c473b8062105cfe0954c3e45a88"; //India
    // pf.u_supplier.setDisplayValue(actualData1.u_supplier);
    pf.u_supplier = "792d9fc82b956690f90cf760ce91bf88"; //Mphasis
    pf.u_case_id = actualData1.u_caseid;
    pf.u_activebookingcode = actualData1.u_activebookingcode;
    pf.u_bookingid = actualData1.u_bookingid;
    pf.u_case_closed_on = actualData1.u_casecloseddate;
    pf.u_case_created_on = actualData1.u_casecreatedon;
    pf.u_caseresolutioncode = actualData1.u_caseresolutioncode;
    pf.u_casesubject = actualData1.u_casesubject;
    pf.u_case_otc_code_value = actualData1.u_case_otc_code_value;
    pf.u_inc_otc_code = actualData1.u_inc_otc_code;
    pf.u_part_number = actualData1.u_partnumber;
    pf.u_service_type = "Onsite";
    pf.u_material_order_number = actualData1.u_material_order;
    pf.u_mocreatedon = actualData1.u_mocreatedon;
    pf.u_molicreatedon = actualData1.u_molicreatedon;
    pf.u_moliid = actualData1.u_moliid;
    pf.u_moli_name = actualData1.u_moli_name;
    pf.u_product_number = actualData1.u_productnumber;
    pf.u_repairclasscodeid = actualData1.u_repairclasscodeid;
    pf.u_pl_key = plCode;
    pf.u_wo_otc_code = actualData1.u_wo_otc_code;
    pf.u_wo_otc_code_value = actualData1.u_wo_otc_code_value;
    pf.u_wo_number = actualData1.u_workordernumber;
    pf.u_wo_created_on = actualData1.u_workordercreatedon;
    pf.u_wo_closed_on = actualData1.u_workordercloseddate;
    pf.u_wo_system_status_code = actualData1.u_wo_system_status_code;

    pf.insert();

    gs.addInfoMessage("CDAX Records are added to Payfile table successfully!!");

    mapPartTier(pf);

    if (!pf.u_error_out) {
        calculateLineItemRates(pf);
    }
}

function mapPartTier(pf) {
    var partmap = new GlideRecord("u_part_mapping");
    partmap.addQuery("u_part_number", pf.u_part_number);
    partmap.addQuery("u_country", pf.u_country);
    partmap.query();
    if (partmap.next()) {
        pf.u_part_tier = partmap.u_program_code_part_class_part_used;
        pf.update();
    } else {
        gs.addErrorMessage("Part Mapping is missing for " + pf.u_part_number);
        pf.u_error_out = true;
        pf.u_reason_for_error = " Part Mapping is Missing";
        pf.update();
    }
}

function calculateLineItemRates(pf) {
    var rc = new GlideRecord("fm_labor_rate_card");
    rc.addQuery("u_pl_key", 'CONTAINS', pf.u_pl_key);
    rc.addQuery("u_part_tier", pf.u_part_tier);
    rc.addQuery("u_service_type", pf.u_service_type);
    rc.query();
    if (rc.next()) {
        pf.u_new_call_amount = rc.u_rate;
        pf.update();
    } else {
        gs.addErrorMessage("Rate card is not found for the " + pf.u_pl_key + " Payfile PL Key");
        pf.u_error_out = true;
        pf.u_reason_for_error = "Rate Card is not found with this PL key,Part Category and Service Type";
        pf.update();
    }
}

function payfileSummary() {

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

    //gs.log("Current Supplier is : " + grfile.u_supplier);

    var sum = 0;
    while (grfile.next()) {
        sum = sum + parseInt(grfile.u_new_call_amount);
        var totalcalls = grfile.getRowCount();

        gs.log("Total sum of calls are : " + sum);
        gs.log("Total calls are : " + totalcalls);

        var GST = sum * 0.18;

        gs.log("GST on total sum is: " + GST);

        var netAmt = sum + GST;

        gs.log("Net amount is :" + netAmt);

    }
    // Update the sum and total calls in psyfile summary table
    var grsum = new GlideRecord('u_payfile_summary');
    grsum.initialize();
    grsum.u_partner_name = grfile.u_supplier;
    grsum.u_month = grfile.u_month;
    grsum.u_total_amount_payable_to_partner = numberToWords(sum);
    grsum.u_total_no_of_calls_closed_by_partner = totalcalls;
    grsum.u_gst_at_the_rate_18 = GST;
    grsum.u_net_amount = netAmt;
    grsum.insert();

}

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