Published on March 3, 2024.

A SuiteQL-related question that I get asked quite frequently from NetSuite developers and administrators involves sales tax. Specifically, they often struggle to get a sales order's sales tax value.

When you view a sales order in the NetSuite UI, the "Tax Total" is presented in the Summary area. Therefore, it's easy to assume that this value would be available in the Transaction table.

If you look in the SuiteQL Query Tool's Tables Reference, you'll see that "taxTotal" is listed as a column on the Transaction table. However, for Sales Orders, the column is usually not actually available.

To get an order's sales tax, you have to dig a little deeper...

One possible solution is to look at a sales order's related TransactionAccountingLine records and sum up the amounts for the lines that are posting to the "Sales Taxes Payable" account.

The query would look something like this.

SELECT
	SUM( Amount * -1 ) AS SalesTax
FROM
	TransactionAccountingLine
WHERE
	( Transaction = 93421254 )
	AND ( BUILTIN.DF( Account ) LIKE '%Sales Tax%' )

However, there's no guarantee that this will be accurate, especially if this is a query that you're going to want to use between different NetSuite instances (which might be configured differently).

A better approach is to query the sales order's related TransactionLine records, and sum up the amounts for the lines that are tax-related.

For example...

SELECT 
	SUM( TransactionLine.ForeignAmount * -1 ) AS SalesTax 
FROM
	TransactionLine 
WHERE
	( TransactionLine.Transaction = 93421254 )
	AND ( TransactionLine.TaxLine = 'T' )

That's a much more accurate and safe way to get the sales tax value - and it should work properly regardless of the NetSuite instance it's being run in.

If you'd like to avoid having to run a separate query just to get a sales order's sales tax, you can use a subquery.

For example, this query returns values from the Transaction table, and includes the Sales Tax value (which is sourced from the related TransactionLine records via a subquery).

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Entity AS CustomerID,
	BUILTIN.DF( Transaction.Entity ) AS CustomerName,
	BUILTIN.DF( Transaction.Status ) AS Status,
	Transaction.ForeignTotal AS Total,
	( 
		SELECT 
			SUM( TransactionLine.ForeignAmount * -1 ) AS SalesTax 
		FROM
			TransactionLine 
		WHERE
			( TransactionLine.Transaction = Transaction.ID )
			AND ( TransactionLine.TaxLine = 'T' )
	) AS SalesTax
FROM 
	Transaction
WHERE 
	( Transaction.Type = 'SalesOrd' )
	AND ( Transaction.ID = 93422549 )

Note the use of a subquery to get the SalesTax, which eliminates the need to run a second query to get that value.

You can find that last query in the SuiteQL Query Tool's "Remote Library." It's listed as "Sales Order - With Sales Tax."

As always, I hope you've found the queries that I've shared to be helpful.