transaction search via suitesscript: invoice, get items sublist

AI generated answer (using internal Slack threads):

To access the sublist (item) from an Invoice via SuiteQL, you can use a JOIN to the

TransactionLine

table. Here is an example of a query that can 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 multiple rows for the same invoice if there are multiple items. 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 create an invoice with items using a SOAP request in .Net, you might want to refer to the SuiteTalk API documentation or ask in the SuiteTalk API channel for more specific guidance. For getting the inventory lot number, inventory on hand, and inventory status field of a lot-numbered item in a specific location using SuiteScript, you can create a search on the Inventory Balance record. Here is an example of how to load a saved search:

javascript
let searchObj = search.load({
    id: savedSearchId,
    type: search.Type.INVENTORY_BALANCE
});

Please note that the search type is mandatory when loading a search due to the Inventory Balance being a standalone search. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******