SuiteQL: Transaction Line Mainline

This will be a short post to cover the column mainline on the Transactionline table.

The main line of a transaction is a row on the TransactionLine table that contains certain information about the transaction. Each transaction has exactly one main line. We can check if a transaction line is the main line by checking the mainline column on the TransactionLine table.

If the transaction is a work order or an assembly build, the main line will contain the item being built. On some transactions that don’t have a main line item - such as a sales order, item fullfilment etc. - there will be a Transactionline row that has the mainline column set to true with a null item column.

The reason for NetSuite moving some info to the TransactionLine table is for certain accounting reasons. Specifically when NetSuite gets inventory, internally it is summing up the TransactionLine table and grouping by item (see SuiteQL: Inventory Detail).

So let’s imagine creating an assembly transaction. If we store the assembly item in the Transaction table, as we usually do with other transaction body fields, when NetSuite looks at the TransactionLine table it won’t see the line for the assembly item.

Let’s take a look at the TransactionLine rows for an Assembly:

SELECT
    Transaction.tranid,
    TransactionLine.mainline,
    BUILTIN.DF(TransactionLine.item) AS item,
    Transactionline.quantity
FROM Transaction
    INNER JOIN
    TransactionLine
    ON TransactionLine.Transaction = Transaction.id
WHERE
    Transaction.tranid = 'ASSYB123'

This returns:

Tranid	Mainline	Item	Quantity
ASSYB123	T	Table (Assembly)	1
ASSYB123	F	Legs (Component)	-4
ASSYB123	F	Base (Component)	-1
ASSYB123	F	Screws (Component)	-10

As we can see the mainline row has the assembly item and the quantity is positive 1 because we are adding 1 assembly to inventory. The other rows are the components and the quantity is negative because we are removing the components from inventory (note that we will see a positive quantity in the UI).

Some fields on the TransactionLine main line row can be usefull even when we aren’t looking at the line data.

The field Transaction.location is marked as removed. The query SELECT Transaction.location FROM Transaction returns the following error:

Search error occurred: Field 'location' for record 'transaction' was not found. Reason: REMOVED - Field is removed

We can still get the location of a transaction by joining the main line row:

SELECT
    Transaction.tranid,
    TransactionLine.location,
FROM
  Transaction inner join TransactionLine on TransactionLine.Transaction = Transaction.id
  AND TransactionLine.mainline = 'T'

Now you will be able to get the location. Another field not found on the Transaction table is the “Created From” field usually found on the body of a transaction.

Again we can get this field by joining the main line row:

SELECT
    Transaction.tranid,
    TransactionLine.createdfrom,
FROM
  Transaction inner join TransactionLine on TransactionLine.Transaction = Transaction.id
  AND TransactionLine.mainline = 'T'
WHERE transaction.tranid = 'ASSYB123'

This will return the internal id of the work order that created this assembly. We can also use BUILTIN.DF to get the text representation as it shows in the UI:

SELECT
    Transaction.tranid,
    BUILTIN.DF(TransactionLine.createdfrom) AS createdfrom,
FROM
  Transaction inner join TransactionLine on TransactionLine.Transaction = Transaction.id
  AND TransactionLine.mainline = 'T'
WHERE transaction.tranid = 'ASSYB123'

This returns:

Tranid	Createdfrom
ASSYB123	Work Order #WO110

Note that Work Order #WO110 is not exactly the tranid of the work order which is WO110. Be careful when filtering.

The subsidiary column is also not accessible on the Transaction table.

The specific error when trying to access it is:

Search error occurred: Field 'subsidiary' for record 'transaction' was not found. Reason: NOT_EXPOSED - Field is marked as internal for channel SEARCH

Once again we can get this field by joining the main line row.

SELECT
    Transaction.tranid,
    TransactionLine.subsidiary,
FROM
   Transaction inner join TransactionLine on TransactionLine.Transaction = Transaction.id
    AND TransactionLine.mainline = 'T'

I hope this post was helpful.

For questions or comments, please leave a comment below or contact me at contact@netsuitediagnostics.com.