Published on August 18, 2022.

Many of the questions that I get from NetSuite users regarding SuiteQL involve related transactions. I've written about this in the past (see "Use SuiteQL to Get Related Transactions"), and shown how you can use the NextTransactionLineLink and PreviousTransactionLineLink Tables to join related transactions.

In this post, I'll show another example of how to use SuiteQL to work with related transactions. Specifically, I'm going to show how you can build a query that joins a Vendor Bill to its related Purchase Order - at both the header and lineitem levels.

The Initial Query

I'm going to start with a very basic query, and then build on it.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor
FROM
	Transaction
WHERE
	( Transaction.ID = 11272022 )

In this example, 11272022 is the ID of a Vendor Bill.

Adding Transaction Lines

Next, I'll join to the TransactionLine table to get the Vendor Bill's lineitems.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	TransactionLine.Item AS ItemID,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	BUILTIN.DF( TransactionLine.ItemType ) AS ItemType,
	TransactionLine.Memo,
	TransactionLine.Quantity,
	TransactionLine.Rate
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'F' )
WHERE
	( Transaction.ID = 11272022 )

Notice that I'm filtering out the TransactionLine that serves as the "Mainline," because it isn't needed.

Joining to PreviousTransactionLineLink

In this example, I'm starting with a Vendor Bill, and want to work back to its source PO. I know the ID of the Vendor Bill transaction. Therefore, I'm going to use the PreviousTransactionLineLink table to get to the PO.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	TransactionLine.Item AS ItemID,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	BUILTIN.DF( TransactionLine.ItemType ) AS ItemType,
	TransactionLine.Memo,
	TransactionLine.Quantity,
	TransactionLine.Rate,
	PTLL.LinkType,
	PTLL.PreviousType,
	PTLL.PreviousDoc,
	PTLL.PreviousLine
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'F' )
	INNER JOIN PreviousTransactionLineLink AS PTLL ON
		( PTLL.NextDoc = TransactionLine.Transaction )
		AND ( PTLL.NextLine = TransactionLine.ID )
WHERE
	( Transaction.ID = 11272022 )

Notice that the join to PreviousTransactionLineLink is based on its NextDoc and NextLine columns. Again, we're working from a transaction (a Vendor Bill) back to its source (a Purchase Order). So the values used for NextDoc and NextLine are based on the ID of the Vendor Bill and the ID of the Vendor Bill line.

I've added a few columns to the SELECT statement to show values for the previous transaction. In my example, I ended up with 3 records. The PreviousType values for all records was "PurchOrd," so I know that I'm joining to the Purchase Order.

However, one of the records was a link based on a Shipment Receipt (the LinkType was "ShipRcpt") and the others were to the based on the Order Bill (the LinkType was "OrdBill"). So as you'll see in a moment, I'm going to filter that out.

And finally, the PreviousDoc value represents the Transaction ID for the Purchase Order, and the and PreviousLine values represent the IDs of the PO's Transaction Lines. It's those values that I'm going to use to join to the PO and its lines.

But first, let's adjust the query above, so that we're only getting links to the PO based on Vendor Bill. Here's the revised query.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	TransactionLine.Item AS ItemID,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	BUILTIN.DF( TransactionLine.ItemType ) AS ItemType,
	TransactionLine.Memo,
	TransactionLine.Quantity,
	TransactionLine.Rate
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'F' )
	INNER JOIN PreviousTransactionLineLink AS PTLL ON
		( PTLL.NextDoc = TransactionLine.Transaction )
		AND ( PTLL.NextLine = TransactionLine.ID )
WHERE
	( Transaction.ID = 11272022 )
	AND ( PTLL.LinkType = 'OrdBill' )

Joining to the Purchase Order

Next, I'll join to the Purchase Order transaction - and specifically to its header record.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	TransactionLine.Item AS ItemID,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	BUILTIN.DF( TransactionLine.ItemType ) AS ItemType,
	TransactionLine.Memo,
	TransactionLine.Quantity,
	TransactionLine.Rate,
	PT.Type AS PTType,
	PT.TranDate AS PTDate,
	PT.ID AS PTID,
	PT.TranID AS PTTranID
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'F' )
	INNER JOIN PreviousTransactionLineLink AS PTLL ON
		( PTLL.NextDoc = TransactionLine.Transaction )
		AND ( PTLL.NextLine = TransactionLine.ID )
	INNER JOIN Transaction AS PT ON
		( PT.ID = PTLL.PreviousDoc )
WHERE
	( Transaction.ID = 11272022 )
	AND ( PTLL.LinkType = 'OrdBill' )

As I mentioned above, to join to the PO, I'm using the PreviousDoc value obtained from the PreviousTransactionLineLink record.

Adding the Purchase Order Lines

The final step is to join to the PO's lineitems.

SELECT	
	Transaction.Type,
	Transaction.TranID,
	BUILTIN.DF( Transaction.Entity ) AS Vendor,
	TransactionLine.Item AS ItemID,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	BUILTIN.DF( TransactionLine.ItemType ) AS ItemType,
	TransactionLine.Memo,
	TransactionLine.Quantity,
	TransactionLine.Rate,
	PT.Type AS PTType,
	PT.TranDate AS PTDate,
	PT.ID AS PTID,
	PT.TranID AS PTTranID,
	PTL.Item AS PTLItem,
	PTL.Quantity AS PTLQuantity,
	PTL.Rate AS PTLRate
FROM
	Transaction
	INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
		AND ( TransactionLine.Mainline = 'F' )
	INNER JOIN PreviousTransactionLineLink AS PTLL ON
		( PTLL.NextDoc = TransactionLine.Transaction )
		AND ( PTLL.NextLine = TransactionLine.ID )
	INNER JOIN Transaction AS PT ON
		( PT.ID = PTLL.PreviousDoc )
	INNER JOIN TransactionLine AS PTL ON
		( PTL.Transaction = PTLL.PreviousDoc )
		AND ( PTL.ID = PTLL.PreviousLine )
WHERE
	( Transaction.ID = 11272022 )
	AND ( PTLL.LinkType = 'OrdBill' )

Again, I'm using the PreviousDoc and PreviousLine from the PreviousTransactionLineLink record to join to the transaction's lines.

Wrapping Up

To join between other record types, you'd follow a similar process. If you're trying to join from a transaction back to a related source transaction, you'd use PreviousTransactionLineLink. However, if you're starting with a source transaction and want to join to transactions that are based on it, you'd likely want to use the NextTransactionLineLink instead.

When it comes to these types of queries, my general advice is to build them gradually. Again, following the process that I described above, start with a good query as a foundation, and build on it, step by step.

As always, I hope you've found this post to be helpful.