Scenario: We require an exchange rate search that provides the exchange rate of the selected currency on the chosen date. If the date is in the future, the exchange rate for that day will not be available. In such cases, we need the latest exchange rate available in NetSuite.
If the selected currency is the base currency, then from the beginning date, it will be ‘1’ at all times. In this situation, we remove the date criteria from the search by setting the date as an empty value (”).
Explanation of Variables: currencyId = Internal Id of the selected currency ExchangeRateOnDate = preffered date (date frmat should be 12/31/2023) BASE_CURRENCY = Internal Id of the Base currency.
The search;
/*****************************************************************************************
To get the latest exchange rate
*****************************************************************************************/
getCurrencyExchangeObj: function (currencyId, ExchangeRateOnDate) {
log.debug('ExchangeRateOnDate', ExchangeRateOnDate)
if (checkForParameter(currencyId) && (currencyId == 1)) { // currencyId == 1, then it is Base currency
ExchangeRateOnDate = '';
}
var exchangeRate = ''
filters = [
["basecurrency", "anyof", BASE_CURRENCY],
"AND",
["transactioncurrency", "anyof", currencyId]
]
if (checkForParameter(ExchangeRateOnDate) && ExchangeRateOnDate != '') {
filters.push("AND", ["effectivedate", "on", ExchangeRateOnDate])
}
var currencyrateSearchObj = search.create({
type: "currencyrate",
filters: filters,
columns: [
search.createColumn({
name: "effectivedate",
summary: "MAX",
label: "Effective Date"
}),
search.createColumn({
name: "formulanumeric",
summary: "MAX",
formula: "MAX({exchangerate}) KEEP(DENSE_RANK LAST ORDER BY {effectivedate} )",
label: "Formula (Numeric)"
})
]
});
var searchResultCount = currencyrateSearchObj.runPaged().count;
log.debug('exchangeRateSearchResultCount', searchResultCount)
if ((currencyrateSearchObj.runPaged().count) < 1) {
return false;
} else {
currencyrateSearchObj.run().each(function (result) {
exchangeRate = result.getValue(currencyrateSearchObj.columns[1]);
return true;
});
return exchangeRate;
}
},