Published on March 29, 2021.

A frequent question that I get from other NetSuite developers involves the use of SuiteQL to get related transactions. Specifically, they want to know if it is possible to get a transaction's source transaction, or a transaction's child transactions. For example, in the case of a Sales Order, they want to query for related transactions that were based on the order, such as item fulfillments, invoices, and so on.

The NextTransactionLineLink and PreviousTransactionLineLink Tables

There are two tables that NetSuite provides that make type of query is possible. One is the NextTransactionLineLink, and the other is PreviousTransactionLineLink. These are essentially join tables between a source transaction and a destination transaction. More specifically, it's a join between the lineitems on the source and destination transactions.

The two tables are essentially the same, and it really doesn't matter which one use. However, I like to use the NextTransactionLineLink table when I'm trying to get a transaction's child transaction (i.e. its "next" transactions), and I use the PreviousTransactionLineLink when I'm working with a child transaction and need to get its parent transaction.

Here are the columns that you'll find in the NextTransactionLineLink table:
• PreviousType: The previous transaction's type. (ex: SalesOrd)
• PreviousLine: The line number of the previous transaction that maps to the next transaction's line.
• PreviousDoc: The previous transaction's ID.
• NextType: The next transaction's type. (ex: ItemShip)
• NextDoc: The next transaction's ID.
• LinkType: The relationship, or "link type," between the transaction. (ex: ShipRcpt)
• LastModifiedDate: The date that the link was established.
• ForeignAmount: The amount of the lineitem.

Example Queries

Let's take a look at a query that uses the NextTransactionLineLink table. In this example, I'm using the ID of a transaction to get its related ("next") transactions. In this particular case, that transaction is a Sales Order.

SELECT DISTINCT
	NT.ID,
	NT.TranDate,
	NT.Type,
	NT.TranID,
	NT.Status,
	NT.ForeignTotal
FROM
	NextTransactionLineLink AS NTLL
	INNER JOIN Transaction AS NT ON
		( NT.ID = NTLL.NextDoc )
WHERE
	( NTLL.PreviousDoc = 1000348 )
ORDER BY
	NT.ID

Notice that I'm filtering on the PreviousDoc column. This is the ID of the source transaction. In other words, it's the ID of the transaction whose child transactions I am searching for.

Also notice that I'm using the DISTINCT operator. Remember that the NextTransactionLineLink table (as well as the PreviousTransactionLineLink table) is a lineitem-level join table. I'm using DISTINCT to prevent duplicate rows, which you would get if there are multiple lineitems involved.

I'm joining to the Transaction table based on the NextDoc value, and aliasing it as NT (for "next transaction").

Here's an example of the results.

That's good, but we can certainly improve it. For example, let's clean up the next transaction's type and status.

We can map the transaction type to a friendlier value using the BUILTIN.DF function (which I first wrote about in this post). That one is easy.

We can also use BUILTIN.DF to map the next transaction's status.

Here's the revised query.

SELECT DISTINCT
	NT.ID,
	NT.TranDate,
	BUILTIN.DF( NT.Type ) AS Type,
	NT.TranID,
	BUILTIN.DF( NT.Status ) AS Status,
	NT.ForeignTotal
FROM
	NextTransactionLineLink AS NTLL
	INNER JOIN Transaction AS NT ON
		( NT.ID = NTLL.NextDoc  )
WHERE
	( NTLL.PreviousDoc = 1000348 )
ORDER BY
	NT.ID

And here's an example of the results.

That's a little better, but we can make one more improvement to the Status. As you can see, when using the BUILTIN.DF on a transaction's status, the result is the status with the document type as a prefix. For example, "Item Fulfillment : Shipped." We can use the Oracle REPLACE function to remove the prefix.

Here's the final version of the query.

SELECT DISTINCT
	NT.ID,
	NT.TranDate,
	BUILTIN.DF( NT.Type ) AS Type,
	NT.TranID,
	REPLACE( BUILTIN.DF( NT.Status ), BUILTIN.DF( NT.Type ) || ' : ' ) AS Status,
	NT.ForeignTotal
FROM
	NextTransactionLineLink AS NTLL
	INNER JOIN Transaction AS NT ON
		( NT.ID = NTLL.NextDoc  )
WHERE
	( NTLL.PreviousDoc = 1000348 )
ORDER BY
	NT.ID

Here's an example of the results.

Final Thoughts

In this post I've shown how you can use the NextTransactionLineLink to query for a transaction's child transactions. With the PreviousTransactionLineLink table and similar logic, you could query for a transaction's parent transaction.

When using the NextTransactionLineLink and PreviousTransactionLineLink tables, remember that you're really working with lineitem-level join tables. That can be helpful in cases where you are trying to map between the lineitems of parent and child transactions. But it can also make working with these tables a little tricky at times.

With regards to the NextTransactionLineLink table:
• PreviousDoc is the internal ID of the source transaction. For example, the ID of a sales order.
• NextDoc is the internal ID the destination transaction. For example, the ID of a customer invoice, a customer deposit, or an item fulfillment record.

The PreviousTransactionLineLink table is essentially a mirror image of NextTransactionLineLink.

I hope you find this post, and the queries that I've shared, to be helpful. If you have any questions, please feel free to contact me.