The currency module provides an accurate way to convert the amount of transactions between different currencies. You can use this module to find the exchange rate between two currencies based on a certain date. The following code snippet shows the use of the currency module.
/**
* Function defined to calculate all the espenses of the purchase order
* @param {Array} allExpBill
* @param {Number} currencyId
* @returns {Number}
*/
function getAllExpAmt(allExpBill, currencyId) {
try {
let totalBillAmt = 0;
let vendorbillSearchObj = search.create({
type: "transaction",
filters:
[
["type", "anyof", "VendBill", "Journal"],
"AND",
["mainline", "is", "T"],
"AND",
["internalid", "anyof", allExpBill]
],
columns:
[
search.createColumn({
name: "internalid",
summary: "GROUP",
label: "Internal ID"
}),
search.createColumn({
name: "trandate",
summary: "GROUP",
label: "Date"
}),
search.createColumn({
name: "currency",
summary: "GROUP",
label: "Currency"
}),
search.createColumn({
name: "fxamount",
summary: "SUM",
label: "Amount (Foreign Currency)"
}),
search.createColumn({
name: "formulacurrency",
summary: "SUM",
formula: "CASE WHEN {type}='Bill' THEN {fxamount} ELSE {creditfxamount} END",
label: "Formula (Currency)"
})
]
});
vendorbillSearchObj.run().each(function (result) {
// .run().each has a limit of 4,000 results
let billAmt = result.getValue({
name: "formulacurrency",
summary: "SUM",
formula: "CASE WHEN {type}='Bill' THEN {fxamount} ELSE {creditfxamount} END",
label: "Formula (Currency)"
});
let fromCurrencyId = result.getValue({
name: "currency",
summary: "GROUP",
label: "Currency"
});
let date = result.getValue({
name: "trandate",
summary: "GROUP",
label: "Date"
});
let formattedDate = format.parse({
value: date,
type: format.Type.DATE // Change this to DATE format
});
let exchRate = currency.exchangeRate({
source: fromCurrencyId,
target: currencyId,
date: formattedDate
});
totalBillAmt += (billAmt * exchRate);
return true;
});
return totalBillAmt;
} catch (e) {
log.error('error@getTotBillAmount', e);
return 0;
}
}