Does anyone have a perfect way of determining the currency of the

transactionaccountingline.amount

field? I feel like the data may come from a lot of different tables depending on the features enabled in the netsuite account

@Marc Reicher I think it's just the Currency value at the Transaction header level.

SELECT
	TAL.Amount,
	Currency.Symbol,
	Currency.ExchangeRate AS Rate_Current,
	Transaction.ExchangeRate AS Rate_Trans
FROM
	Transaction
	INNER JOIN TransactionAccountingLine AS TAL ON
		( TAL.Transaction = Transaction.ID )
	INNER JOIN Currency ON
		( Currency.ID = Transaction.Currency )
WHERE
	Transaction.ID = 2466063

@tdietrich so let's say you have a USD subsidiary with a GBP transaction. My understanding is: • the

transaction.currency

would be GBP • the

transactionline.foreignamount

would be in GBP • the

transactionaccountingline.amount

would be in USD I don't believe the

transaction

, the

transactionline

, or the

transactionaccountingline

has a field that shows the currency of the

transactionaccountingline.amount

field (though I could be wrong). I know I could join on the

subsidiary

for some NetSuite accounts (and just look at the

subsidiary.currency

value), but there are two other interesting edge cases where this strategy wouldn't work: • NetSuite accounts without subsidiaries (i.e. non NetSuite OneWorld) but with multi-currency enabled • NetSuite accounts with subsidiaries but without multi-currency enabled In those two situations, I'm not sure where I'd grab the currency

I see what you mean, and yeah, it gets complicated pretty quickly. I'm not sure if this will help, but I see "currency" in at least 32 different tables:

account
AccountingBookSubsidiaries
amortizationSchedule
billingAccount
BillingRateCardPriceMultiForSearch
budgets
charge
customer
customerCurrencyBalance
customerItemPricing
employee
EmployeeExpenseReportCurrencies
ExpenseCategoryRate
expenseMachine
fairValuePrice
InvoiceGroup
invtItemPriceHistory
job
memDocTransactionTemplate
otherName
priceBook
pricePlan
pricing
ProjectIcChargeRequest
promotionCode
promotionCodeCurrency
revenueElement
subscription
subsidiary
subsidiaryAccountingBookDetail
transaction
vendor
vendorCurrencyBalance

Oh that's a clever way of look at the problem hah. Not sure if it will be in there though. And do you know if there is a table that shows the features that are enabled on an account? I know something like that exists in suitescript, but not sure if it's accessible via suiteql

Also, I haven't identified a table that provides an account's default settings. However, you might be able to use the N/config module to get some of the values that you'd need to programmatically determine the features that are enabled, etc. For example, there's a "multicurrency" value exposed.

I just tried another trick that you (or maybe someone else in this channel) taught me. I know there is a spot in the UI that shows the piece of data I want https://{account_id}.app.netsuite.com/app/common/otherlists/company.nl?whence= So then I added

&xml=true

to the end of the URL https://{account_id}.app.netsuite.com/app/common/otherlists/company.nl?whence=&xml=true Interestingly enough, this revealed that the company settings page is actually rendering a subsidiary record (see screenshot)? Any chance you have access to a NetSuite account that does not have subsidiaries enabled? I wonder what

recordType

would be listed in the xml

I sure do. Let me look...

such a bummer. And this is an account without subsidiaries right? My guess is a

select * from subsidiary

will fail for that customer

I thought that, too. But sure enough, it returns a single record.

Hmm that's really interesting. Are you using an admin role?

Yeah.

In that NetSuite account, if you go to create a new role, is there actually a permissions option for Lists => Subsidiaries? If not, then my guess is behind the scenes, all NetSuite accounts have one subsidiary, but on a non OneWorld account, perhaps only admins can actually view the data in SuiteQL

No, it's not an option.

Wow ok yeah then that's really interesting

And I think you're right about the "one subsidiary" theory.

We have a lot of clients where that permission is not an option, so the

select * from subsidiary

query always fails. I never even considered that it would work if you ran it as an admin

Also...

That's really interesting. Well I guess then the

subsidiary

join is the answer after all. It just won't work for non OneWorld accounts unless an admin token is used. What a strange thing to have to explain to a customer 🙂

No problem at all. It's an interesting and challenging problem, and the fact that you're unable to tap into the power of SuiteScript makes it even more so.

I was actually just about to ask you about whether that account had multi-currency enabled hah

So it seems like there are some roundabout ways to determine what you're dealing with. But man, it's confusing.

here is another funny question. Can you do a

select currency FROM transaction

on that account?

In RP, I can see that column...

and it's just like an integer? but you can't join it against the currency table?

In RP, yes. In production...

Also to go back to your earlier comment about the RP environment, you mentioned you can't do a

select currency FROM subsidiary

, but if you do a

select * FROM subsidiary

I wonder if the currency field does in fact return. I also wonder if

select * FROM currency

works lol

No.

What a mess haha. I mean that’s crazy that the transaction has a currency column but that you can’t join on the currency table to see what the currency is.

I think we should revisit all of this when 2021.1 goes live. I'd be happy to give it another try.

Absolutely. I think the permissions errors in SuiteQL are still a little wacky. Not surprising given that it’s still relatively new

I agree.

Exactly. There are so many stupid conditionals in our codebase as a result of this haha