Published on December 17, 2020.

One of the questions that I get asked most frequently about SuiteQL involves querying NetSuite transactions. Specifically, developers ask me how to create queries that return results based on a combination of data from transactions, transaction lines, items, and entities. And it recently occurred to me that I haven't written about creating queries that join those tables.

In this post, I'll walk through the process of creating a SuiteQL query that returns data from those tables. I'll start with the Transaction table, and then join to Entities, Transaction Lines, and Items. Along the way, I'll discuss a few helpful functions, including "TO_DATE" and "BUILTIN.DF." I'll also show how you can replicate the mainline search filter, and briefly discuss querying for journal entry transactions.

The Transactions Table

We'll start with a simple query that pulls records from the Transaction table.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Type,
	Transaction.Status,
	Transaction.Entity,
	Transaction.Employee
FROM
	Transaction
WHERE
	( Transaction.ID = 107796 )

In the WHERE clause, I'm indicating that I want a specific transaction returned, and I've specified a transaction ID to do so. That ID is NetSuite's internal ID for the transaction.

However, you can also query for a transaction based on its transaction number (such as a sales order number or purchase order number). Here's a modified version of that query as an example.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Type,
	Transaction.Status,
	Transaction.Entity,
	Transaction.Employee
FROM
	Transaction
WHERE
	(Transaction.TranID = 'SO312969' )

What about querying for transactions that occurred on a specific date? Again, we can update the WHERE clause to do that.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Type,
	Transaction.Status,
	Transaction.Entity,
	Transaction.Employee
FROM
	Transaction
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )

Note that I'm using the "TO_DATE" function to specify the date value. For more information on that function, see my post on "Working With Dates and Times in SuiteQL Queries."

And finally, what if we want to query for all transaction that occurred in a date range? Here's how we can do that.

	 
SELECT
	Transaction.TranID,
	Transaction.TranDate,
	Transaction.Type,
	Transaction.Status,
	Transaction.Entity,
	Transaction.Employee
FROM
	Transaction
WHERE
	 ( Transaction.TranDate BETWEEN TO_DATE( '2020-12-01', 'YYYY-MM-DD' ) AND TO_DATE( '2020-12-31', 'YYYY-MM-DD' ) )

The BUILTIN.DF Function and Column Aliases

If you look at the responses to the previous queries, you might notice that the values for things like status, entity, and employee are somewhat less than helpful. The Status value is simply a single letter, and it is unclear what the value represents. The Entity and Employee values are integers, and again, it isn't clear what they represent. And the Type column is somewhat helpful, with values such as SalesOrd, PurchOrd, VendBill, and so on. But even those values could be more clear.

This is where the BUILTIN.DF function can come in handy. I've written about this function in previous posts (most recently in "SuiteQL Built-In Functions Revisited"). Basically, it gives us an easy way to map an encoded value to a more readable, usable, human-friendly value. For example, when using it on the Status column, a value of "G" on a sales order transaction will return "Sales Order : Billed." When using it on the Type column, "SalesOrd" becomes "Sales Order."

Similarly, when you use the BUILTIN.DF function on columns that represent the IDs of related tables, the function returns a text value describing the related record. (For database-savvy readers, it is essentially returning an alternate primary key.)

Here's an updated query showing the use of the BUILTIN.DF function.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	BUILTIN.DF( Transaction.Employee ) AS Employee
FROM
	Transaction
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )

Notice that in the SELECT clause, for the columns that I'm using the BUILTIN.DF function on, I've aliased the column names. For example, "BUILTIN.DF( Transaction.Type ) AS Type." When using a function on a column, SuiteQL will return the value as EXPR1, EXPR2, and so on (the "expr" value being short for "expression"). By aliasing the columns, you get a result that is easier to work with.

The Entity and Employee Tables

I've shown how you can use the BUILTIN.DF function to map the IDs of columns like Entity and Employee from their numeric record IDs to values that are easier to work with. But what if there are other values that we need from the related tables?

In that case, we need to join from the the Transaction table to the related tables. Let's look at a query that does that.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	BUILTIN.DF( Transaction.Employee ) AS Employee,
	BUILTIN.DF( Entity.Type ) AS EntityType,
	BUILTIN.DF( Entity.Phone ) AS EntityPhone,
	Employee.Title AS EmployeeTitle,
	Employee.Phone AS EmployeePhone,	
	Employee.Email AS EmployeeEmail
FROM
	Transaction
	INNER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	LEFT OUTER JOIN Employee ON
		( Employee.ID = Transaction.Employee )
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )

Notice in the WHERE clause that I'm joining to the Entity and Employee tables. In the case of the Entity table, I'm using an INNER JOIN, which indicates that I only want rows returned where a related record exists in the Entity table.

However, in the join to the Employee table, I'm using a LEFT OUTER JOIN. This indicates that I want rows returned even if there is no related record in the Employee table. This is possible, because some transaction types (such as Customer Deposits, Purchase Orders, Item Receipts, and so on) do not have an employee associated with them. In some cases, such as Sales Orders, there is an employee associated with the transaction . (In the case of a sales order, the related employee is the salesperson.)

Also notice that in the SELECT clause, I'm aliasing more of the columns. I'm doing this to avoid confusion in cases where the column names are the same in one or more tables. For example, we're requesting the Phone columns from both the Entity and Employee tables. Without the aliases, those values would be returned as "phone" and "phone_0," and it isn't clear as to what value is associated with each table.

The TransactionLine Table

At this point, the query returns values that can be thought of as being at the header level of the transactions. Now we're ready to join to the TransactionLine table.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	BUILTIN.DF( Transaction.Employee ) AS Employee,
	BUILTIN.DF( Entity.Type ) AS EntityType,
	BUILTIN.DF( Entity.Phone ) AS EntityPhone,
	Employee.Title AS EmployeeTitle,
	Employee.Phone,	
	Employee.Email AS EmployeeEmail,
	BUILTIN.DF( TransactionLine.Item ) AS Item,
	TransactionLine.Quantity,
	TransactionLine.Rate
FROM
	Transaction
	INNER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	LEFT OUTER JOIN Employee ON
		( Employee.ID = Transaction.Employee )
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )

In this query, I'm joining from the TransactionLine to the related Item record, and doing so with an INNER JOIN. I've added a few columns, sourced from the TransactionLine table, to the SELECT clause. And I'm using the BUILTIN.DF function on the TransactionLine.Item column to get the name of the related item.

Replicating the Mainline Search Filter

Depending on your query and data, you might notice that when you join to the TransactionLine table, the query response includes multiple rows for a single transaction. For example, if a transaction has 3 lineitems associated with it, then the transaction will be included three times in the response - one row for each related transaction line.

If you are familiar with transaction searches, then you're probably also familiar with the "mainline" search filter. When using that filter, the duplicate transactions are removed. ( For more information on the mainline search filter, check out Eric Grubaugh's blog post, "Main Line for Transaction Searches."

It is possible to replicate the mainline filter concept in SuiteQL, and it's easy to do. Here's an example.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	BUILTIN.DF( Transaction.Employee ) AS Employee,
	BUILTIN.DF( Entity.Type ) AS EntityType,
	BUILTIN.DF( Entity.Phone ) AS EntityPhone,
	Employee.Title AS EmployeeTitle,
	Employee.Phone,	
	Employee.Email AS EmployeeEmail,
	BUILTIN.DF( TransactionLine.Item ) AS Item,
	TransactionLine.Quantity,
	TransactionLine.Rate,
	Item.ItemID,
	BUILTIN.DF( Item.ItemType ) AS ItemType
FROM
	Transaction
	INNER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	LEFT OUTER JOIN Employee ON
		( Employee.ID = Transaction.Employee )
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )
	AND ( TransactionLine.MainLine = 'T' )

Notice that I've added a "TransactionLine.MainLine = 'T'" condition to the WHERE clause. That's all there is to it.

The Item Table

Suppose that we want the query result to include additional information about the related items. We can do that by adding another join, this time to the Item table.

SELECT
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	BUILTIN.DF( Transaction.Status ) AS Status,
	BUILTIN.DF( Transaction.Entity ) AS Entity,
	BUILTIN.DF( Transaction.Employee ) AS Employee,
	BUILTIN.DF( Entity.Type ) AS EntityType,
	BUILTIN.DF( Entity.Phone ) AS EntityPhone,
	Employee.Title AS EmployeeTitle,
	Employee.Phone,	
	Employee.Email AS EmployeeEmail,
	BUILTIN.DF( TransactionLine.Item ) AS Item,
	TransactionLine.Quantity,
	TransactionLine.Rate,
	Item.ItemID,
	BUILTIN.DF( Item.ItemType ) AS ItemType
FROM
	Transaction
	INNER JOIN Entity ON
		( Entity.ID = Transaction.Entity )
	LEFT OUTER JOIN Employee ON
		( Employee.ID = Transaction.Employee )
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
	INNER JOIN Item ON
		( Item.ID = TransactionLine.Item )
WHERE
	( Transaction.TranDate = TO_DATE('2020-12-01', 'YYYY-MM-DD' ) )

In this query, I'm joining to the Item table using an INNER JOIN, and I've added a few columns from the Item table to the SELECT clause.

Journal Entry Transactions

In most of the queries above, I've assumed that we want to query for transactions that involve items, such as sales orders, purchase orders, and so on. But the Transaction table is also used to store transactions that do not involve items, such as journal entries.

Back in November, I wrote about how you can "Use SuiteQL to Access Journal Entries and Determine GL Impact." If you look at the queries that I shared in that post, you'll see that instead of joining from the Transaction table to the TransactionLine table, I'm joining to the TransactionAccountingLine table instead. It's a simple, but very important difference.

If you're curious about querying for journal entires, I encourage you to read that post.

Wrapping Up

This was somewhat of a "deep dive" into querying transactions with SuiteQL, and we covered a lot of important topics. However, there's still a lot more to know, and I'll do my best to follow-up with related posts in the weeks and months ahead.

In the meantime, if you have any questions about SuiteQL, please feel free to contact me. I'm booked up through the end of 2020, but will be providing one-hour support calls starting in January 2021.