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 · Accepted solution]

@Vijay Baokar 

means if the sum is 19 then you need nineteen?

something like this but please use this function in your script

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();
}

var val = numberToWords(9019);
gs.info(val);

Output:

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

[Vijay Baokar]

Hi @Ankur Bawiskar The script which i have given that is already in a function in UI action, now your syntax is also in a function, shall i keep 2 diff function or can i add your code into my function.

[Ankur Bawiskar]

@Vijay Baokar 

you can keep that function outside the UI action code and call it from your main function/code

add the updated one, test and if it doesn't work share here the complete code.

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

[Vijay Baokar]

@Ankur Bawiskar below is the complete code in UI action, my function is "payfileSummary"

processBusinessRules();

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=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();

}

Note: i just need to convert the value stored in "sum = sum+parseInt(grfile.u_new_call_amount);" sum to words in another field.