Published on June 27, 2022.

A NetSuite topic that I get a lot of questions about is shipments. I'm often asked for assistance by developers who are trying to use SuiteQL to integrate NetSuite with external systems to provide shipment information, tracking numbers, and so on.

I think one of the reasons that developers struggle with shipments is that there are so many shipping solutions being used by NetSuite customers. But in this post, I'm going to provide some queries that should work in nearly all NetSuite instances, regardless of what shipping system is actually being used.

Item Fulfillments

Let's start by querying the table that the core shipment information is stored in. In NetSuite, a shipment is actually a type of transaction known as an Item Fulfillment. The internal transaction type for Item Fulfillments is "ItemShip."

This query returns all Item Fulfillment transactions that occurred in last 30 days.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	Transaction.TrackingNumberList
FROM
	Transaction
WHERE
	( Transaction.Type = 'ItemShip' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

Notice the use of the BUILTIN.RELATIVE_RANGES function in the WHERE clause. I first wrote about that function in this post. In this example, I'm specifying that I want the date "30 days ago." And the nice thing about using the BUILTIN.RELATIVE_RANGES function is that the query will always return transactions based on that dynamic date range.

Tracking Numbers

One of the columns being returned in that first query is TrackingNumberList. This is an interesting column, and it can be confusing.

The TrackingNumberList column contains a comma-delimited list of the related records in the TrackingNumber table. The values in the list are NetSuite's internal IDs of the TrackingNumber records, not the actual tracking numbers.

Obviously, that's not very helpful. But what's nice about TrackingNumberList column - and where it becomes very helpful - is that you can wrap it in the BUILTIN.DF function to get a comma-delimited list of the tracking numbers. For example:

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	BUILTIN.DF( Transaction.TrackingNumberList ) AS TrackingNumbers	
FROM
	Transaction
WHERE
	( Transaction.Type = 'ItemShip' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

If all that you need is a basic list of the tracking numbers related to a shipment, then that's all you need to do. However, if you need the individual tracking numbers - one per result row - then you'll need to use a more complicated query. For example:

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	TrackingNumber.TrackingNumber
FROM
	Transaction
	INNER JOIN TrackingNumberMap ON
		( TrackingNumberMap.Transaction = Transaction.ID )
	INNER JOIN TrackingNumber ON
		( TrackingNumber.ID = TrackingNumberMap.TrackingNumber)
WHERE
	( Transaction.Type = 'ItemShip' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

In this modified query, I'm joining from the Transaction table to a table named TrackingNumberMap, and from there to the TrackingNumber table (which is where the tracking numbers are really stored). The query will return one row for every related tracking number. (The TrackingNumberMap table is a join table between a record in the Transaction table and one or more records in the TrackingNumber table.)

After looking at the additional joins needed in that second query, the convenience of the TrackingNumberList column becomes more clear. Being able to use the TrackingNumberList column with the BUILTIN.DF function really can save you a lot of work. It just depends on how you need the tracking numbers, and what you're going to do with them.

Shipment Details

There's one more shipment-related table that I want to discuss, and it's the TransactionShipment table. This table is where the address, method, weight, and rates are stored.

To get the TransactionShipment record for a given Item Fulfillment transaction, you query the TransactionShipment table and filter against the "Doc" column with the transaction's internal ID. For example, if the Item Fulfillment transaction has an ID of 999999, then you'd query the table like this.

SELECT
	BUILTIN.DF( TransactionShipment.SourceAddress ) AS ShipFrom,
	BUILTIN.DF( TransactionShipment.DestinationAddress ) AS ShipTo,
	BUILTIN.DF( TransactionShipment.ShippingMethod ) AS ShipMethod,
	TransactionShipment.Weight,
	TransactionShipment.ShippingRate,
	TransactionShipment.HandlingRate
FROM
	TransactionShipment
WHERE
	( TransactionShipment.Doc = 999999 )

As you can see, I'm making liberal use of the BUILTIN.DF function to prevent having to join from the TransactionShipment table to the actual source tables (for the two addresses and the ship method). The query also returns information about the shipment's weight, shipping amount, and handling charge.

Combining the Queries

Let's combine a few of the previous queries to get a more comprehensive result.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	BUILTIN.DF( TransactionShipment.SourceAddress ) AS ShipFrom,
	BUILTIN.DF( TransactionShipment.DestinationAddress ) AS ShipTo,
	BUILTIN.DF( TransactionShipment.ShippingMethod ) AS ShipMethod,
	TransactionShipment.Weight,
	TransactionShipment.ShippingRate,
	TransactionShipment.HandlingRate,
	BUILTIN.DF( Transaction.TrackingNumberList ) AS TrackingNumbers
FROM
	Transaction
	INNER JOIN TransactionShipment ON
		( TransactionShipment.Doc = Transaction.ID )
WHERE
	( Transaction.Type = 'ItemShip' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

With this single query, we're getting nearly all of the important information about a shipment - or in this case, about the shipments made in the last 30 days.

Joining to the Sales Order

There is one additional value that I'm often asked to provide in these shipment-related queries, and that's the customer's purchase order number. To get that, we need to join from the Item Fulfillment transaction back to its source Sales Order transaction.

In order to do that, we have to join to the TransactionLine table, because that's where the ID of the source Sales Order transaction is stored.

Here's the revised query.

SELECT
	Transaction.ID,
	Transaction.TranID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Entity ) AS Customer,
	SalesOrder.TranID AS SONumber,
	SalesOrder.OtherRefNum AS PONumber,
	BUILTIN.DF( TransactionShipment.SourceAddress ) AS ShipFrom,
	BUILTIN.DF( TransactionShipment.DestinationAddress ) AS ShipTo,
	BUILTIN.DF( TransactionShipment.ShippingMethod ) AS ShipMethod,
	TransactionShipment.Weight,
	TransactionShipment.ShippingRate,
	TransactionShipment.HandlingRate,
	BUILTIN.DF( Transaction.TrackingNumberList ) AS TrackingNumbers
FROM
	Transaction
	INNER JOIN TransactionShipment ON
		( TransactionShipment.Doc = Transaction.ID )
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.MainLine = 'T' )
	INNER JOIN Transaction AS SalesOrder ON
		( SalesOrder.ID = TransactionLine.CreatedFrom )
WHERE
	( Transaction.Type = 'ItemShip' )
	AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) )
ORDER BY
	Transaction.TranDate DESC,
	Transaction.TranID DESC

Notice that in the join to the TransactionLine table, I've specified that the MainLine should be true. By doing so, the join is made only to the first transaction line. Therefore, regardless of the number of lineitems on the item fulfillment, it is only included once in the results.

In addition, now that we have access to the source Sales Order, we can include additional information from it in the results. I've included both the sales order number and the customer's PO number, but you could also have included information such as the sales rep, the customer contact that placed the order, and so on. And that's one of the amazing things about SuiteQL: We can join to as many tables as necessary.

Wrapping Up

In this post I've shown how you can use SuiteQL to get information about outbound shipments. The key point to remember is that in NetSuite a shipment is, like most events, treated as a transaction - and is therefore stored in the Transaction table. But there are some interesting related tables that you should know about, including TrackingNumberMap, TrackingNumber, and TransactionShipment. Depending on what you're trying to accomplish, you might need to use those tables as well.