0

[![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.stack.imgur.com/1KPMK.png

Tyrion Huang
  • 71
  • 1
  • 12

2 Answers2

1

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);
});
bknights
  • 14,408
  • 2
  • 18
  • 31
  • But this SuiteQL cannot be act like the "AS OF" filter field did, What I want is to get all the effective Currency Exchange Rate by certain date, While the Table CurrencyRate seems had not expose enough column to fulfill my demand. – Tyrion Huang Jul 05 '22 at 00:32
  • It does if you add a date filter. See edited code example – bknights Jul 05 '22 at 01:02
  • I got the update for a specific effective date column filter with the value "5/22/2022". At the same time, this suiteQL returns the specific Effective Date equal to this date, and the "AS OF" filter in the NS "Currency Exchange Rates" page returns a list of currencies which is effective on this date. So I think my demand still has not been fulfilled. – Tyrion Huang Jul 05 '22 at 05:15
  • Have you run this code and reviewed the results? – bknights Jul 05 '22 at 14:06
  • You have to be prepared to do some work here. Since you mentioned N/currency it sounded like you were prepared to use a script. The sample shows it is possible. If you want rates for a range of dates just change the effectivedate query. If you want a CSV file then write the output to a file cabinet file. – bknights Jul 05 '22 at 16:43
  • Yes, In the end, I turned my way to write some JS to get what I want. – Tyrion Huang Jul 06 '22 at 07:17
  • Hello, Since your final solution essentially used my concept to get you started the polite thing would be to mark my answer as accepted. – bknights Jul 06 '22 at 16:30
0

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);
}, );
Tyrion Huang
  • 71
  • 1
  • 12