How to get All the effective Currency exchange rate by date in NetSuite one time?

Question

[![enter image description here][1]][1]

Any method to query All the effective Currency exchange rates by date in NetSuite one time? Just like the Field "AS OF" in the Currency Exchange Rates Page did.

I thought about the "N/curency" module and the https.get function, but it seems to be high cost, any tips or solution? [1]: https://i.sstatic.net/1KPMK.png

Answer 1

The script below is my final solution to fulfill my demand, You may run it on any Record page's Console, It will print the result JUST like the "Currency Exchange Rates" page shows.

I'm not sure if it's a good one, but it did returns the data that I want.

require(['N/query'], function getEffCcyExchRtArr(query) {

    const getQueryResBySql = (sql) => {

        const resObj = query.runSuiteQL({query: sql, });
        return resObj.asMappedResults();
    };

    const getPagedQueryResBySql = (sql) => {

        const maxPageSize = 1_000;
        const resultSet = query.runSuiteQLPaged({query: sql, pageSize: maxPageSize, });

        const tempArr = [];
        if (resultSet && resultSet.count > 0) {
            for (let i = 0; i < resultSet.pageRanges.length; i++)  {
                const page = resultSet.fetch(i);
                page.data.results.forEach(item => tempArr.push(item.asMap()));
            }
        }

        return tempArr;
    };

    const getAllCurrencyArr = () => {

        const sql = `
            SELECT
                Currency.id AS id,
                Currency.isbasecurrency AS is_base_ccy,
                Currency.symbol AS iso_symbol
            FROM
                Currency
            WHERE
                NVL(Currency.isinactive, 'F') = 'F'
        `;

        return getQueryResBySql(sql);
    };

    const getAllEffectiveCcyExchRtArr = (effectiveDateStr) => {

        let effectiveDate = !!effectiveDateStr ? `to_date('${effectiveDateStr}','YYYY-MM-DD')` : 'CURRENT_DATE';
        let sql = `
            SELECT
                currencyRate.basecurrency AS base_ccy,
                currencyRate.transactioncurrency AS trans_ccy,
            
                currencyRate.exchangerate AS exchange_rt,
                currencyRate.effectivedate AS effective_date
            FROM
                currencyRate
            WHERE
                currencyRate.effectivedate <= ${effectiveDate}
            ORDER BY currencyRate.effectivedate DESC, currencyRate.id DESC
        `;

        return getPagedQueryResBySql(sql);
    };

    const currencyArr = getAllCurrencyArr();
    const baseCurrencyIdArr = currencyArr.filter(item => item['is_base_ccy'] === 'T').map(item => item.id);
    const allCurrencyIdArr = currencyArr.map(item => item.id);
    const currencyIdSymbolObj = currencyArr.reduce((pre, cur) => {

        pre[cur?.id] = cur?.['iso_symbol'];
        return pre;
    }, {});
    const allEffectiveCcyExchRtArr = getAllEffectiveCcyExchRtArr();

    const effectiveCcyExchRtArr = [];
    for (const baseCurrencyId of baseCurrencyIdArr) {
        for (const currencyId of allCurrencyIdArr) {
            for (const currencyObj of allEffectiveCcyExchRtArr) {

                if (currencyObj?.['base_ccy'] === baseCurrencyId && currencyObj?.['trans_ccy'] === currencyId) {

                    effectiveCcyExchRtArr.push({
                        baseCurrency: currencyIdSymbolObj[baseCurrencyId],
                        transactionCurrency: currencyIdSymbolObj[currencyId],
                        exchangeRate: currencyObj?.['exchange_rt']
                    });
                    break;
                }
            }
        }
    }

    window.console.table(effectiveCcyExchRtArr);
}, );

Answer 2

The table is exposed in SuiteTalk and the Analytics browser so you can get the values either way. Via Analytics/SuiteQL

require(['N/query'], function(query) {
   var sql =
        "SELECT " +
        " cr.id, b.symbol as basecurrency, c.symbol as transactioncurrency,  cr.effectivedate, cr.exchangerate" +
        " FROM " +
        "  currencyrate as cr, currency as c, currency b where c.id = transactioncurrency and b.id = basecurrency and cr.effectivedate = '5/2/2022' ";

    var results = query.runSuiteQL({
        query: sql
    }).asMappedResults();

    console.log(results);
});