NetSuite Internals – Transaction_Line Table

NetSuite Internals – Transaction_Line Table

Transaction_Line Table in NetSuite: A Complete Guide

The Transaction_Line table in NetSuite contains line-level details for all types of transactions. This is a child table of the Transaction table, meaning each record represents an individual line item within a transaction (e.g., sales order, invoice, bill, etc.).

---

1️⃣ Purpose of Transaction_Line Table

This table is used to store detailed line-level information for transactions such as:

• Sales Orders
• Invoices
• Purchase Orders
• Bills
• Journal Entries
• Credit Memos
• Cash Sales
• Expense Reports
  and more.

Each row in this table represents one line within a transaction.

---

2️⃣ Relationship with the Transaction Table

The Transaction_Line table is directly linked to the Transaction table.
Each transaction (e.g., an invoice) may have multiple line items, which are stored in Transaction_Line.

Transaction (Parent Table)	Transaction_Line (Child Table)
Sales Order #123	Line 1: Product A – Qty 5
	Line 2: Product B – Qty 2
Invoice #456	Line 1: Service A – Qty 3
	Line 2: Service B – Qty 1

Each Transaction_Line entry belongs to a specific transaction, linked via the Transaction field.

---

3️⃣ Key Fields in the Transaction_Line Table

Below are some of the most important fields found in this table:

Column Name	Description
Transaction	The ID of the parent transaction (foreign key to Transaction table).
Line	Line number within the transaction.
Item	The ID of the item (product/service) on the line.
Quantity	The number of units for the line item.
Rate	The rate per unit for the line item.
Amount	Total amount for the line (before taxes and discounts).
Tax Amount	The tax applied to this line item.
Account	The general ledger (GL) account associated with the transaction line.
Posting	Boolean (T/F) that indicates if this line posts to the General Ledger.
Department	The department associated with the line item.
Class	The class assigned to the line.
Location	The location associated with the line item.
Memo	Any description/memo entered for the line item.
Created By	The user who created this transaction line.
Last Modified	Timestamp of when the line item was last updated.

---

4️⃣ How to Use Transaction_Line Data

This table is commonly used in:

• Saved Searches: To retrieve line-level details for custom reports.
• SuiteQL Queries: To extract detailed financial and item-level data.
• Integrations (REST/SOAP API): To fetch detailed transaction data for external applications.
• Custom SuiteScripts: To manipulate transaction lines dynamically.

---

5️⃣ Example SQL Query to Fetch Transaction Line Data

Here’s a SuiteQL query to retrieve the latest Sales Order line items:

SELECT 
    tl.transaction AS Transaction_ID,
    t.tranid AS Transaction_Number,
    t.trandate AS Transaction_Date,
    tl.line AS Line_Number,
    i.itemid AS Item_Name,
    tl.quantity,
    tl.amount,
    tl.account,
    tl.posting
FROM 
    Transaction_Line tl
JOIN 
    Transaction t ON tl.transaction = t.id
JOIN 
    Item i ON tl.item = i.id
WHERE 
    t.type = 'SalesOrd'
ORDER BY 
    t.trandate DESC;

🔹 This fetches line items for Sales Orders, including item names, quantities, amounts, and posting details.

---

6️⃣ Example SuiteScript to Fetch Transaction_Line Data

If you want to extract transaction line details via SuiteScript:

/**
 * @NApiVersion 2.x
 * @NScriptType Suitelet
 */
define(['N/query'], function(query) {
    function onRequest(context) {
        if (context.request.method === 'GET') {
            var sql = `
                SELECT 
                    tl.transaction AS Transaction_ID,
                    t.tranid AS Transaction_Number,
                    t.trandate AS Transaction_Date,
                    tl.line AS Line_Number,
                    i.itemid AS Item_Name,
                    tl.quantity,
                    tl.amount
                FROM 
                    Transaction_Line tl
                JOIN 
                    Transaction t ON tl.transaction = t.id
                JOIN 
                    Item i ON tl.item = i.id
                WHERE 
                    t.type = 'SalesOrd'
                ORDER BY 
                    t.trandate DESC
                LIMIT 10;
            `;

            var resultSet = query.runSuiteQL({ query: sql }).asMappedResults();
            context.response.write(JSON.stringify(resultSet, null, 2));
        }
    }

    return { onRequest: onRequest };
});

📌 How it Works:

• Fetches line-level transaction details for Sales Orders.
• Uses SuiteQL with N/query.
• Returns JSON output.

---

7️⃣ Common Use Cases for Transaction_Line

✅ Sales Reports

• Track item sales per transaction line.
• Filter by specific item IDs.

✅ Financial Reports

• Get GL account details for each transaction.
• Identify which lines impact the General Ledger.

✅ Inventory & Fulfillment

• Analyze line-level stock movements.
• Extract data for purchasing and order management.

✅ Custom Business Logic

• Automate workflows based on line-level transactions.
• Adjust pricing, taxes, or discounts dynamically.

---

8️⃣ Best Practices

• Use Joins Carefully → The Transaction_Line table is large, so optimize queries.
• Index Filtering → Filter by transaction, item, or type for better performance.
• Avoid Unnecessary Fields → Fetch only the data you need to improve query speed.
• Use SuiteQL for Large Data Sets → Faster than Saved Searches for big queries.