Get the Total Value on the Amount Field of Transaction Records Using ODBC

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.

 

Solution/Workaround/Gotcha/Recommendation Details:

 

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