How to query only the item lines in suiteql transactionline

AI generated answer (using internal Slack threads):

To query only the item lines in SuiteQL transactionline, you can use the

TransactionLine

table and join it with the

transaction

table. Here is an example of how you can do this:

sql
SELECT transaction.id, transaction.terms, transaction.type, transaction.tranid, transaction.trandisplayname, TransactionLine.item, item.fullname 
FROM transaction 
INNER JOIN TransactionLine ON ( Transaction.ID = TransactionLine.Transaction ) 
LEFT JOIN item ON (TransactionLine.item = item.id ) 
WHERE transaction.type='CustInvc' AND TransactionLine.itemtype IS NOT NULL 
AND transactionline.itemtype<>'TaxGroup' 
AND transactionline.itemtype<>'TaxItem' 
AND transactionline.itemtype<>'NonInvtPart'

This query will return the transaction details along with the item details from the

TransactionLine

table. The

TransactionLine.itemtype

is used to filter out non-item lines. Please note that this query will return multiple rows for a single invoice if it has multiple item lines. If you want to get a single row per invoice, you will need to use aggregate functions or subqueries, which are not fully supported in SuiteQL. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******