Currency field is not setting up correctly

[Nivedita9]

Hi,
I'm facing an issue with transform map. I have a field in excel sheet "Amount" where user can give numbers. It works well if I enter and transform whole number but when I enter decimal number like 7400.78 the amount on expense line field is setting as 740078.00. However, I see in staging table this as it is 7400.78(field type is string) Adding screenshot as well.

Code in On before script

 if (source.u_amount != '') { //whatever is the task currency type the expense line amount field should also have same currency type.

        var sourceAmount = source.u_amount;

        var finalRes = sourceAmount.replace(/[^0-9]/g, '');

        var taskCurrency;

        var grFEL = new GlideRecord('fm_expense_line');

        grFEL.addEncodedQuery('number=' + source.u_number);

        grFEL.query();

        if (grFEL.next()) {

            taskCurrency = grFEL.task.project_currency.getDisplayValue();

        }

        var ret = 0;

        if (sourceAmount.indexOf(".") >= 0) {

            var value1 = sourceAmount.toString().split(".")[0];

            var dec = sourceAmount.toString().split(".")[1];

            ret = value1 + "," + dec;

            var decimalVal = target.amount(".00",'');

            target.amount = taskCurrency + ';' + ret;

                 } else {

            target.amount = taskCurrency + ';' + finalRes;

                   }

Need help in troubleshooting this?

[Mark Manders · Accepted solution]

Could you try it with this code in your onBefore script?

if (source.u_amount != '') {
    var sourceAmount = source.u_amount.toString();
    var taskCurrency;
    var grFEL = new GlideRecord('fm_expense_line');
    grFEL.addEncodedQuery('number=' + source.u_number);
    grFEL.query();
    
    if (grFEL.next()) {
        taskCurrency = grFEL.getDisplayValue('task.project_currency');
    }
    var ret = sourceAmount.replace(/,/g, ''); // Remove commas if any
    if (sourceAmount.indexOf(".") >= 0) {
        var parts = ret.split('.');
        var integerPart = parts[0];
        var decimalPart = parts[1];
        if (decimalPart.length === 1) {
            decimalPart += "0"; // Ensure two decimal places
        }
        ret = integerPart + "." + decimalPart;
    }
    target.amount = taskCurrency + ';' + ret;
}

[Nivedita9]

Thank you.It is working well @Mark Manders