The netsuite query module is broken. Run the following query in your account code and you'll get listed values instead of grouped ones. In the occurances variable you'll see how many there are for each entity. There should never be more than 1 let myQuery = query.create({ type: 'transaction' }); let transactionlinesJoin = myQuery.autoJoin({ fieldId:'transactionlines'}); let accountingimpactJoin = transactionlinesJoin.autoJoin({ fieldId:'accountingimpact'}); myQuery.columns = [ accountingimpactJoin.createColumn({fieldId: "amount", alias: "amount", aggregate:query.Aggregate.SUM, context: query.FieldContext.CURRENCY_CONSOLIDATED}), myQuery.createColumn({fieldId: "entity", alias: "entity", groupBy:true, context: query.FieldContext.DISPLAY})]; let resultSet = myQuery.runPaged({pageSize:1000}); let occurrances = {}; for (let i = 0; i < resultSet.pageRanges.length; i++) { let thisPage = resultSet.fetch({ index: i }); let pagedResults = thisPage.data.asMappedResults(); pagedResults.forEach( (mappedResult) => { let {amount, entity} = mappedResult; if (occurrances[entity] === undefined) occurrances[entity] = 0; occurrances[entity]++ //console.log(entity, amount); }); }

the only thing I use

query

for is to run SuiteQL. I don't like their object model for non SuiteQL queries. I suggest trying rewriting the query as SuiteQL and see if you get better results).

I mean thats valid. SuiteQL wasn't out when I was building this though and at the same time their query module does need to work and group correctly.

when you submit the bug report to netsuite, you probably want to help them out

True. They are asking me if this is still happening like a weekend of time would magically fix it. I didn't think I'd have to do all their legwork but if they come back again I'll give them that printout

support isnt that useful, expect to clearly outline what the problem is

Initially the query would fail. They "fixed" it but now it doesn't group

share the sql query that your query is generating, and then we can evaluate why im saying you are representing the problem incorrectly

I think this is it? I don't have it handy at the moment.txt

thats pretty ugly looking, the prettier form is

SELECT 
  BUILTIN_RESULT.TYPE_CURRENCY(SUM(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 25, 'DEFAULT')), BUILTIN.CURRENCY(SUM(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 25, 'DEFAULT')))) AS amount /*SUM({transactionlines.accountingimpact.amount#CURRENCY_CONSOLIDATED})*/, 
  BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF("TRANSACTION".entity)) AS entity /*{entity#DISPLAY}*/
FROM 
  "TRANSACTION", 
  TransactionAccountingLine, 
  transactionLine
WHERE 
  ((transactionLine."TRANSACTION" = TransactionAccountingLine."TRANSACTION" AND transactionLine."ID" = TransactionAccountingLine.transactionline))
   AND "TRANSACTION"."ID" = transactionLine."TRANSACTION"
GROUP BY 
  BUILTIN.CURRENCY(SUM(BUILTIN.CONSOLIDATE(TransactionAccountingLine.amount, 'LEDGER', 'DEFAULT', 'DEFAULT', 1, 25, 'DEFAULT'))), 
  BUILTIN.DF("TRANSACTION".entity)