SuiteQL: Transactions

Introduction

SELECT transaction.tranid from Transaction

Many if not most of your queries will start from the Transaction table.

While more joins are usually necessary to get the information you want, the transaction table contains most of the building blocks that make up the data in NetSuite.

Record vs Transaction

It is important to understand the difference between a transaction and a record. A transaction represents an “event” that happened in NetSuite. Some of the them will have GL entries posted on that date. Examples include:

Purchase Order
Invoice
Sales Order
Work Order
Assembly Build
Work Order Close

and others. All these records, as far as SuiteQL is concerned, are stored in the Transaction table.

We can differentiate between the different types of transactions by looking at the type column.

SELECT DISTINCT
transaction.type from Transaction

This returns:

Type
ItemRcpt
CardChrg
PurchOrd
SalesOrd
…

As you can see, the fields are human readable-ish but we can get a clearer sense of what they represent by using the BUILTIN.DF function.

SELECT DISTINCT
BUILTIN.DF(transaction.type) as type from Transaction

This returns:

Type
Item Receipt
Credit Card
Purchase Order
Sales Order
…

A record represents a piece of data stored in NetSuite. It doesn’t matter when it was entered, only that at the time you referenced it on a transaction or another record it existed in the system.

Examples or records include:

Item
Lot Number
Bin
Bill of Materials
Routing
Customer

and others. Generally each record is stored in a separate table in SuiteQL.

Transaction Table

This table stores the body fields of a transaction.

Here are some common columns you might query:

Column Name	Description	Type
tranid	Document Number	String
trandate	The date entered on the transaction. (Note that the user can change this date)	Date
createddate	The date the transaction was created.	Date
type	The type of transaction.	String (List)
status	The status of the transaction.	String (List)
memo	The memo field.	String
entity	The entity the transaction is for.	Integer (Internal ID)
location	The location the transaction is for.	Integer (Internal ID)
subsidiary	The subsidiary the transaction is for.	Integer (Internal ID)

Generally the column name is the same as the internal id of the field.

TransactionLine Table

Suppose we wanted to get the items on a purchase order.

We can try:

SELECT
Transaction.tranid,
Transaction.item FROM
Transaction
WHERE Transaction.type = 'PurchOrd'

But running this query results in an error:

Search error occurred: Field 'item' for record 'transaction' was not found.

This is because there are multiple lines for a transaction so we must store that information in a separate table TransactionLine

The join is as follows:

FROM Transaction
INNER JOIN TransactionLine on TransactionLine.transaction = Transaction.id

where TransactionLine.transaction and Transaction.id is the Transaction’s internal id.

Lets try getting the item now:

SELECT
Transaction.tranid,
TransactionLine.item
FROM Transaction
INNER JOIN TransactionLine ON TransactionLine.transaction = transaction.id

Running this produces the following results:

Tranid	Item
WO234	323
SO523	2432
SO523	8943
JE342	null
…	…

Lets notice a few things:

1. Right now the item column refers to the internal id of the item. If we want the item name we need to use a builtin function called BUILTIN.DFor join with the item table.
2. The item column is null for some transaction types such as journal entries.
3. We now have multiple rows for transactions that contain multiple items.

Quantity

There are two important things to note about the column quantity on the table TransactionLine.

1. The quantity is always in the base unit of measure. If the base unit is Pounds (lb) even if the units on the line are in Case (50 lb) the quantity will always be in pounds. If the transaction line looks like 5 Case (50 lb) the quantity will be 50 * 5 = 250.

2. The quantity is sometimes negative even if on the UI it shows it as positive. This happens when the transaction is subtracting inventory from the total. Here are some examples of transactions and whether the column quantity is positive or negative:

Type	P/N
Sales Order	Positive
Work Order	Negative
Item Receipt	Positive
Item Fulfillment	Negative
Invoice	Negative

Sample Query | Sales By Location per month per Item

This is an easy task to do with a saved search, but I will write it with a query to demonstrate the use of the Transaction and TransactionLine tables.

Before I do any grouping I will pull the relevant transactions into a CTE. For more information on CTEs see the Oracle documentation

This may not be efficient but it is easier to understand for the purposes of this blog.


First lets get the transactions we want to group:

```sql
SELECT
transaction.tranid,
transaction.trandate,
TO_CHAR(transaction.trandate, 'Month YYYY') AS month,
BUILTIN.DF(transactionline.item) as item,
inventoryassignment.quantity
where transaction.type = 'SalesOrd'

The TO_CHAR function will convert a date, say 04/01/2020 to a string like April 2020. For more information see the documentation

I also include two columns that we won’t need in the final query (trandate and tranid) but when constructing queries it is useful to include them in the intermediate steps to verify that you are pulling the right data.

When running the query in your editor you might want to limit the results as it can return many transactions.

Now we pull the transactions into a CTE and do the grouping:

WITH salestransactions AS (
    SELECT
    transaction.tranid,
    transaction.trandate,
    TO_CHAR(transaction.trandate, 'Month YYYY') AS month,
    BUILTIN.DF(transactionline.item) AS item,
    transactionline.quantity
    WHERE transaction.type = 'SalesOrd'
)
SELECT
salestransactions.month,
salestransactions.item,
SUM(salestransactions.quantity)
GROUP BY
salestransactions.month,
salestransactions.item

Conclusion

This is a very basic introduction to transactions. Transactions make up the core of NetSuite. The general ledger, inventory, financial statements, are all based on transactions and as such, to query them with SuiteQL you must query the transaction tables.

The next post will discuss inventory detail on transactions and how we can use the Inventoryassignment table to query inventory detail such as lot numbers and bins.