Problem/Business Requirement/Unique Question:
ODBC > How to get the total value on the amount field of transaction records?
A user would like to get the total value on the amount field of the transaction.
The amount of each item is located in the AMOUNT column of TRANSACTION_LINES table.
In a typical database, we can get the total amount of multiple items by adding the value on each column. In Netsuite ODBC, for every changes made on the transaction record, a row is created on the TRANSACTION_LINES table. The TRANSACTION_LINES table is keeping all the changes made on the transaction record. Adding the sum on the column of the line items is not applicable on the structure of TRANSACTION_LINES table.
In order to get the total value on the transaction, execute this query:
//Join the TRANSACTIONS table to the TRANSACTION_LINES table using the TRANSACTION_ID value.
SELECT * FROM TRANSACTION_LINES AS TL
LEFT JOIN TRANSACTIONS AS T ON TL.TRANSACTION_ID = T.TRANSACTION_ID
WHERE TL.TRANSACTION_ID = 277
In the result look for a row with a value of:
TRANSACTION_LINE_ID = 0 AND TRANSACTION_ORDER = 0
The value on the AMOUNT column is the total value of the transaction.
Similarly, a direct query can be executed and add the condition directly:
SELECT * FROM Administrator.TRANSACTION_LINES AS TL
LEFT JOIN Administrator.TRANSACTIONS AS T ON TL.TRANSACTION_ID = T.TRANSACTION_ID
WHERE TL.TRANSACTION_ID = 277 AND TRANSACTION_LINE_ID = 0 AND TRANSACTION_ORDER = 0