[JMUnderwood]

What is the best way to return the Transaction totals (like Amount) along with other details from the Transaction Body? There is no “Transaction_Total” field in the “Transactions” table.

I want to return the same fields you’re able to easily do using a custom search on Transactions where Type = “Invoice” and Mainline = True.

I need to return the following columns for each invoice:

Inv#

Inv Date

Customer

Ship Date

Days Overdue

Inv Total

Amount Paid

Amount Remaining

I know I can get the Inv Total by a SUM(Amount) on the Transaction_Lines table joins with the Transactions table. But what’s the most efficient way to get all of the columns I need?

I assume NetSuite has this nailed, since it is routinely and quickly provided in a Transaction custom search. So, NetSuite, mind sharing your SQL code for this?

Thanks,

JMU
This is a cached copy. Click here to see the original post.

[JMUnderwood]

RE: Best Method to Return Transactions Totals with SQL

Can anyone help on this?

TIA.

JMU

[shanondink]

RE: Best Method to Return Transactions Totals with SQL

JM,

Maybe the Amount field at TRANSACTION_LINES | TRANSACTION_LINE_ID = 0 will help.

From what I can tell, this is the total of the line items 1-x.

I might be all wrong but this seems to match on my 3 newest Invoices which is as far as I looked.

Seems funny on Item Fullfillments though.

Shanon

[JMUnderwood]

RE: Best Method to Return Transactions Totals with SQL

Originally posted by shanondink

Maybe the Amount field at TRANSACTION_LINES | TRANSACTION_LINE_ID = 0 will help.

Shannon,

I think you may be on to something.

Can someone from NetSuite please confirm this:

The data in the Transaction_Lines view for Transaction_Line_ID = 0 is:Amount — Transaction total as applied to the A/R ledger
Amount_Linked — Total amount paid to-date on this transaction
So, for Transaction_Line_ID = 0 the data is for the entire Transaction, not just one Item.

TIA,

JMU

[JMUnderwood]

Can someone from NetSuite please confirm this?

Evan? Yang? Anyone?

I really need to move forward with this task, and I am hesitant to do so until my assumptions are confirmed.

TIA,

JMU

[yang]

That is correct. For non journal entries, you can reliably get the total amount of the transaction by looking at the amount column in the transaction_lines row with transaction_line_id = 0.

Thanks,

Yang

[JMUnderwood]

Thanks for the confirmation Yang. Have a great day!

Best Regards,

JMU

[Author]

Posts