Overview: Tracking product returns is essential for improving quality, reducing costs, and enhancing customer satisfaction. This SuiteQL query helps NetSuite users identify which inventory items are returned most frequently, offering actionable insights for product and operations teams. What It Does: Targets return authorization transactions (RtnAuth) from NetSuite Filters out voided or cancelled records Focuses specifically… Continue reading SuiteQL query for Identifying most returned Inventory items in NetSuite
Tag: suiteql query
SuiteQL query to fetch the item details based on a subsidiary
Since we are unable to directly filter the item data using the subsidiary, we will have to use a modified version of the item query by joining the itemSubsidiaryMap table and subsidiary table to get the required results. SELECT item.itemid,item.id,item.itemtype FROM itemSubsidiaryMap INNER JOIN item ON (item.id=itemSubsidiaryMap.item) INNER JOIN subsidiary ON (subsidiary.ID=itemSubsidiaryMap.subsidiary) WHERE subsidiary.id=${nsSubsidiary}
SuiteQL: Retrieve Top-Selling Items
SELECT item.itemid, SUM(transactionlines.quantity) AS total_sold FROM transaction INNER JOIN transactionlines ON transaction.id = transactionlines.transaction INNER JOIN item ON transactionlines.item = item.id WHERE transaction.type = ‘SalesOrd’ AND transaction.status = ‘SalesOrd:B’ GROUP BY item.itemid ORDER BY total_sold DESC LIMIT 10; Identifies the top 10 best-selling items based on quantities sold (SUM(transactionlines.quantity)). Filters billed sales… Continue reading SuiteQL: Retrieve Top-Selling Items
SuiteQL: Join Tables-Transactions and Items
SELECT transaction.tranid AS sales_order, item.itemid AS item_name, transactionlines.quantity AS quantity_sold FROM transaction INNER JOIN transactionlines ON transaction.id = transactionlines.transaction INNER JOIN item ON transactionlines.item = item.id WHERE transaction.type = ‘SalesOrd’ AND transaction.trandate BETWEEN {today}-90 AND {today}; Combines data from transaction, transactionlines, and item tables using INNER JOIN. Fetches sales… Continue reading SuiteQL: Join Tables-Transactions and Items
SuiteQL: Parameterized Query Example
SELECT entityid, email, phone FROM customer WHERE id = {customer_id}; A parameterized query that dynamically fetches data for a specific customer using customer_id. Retrieves entityid (customer name), email, and phone.
SuiteQL: Filter Transactions by Date Range
SELECT tranid, entity, trandate, total FROM transaction WHERE type = ‘SalesOrd’ AND trandate BETWEEN {today}-30 AND {today} ORDER BY trandate DESC; Retrieves all Sales Orders (type = ‘SalesOrd’) created in the last 30 days. The trandate filter ensures only transactions within the specified date range are included. Results are sorted in descending order of trandate,… Continue reading SuiteQL: Filter Transactions by Date Range
Create a Query for Transaction Records and Run It as a Paged Query
The following sample creates a query for transaction records, joins the query with another query type, and runs the query as a paged query. /** * @NApiVersion 2.1 */ require([‘N/query’], query => { // Create a query definition for transaction records let myTransactionQuery = query.create({ type: query.Type.TRANSACTION }); // Join the original query definition based… Continue reading Create a Query for Transaction Records and Run It as a Paged Query
Create a Query for Customer Records and Run It as a Non-Paged Query
The following sample creates a query for customer records, joins the query with two other query types, and runs the query. /** * @NApiVersion 2.1 */ require([‘N/query’], query => { // Create a query definition for customer records let myCustomerQuery = query.create({ type: query.Type.CUSTOMER }); // Join the original query definition based on the salesrep… Continue reading Create a Query for Customer Records and Run It as a Non-Paged Query
How to Retrieve Quota Details for a Sales Rep for the Current Month Using NetSuite Workbook
NetSuite’s Workbook feature is a powerful, intuitive tool that allows you to generate real-time reports and visualizations for your business data. One of the common use cases is to track and monitor the sales quota for a sales rep during the current month. This article walks you through the steps to create a custom workbook… Continue reading How to Retrieve Quota Details for a Sales Rep for the Current Month Using NetSuite Workbook
Solution for getting internal id of all bill credits applied in the Bill Payment record.
The internal id of all the bill credits applied in a bill payment record cannot be retrived easly from record object and by using saved search. Upon checking it was noticed that an enhancement has been created for this functionality: “Enhancement 213645: Search to show all Bills and Bill Credits associated to a Bill Payment”.… Continue reading Solution for getting internal id of all bill credits applied in the Bill Payment record.