Published on February 3, 2022.

In NetSuite OneWorld accounts, it is possible for general ledger accounts to be associated with multiple subsidiaries. As a result, querying for accounts by subsidiary can be tricky.

For example, consider this query.

SELECT
	ID, 
	DisplayNameWithHierarchy,	
	Subsidiary,
	BUILTIN.DF( Subsidiary ) AS SubsidiaryName
FROM 
	Account

Here's an example of the results.

Click the image to view a larger version.

Notice that in nearly every case, the account's Subsidiary value is multi-valued, and returned as a comma-delimited list of the subsidiaries that the account is associated with. Similarly, the value of the BUILTIN.DF( Account.Subsidiary ) formula is also multi-valued. While this behavior is convenient when you're using the results for display purposes, it can cause issues when you're trying to query on the Subsidiary column.

For example, consider this revised query, where I'm requesting all of the accounts that are associated with the "Australia" subsidiary (which has an ID of 5).

SELECT
	ID, 
	DisplayNameWithHierarchy,	
	Subsidiary,
	BUILTIN.DF( Subsidiary ) AS SubsidiaryName
FROM 
	Account
WHERE
	Subsidiary = 5

SuiteQL responds with this error:

Search error occurred: Invalid or unsupported search 

The AccountSubsidiaryMap Table

To resolve this issue, you need to make use of the table that NetSuite uses to map accounts to subsidiaries. The name of that table is AccountSubsidiaryMap.

Here's a revised query that joins accounts to subsidiaries via the AccountSubsidiaryMap table.

SELECT
	Account.ID, 
	Account.DisplayNameWithHierarchy, 
	AccountSubsidiaryMap.Subsidiary,
	BUILTIN.DF( AccountSubsidiaryMap.Subsidiary ) AS SubsidiaryName
FROM 
	Account
	INNER JOIN AccountSubsidiaryMap ON
		( AccountSubsidiaryMap.Account = Account.ID )
WHERE
	AccountSubsidiaryMap.Subsidiary = 5

As I did earlier, I'm requesting all of the accounts that the "Australia" subsidiary (which has an ID of 5) is associated with.

The query results look like this.

Click the image to view a larger version.

Another option is to simply query the AccountSubsidiaryMap table directly. For example:

SELECT
	Account,
	BUILTIN.DF( Account ) AS AccountName
FROM 
	AccountSubsidiaryMap
WHERE
	Subsidiary = 5

Get A Complete Account Subsidiary Map

Here's a query that you can use to generate a complete account subsidiary map. The results include both the numeric and text names of all of accounts and the subsidiaries that they're associated with.

SELECT
	Account,
	BUILTIN.DF( Account ) AS AccountName,
	Subsidiary,
	BUILTIN.DF( Subsidiary ) AS SubsidiaryName
FROM 
	AccountSubsidiaryMap

This query is available via the SuiteQL Query Tool's "Remote Library" - a free, online, searchable library of SuiteQL queries. The name of this query is: Account Subsidiary Map

Wrapping Up

Depending on the type of financial reporting that you're doing in NetSuite, when working in OneWorld accounts, there are additional things to consider. As I mentioned in this post, the mappings between accounts and subsidiaries is one thing to consider, and using the AccountSubsidiaryMap table can help.

Another issue that you might run into involves currency. If you're in a OneWorld account where multiple currencies are being used, then you'll need to consider currency conversions, revaluations, and so on. I'll discuss those challenges in future posts.