Published on November 16, 2021.

One of the SuiteQL-related questions that I get asked the most often by other NetSuite developers has to do with getting related transactions. I've written about this topic before, and shown how you can use SuiteQL to query the NextTransactionLineLink and PreviousTransactionLineLink tables to get related transactions.

However, depending on what you're trying to do and how your NetSuite account is configured, there is a potentially easier way to get related transactions. It involves using a column that's available via the TransactionLine table.

I was recently asked by a client to add support for related transactions to their custom Client Portal, and I used this technique to fulfill their request. I thought I'd share the technique in this post.

The Portal

Before I get started with the query, here's an animation that shows the portal, and what the related transactions look like.

Click the image to view a larger version.

As you can see, the portal is designed to provide customers with easy access to their existing orders. It also gives them the ability to place reorders, and access to product information, specials, etc.

My client's request was to give their customers an easy way to see transactions that are related to a sales order, such as invoices or item fulfillments. It's very similar to the "Related Records" function in NetSuite itself, as shown in this screen shot.

The Query

As I mentioned above, the key to this technique is a column on the TransactionLine table. Its name is CreatedFrom, and the value that it contains is the ID of the transaction from which the transaction line was generated. For example, in the case of an invoice or item fulfillment transaction, the value will be the ID of the Sales Order that the transaction was generated from.

If you know the ID of a transaction, and want to get the IDs of its related transactions, you can simply query the the TransactionLine table like this.

SELECT DISTINCT
	TransactionLine.Transaction
FROM
	TransactionLine
WHERE
	( TransactionLine.CreatedFrom = 7801 )

In this example, the values returned are the IDs of the transactions that are related to Sales Order with an internal ID of 7801.

We can then use that query as a subquery to get additional information about the related transactions, such as their dates, types, statuses, and amounts. Here's a revised query that demonstrates the technique.

SELECT
	Transaction.ID,
	Transaction.TranDate,
	BUILTIN.DF( Transaction.Type ) AS Type,
	Transaction.TranID,
	REPLACE( BUILTIN.DF( Transaction.Status ), BUILTIN.DF( Transaction.Type ) || ' : ', '' ) AS Status,
	Transaction.ForeignTotal
FROM
	Transaction
WHERE
	Transaction.ID IN (
		SELECT DISTINCT
			TransactionLine.Transaction
		FROM
			TransactionLine
		WHERE
			( TransactionLine.CreatedFrom = 7801 )
	)

And that's all there is to it. The results will look like this.

Wrapping Up

I hope you find the query that I've shared in this post to be helpful. If you have any questions about it, please feel free to reach out to me.