RE: SuiteQL selecting TransactionLines and linking inventory line to tax line
I am selecting from TransactionLine table using SuiteQL
some of the lines:
itemType = ‘ShipItem’
itemType = ‘Assembly’
itemType = ‘TaxItem’
itemType = ‘TaxItem’
What is the correct way to link ShipItem to TaxItem and Assembly to TaxItem?
Is there another table I need to use?
I looked at nextTransactionLineLink table but cannot see anything at this detail.
I tried to look on invoice and salesorder transaction types.
In netsuite UI these items are linked correctly.
To link ShipItem to TaxItem and Assembly to TaxItem in SuiteQL, you can use the following query:
SELECT
transactionLine.id,
transactionLine.itemType,
transactionLine.taxItemId
FROM
transactionLine
LEFT JOIN
nextTransactionLineLink
ON
transactionLine.id = nextTransactionLineLink.parentTransactionLineId
WHERE
transactionLine.itemType IN ('ShipItem', 'Assembly')
AND
nextTransactionLineLink.childTransactionLine.itemType = 'TaxItem'
This query will return all ShipItem and Assembly lines, along with the TaxItem line that they are linked to.
If you want to filter the results to only include lines from a specific invoice or sales order, you can add the following criteria to the WHERE clause:
AND
transactionLine.transaction.type IN ('Invoice', 'SalesOrder')
AND
transactionLine.transaction.id = 123456
what is this you have 3 level deep
nextTransactionLineLink.childTransactionLine.itemType = 'TaxItem'
"Invalid search query. Detailed unprocessed description follows. Search error occurred: Field 'childTransactionLine' for record 'NextTransactionLineLink' was not found.\nField 'parentTransactionLineId' for record 'NextTransactionLineLink' was not found.\n."
