Hi, I am trying to access a sublist (item) from Invoice via suiteql but invoice object is not found. I can do transactions where type=custinvc but it does not item object. How can I access the invoices with item sublist via suiteql?

@Kushal bindra you need to join to the

TransactionList

table. Have a look at this post from @tdietrich for some examples

@Kushal bindra I think this is the post that @michoel was referring to: https://timdietrich.me/blog/netsuite-suiteql-item-transactions/

Lol yes, would have helped for me to post it

thank you, i looked at the link again and was able to made progress

It sounds like you want something like LISTAGG or NS_CONCAT which works in saved searches, but I've given it a try and it doesn't look like that is supported by SuiteQL

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'

I'm not sure what you are trying to accomplish Do you only want one result per invoice?

yes, we determine the invoice type from the item name and only 1 result per invoice is appropriate

Will you never have an invoice with more than one inventory item?

i would and in that case we only look at the first or the last item

You probably want to group your results

for our integration we only need the total amount, amount paid and amount remaining

if you group the query all the select fields will need to be either in the group or have a summary type

do you know what does this mean? Invalid search query Search error occurred: Invariant check for stage 'PaginationOperation' check failed for 'SELECT [5x] FROM [1x] GROUP BY [1x]'.