Published on May 18, 2021.

One of my NetSuite clients is transacting business in more than 60 currencies. They recently reached out to me for help with a currency exchange-related request, which lead me to explore the currency-related tables that are available via SuiteQL.

There are essentially two currency-related tables that we have access to: Currency, and CurrencyRate. Let's take a quick look at those tables.

The Currency Table

Information about the currencies that an account is configured for is stored in a table named - you guessed it - Currency. Here's a query that you can use to retrieve the currencies.

	
SELECT
	Symbol,
	Name,
	ExchangeRate,
	DisplaySymbol,
	SymbolPlacement,
	CurrencyPrecision,
	IsBaseCurrency,
	TO_CHAR( CurrencyRate.LastModifiedDate, 'YYYY-MM-DD HH:MI:SS' ) AS LastModifiedDate
FROM
	Currency
WHERE
	( IsInactive = 'F' )
ORDER BY
	Symbol

Notice that I'm using the TO_CHAR formula to format the LastModifiedDate column. This returns the details of the timestamp, including the hours, minutes, and seconds. For more information on the TO_CHAR function, and on working with dates and times in general, see this post from last December.

The CurrencyRate Table

The ExchangeRate value that is provided via the Currency table is the most recent exchange rate between the currency and the account's default currency. In some cases, you might find the need for historical exchange rates, such as for reporting purposes. Those are available via the CurrencyRate table. Here's a query that you can use to retrieve exchange rates for a specified date.

	
SELECT	
	BaseCurrency.Symbol AS BaseSymbol,
	TransactionCurrency.Symbol AS TransactionSymbol,
	CurrencyRate.ExchangeRate,
	TO_CHAR( CurrencyRate.EffectiveDate, 'YYYY-MM-DD HH:MI:SS' ) AS EffectiveDate,
	TO_CHAR( CurrencyRate.LastModifiedDate, 'YYYY-MM-DD HH:MI:SS' ) AS LastModifiedDate
FROM
	CurrencyRate
	INNER JOIN Currency AS BaseCurrency ON
		( BaseCurrency.ID = CurrencyRate.BaseCurrency )
	INNER JOIN Currency AS TransactionCurrency ON
		( TransactionCurrency.ID = CurrencyRate.TransactionCurrency )		
WHERE
	( CurrencyRate.EffectiveDate = TO_DATE( '2021-05-03', 'YYYY-MM-DD' ) )
ORDER BY
	BaseCurrency.Symbol,
	TransactionCurrency.Symbol,
	CurrencyRate.LastModifiedDate DESC

In this query, I'm joining from the CurrencyRate to the Currency table, and I'm doing it twice - once to get the base currency, and another to get the transaction currency.

Performing Currency Conversions

Let's suppose that you want to convert a transaction amount from one currency to another. There are a couple of ways to do that.

First, we can take the transaction's amount ("foreigntotal") and multiple it by the exchange rate that is stored with the transaction. That rate is the exchange rate that was in effect on the date of the transaction, and it's the rate between the transaction's currency and the account or subsidiary's standard currency.

For example, suppose that we have a transaction whose currency is Canadian Dollars, and we want to convert it to US Dollars. We can perform that conversion using a query like this:

	
SELECT 
	TranDate,
	ForeignTotal, 
	Currency AS TransCurrencyID, 
	BUILTIN.DF(currency) TransCurrencyName, 	
	ExchangeRate,	
	( ForeignTotal * ExchangeRate ) AS BaseTotal
FROM
	Transaction 
WHERE 
	ID = 2668382

Another way to perform a currency conversion on transaction values is to use the SuiteQL's CURRENCY_CONVERT built-in function. In the Help Center, the CURRENCY_CONVERT function is described as "converts a currency amount stored in a field to a target currency using the exchange rate that was in effect on a specific date."

CURRENCY_CONVERT takes the field name that you want to convert as its first parameter, and two optional parameters: The target currency, and the date of the exchange rate to use. If you don't specify a target currency, then it is assumed that you want to convert to the currency of the subsidiary. And if you don't specify a date, then it is assumed that you want to use the current day's rate.

Here's a modified version of the previous query, which includes a call to the CURRENCY_CONVERT function.

	
SELECT 
	TranDate,
	ForeignTotal, 
	Currency AS TransCurrencyID, 
	BUILTIN.DF(currency) TransCurrencyName, 	
	ExchangeRate,	
	( ForeignTotal * ExchangeRate ) AS BaseTotal,
	BUILTIN.CURRENCY_CONVERT( ForeignTotal, 2, TranDate ) AS BaseTotal2
FROM
	Transaction 
WHERE 
	ID = 2668382

If you run that query, you should find that the BaseTotal, which is based on the transaction's ForeignTotal and the ExchangeRate values, is equal to BaseTotal2, which uses the CURRENCY_CONVERT function.

CURRENCY_CONVERT really comes in handy when you want to convert an amount to an exchange rate based on an alternative date. For example, to convert the currency based on the rate that was in effect on May 1, 2021, you can do something like this:

	
SELECT 
	TranDate,
	ForeignTotal, 
	Currency AS TransCurrencyID, 
	BUILTIN.DF(currency) TransCurrencyName, 	
	ExchangeRate,	
	( ForeignTotal * ExchangeRate ) AS BaseTotal,
	BUILTIN.CURRENCY_CONVERT( ForeignTotal, 2, TranDate ) AS BaseTotal2,
	BUILTIN.CURRENCY_CONVERT( foreigntotal, 2, TO_DATE( '2021-05-01', 'YYYY-MM-DD' )) AS BaseTotalAlt
FROM
	Transaction 
WHERE 
	ID = 2668382

I've added a second call to the CURRENCY_CONVERT function, and this time I'm passing in a specific date to be used.

Wrapping Up

In this post, I've shared some of the currency-related things that I've discovered while working with SuiteQL. I hope you find it to be helpful.

If you have any questions about SuiteQL, please feel free to reach out to me, or post your question to the SuiteQL channel on the NetSuite Professionals Slack Community.