This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I have saved a search that would give me my total sales per item. Whether it was sold as a kit, assembly or inventory item we are looking to switch this to sql

```var transactionSearchObj = search.create({
   type: "transaction",
   filters:
   [
      ["type","anyof","CashSale","SalesOrd"], 
      "AND", 
      ["mainline","is","F"], 
      "AND", 
      ["taxline","is","F"], 
      "AND", 
      ["accounttype","anyof","Income"], 
      "AND", 
      ["trandate","within","lastmonth"], 
      "AND", 
      ["location.internalid","anyof","5","14","6","29","22"], 
      "AND", 
      ["shipping","is","F"]
   ],
   columns:
   [
      search.createColumn({
         name: "formulatext",
         summary: "GROUP",
         formula: "DECODE({item.memberitem},NULL,{item},{item.memberitem})",
         label: "Item Name"
      }),
      search.createColumn({
         name: "formulanumeric",
         summary: "SUM",
         formula: "DECODE({item.memberquantity},NULL,{quantity},{quantity}*{item.memberquantity})",
         label: " Total  qty sold"
      }),
      search.createColumn({
         name: "custitem2",
         join: "item",
         summary: "GROUP",
         label: "Manufacturer"
      }),
      search.createColumn({
         name: "estgrossprofit",
         summary: "SUM",
         label: "Est. Gross Profit (Line)"
      }),
      search.createColumn({
         name: "costestimaterate",
         summary: "MAX",
         label: "Est. Unit Cost"
      })
   ]
});
var searchResultCount = transactionSearchObj.runPaged().count;
log.debug("transactionSearchObj result count",searchResultCount);
transactionSearchObj.run().each(function(result){
   // .run().each has a limit of 4,000 results
   return true;
});

/*
transactionSearchObj.id="customsearch1688489618219";
transactionSearchObj.title="Total sales --last month (copy)";
var newSearchId = transactionSearchObj.save();
*/```

this is my start
```		SELECT TOP 100
        Transaction.TranDate,
			Transaction.memo, 
			Transaction.Type AS TranType,
            TransactionLine.Item,
            TransactionLine.taxline,
            TransactionLine.location,
           
		FROM
			Transaction
            INNER JOIN TransactionLine ON
		( TransactionLine.Transaction = Transaction.ID )
WHERE Transaction.Type = 'CashSale' OR Transaction.Type = 'SalesOrd' AND TransactionLine.taxline = 'F'  AND TransactionLine.location  = '5'```

is your est gross profit and Est. Unit Cost a calculated custom field in Netsuite?

if so, you should start with trying to figure out how to do this for 1 order and then apply you sum/group by