trasnaction search: invoice, get items sublist

AI generated answer (using internal Slack threads):

To get the items sublist from an invoice using SuiteQL, you can use a join to the

TransactionLine

table. Here is an example of a query that could be used:

sql
SELECT transaction.id, transaction.terms, transaction.type, transaction.tranid, transaction.trandisplayname, currency.symbol, TransactionLine.item, item.fullname 
FROM transaction 
LEFT JOIN currency ON (transaction.currency=currency.id ) 
INNER JOIN TransactionLine ON ( Transaction.ID = TransactionLine.Transaction ) 
LEFT JOIN item ON (TransactionLine.item = item.id ) 
WHERE transaction.type='CustInvc'

This will return each line item on the invoice as a separate row. If you want to get only one row per invoice, you might need to use a grouping function, but it seems that SuiteQL does not support functions like LISTAGG or NS_CONCAT which are available in saved searches. If you want to filter out certain line types, you can add additional conditions to the WHERE clause. For example, to exclude tax lines and non-inventory items, you could use:

sql
SELECT transaction.id, transaction.terms, transaction.type, transaction.tranid, transaction.trandisplayname, currency.symbol, TransactionLine.item, transactionline.itemtype, item.fullname, transactionline.taxline, TransactionLine.mainline 
FROM transaction 
LEFT JOIN currency ON (transaction.currency=currency.id ) 
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'

Please note that if you want to group your results, all the select fields will need to be either in the group or have a summary type. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******