When looking at the transactionLine COGS - how can I tell which specific line the COGS amount is related to? I can’t rely on item ID since an order can have mutliple rows with the same item.

@Cory Weiner I'm not sure if this will help or not, but take a look at the query in this post: https://timdietrich.me/blog/netsuite-suiteql-gl-impact/ In the join between the TransactionAccountingLine and TransactionLine, I added this:

AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine )

I hope that helps.

Thanks Tim, That is close. I’ve been using the “ProjectFinancials” table which is similar. The issue is netsuite generates the GL impact from a source line on the transaction by adding two additional lines to the transaction. For example, line 0 is the actual line I added, and Netsuite added the two lines (ID 1 and 2) behind the scenes. The actual GL impact in the table you referenced links the lines 1 & 2, not to the original source line. What I want to figure out is how those two ‘extra’ lines relate back to the source line (0) --

@Cory Weiner Ok, I see. I didn't realize you were using projects. The join from ProjectFinancials to TransactionLine looks kind of funky. But I think this does it.

SELECT
	ProjectFinancials.*, 
	BUILTIN.DF( ProjectFinancials.Transaction ) AS TransactionID,
	BUILTIN.DF( ProjectFinancials.Account ) AS AccountName,
	BUILTIN.DF( TransactionLine.Item ) AS ItemName,
	TransactionLine.Rate,
	TransactionAccountingLine.Debit,
	TransactionAccountingLine.Credit,
	TransactionAccountingLine.Posting,
FROM 
	ProjectFinancials 
	INNER JOIN transactionLine ON 
		( TransactionLine.Transaction = ProjectFinancials.transaction )
		AND ( transactionLine.ID = ProjectFinancials.transactionline )
	INNER JOIN TransactionAccountingLine ON
		( TransactionLine.Transaction = TransactionAccountingLine.Transaction )
		AND ( TransactionLine.LineSequenceNumber = TransactionAccountingLine.TransactionLine )
WHERE 
	ProjectFinancials.Transaction = 257300

If I'm understanding you correctly, then I think you'd want to add another join, back to ProjectFinancials, with TransactionLine 0.