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
Tag: suiteQL
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
SuiteQL: Retrieve all customers’ names and email addresses.
SELECT entityid, email FROM customer WHERE isinactive = ‘F’ ORDER BY entityid ASC; This query retrieves all active customers (isinactive = ‘F’). It selects the entityid (customer name) and their email. The results are sorted alphabetically by entityid using ORDER BY ASC.
SuiteQL: Retrieving Journal Transactions
Accurate financial reporting is essential for any business, and managing journal transactions plays a key role in this process. These transactions are critical for businesses, as they not only support auditing and compliance efforts but also provide valuable insights for informed financial decision-making. In NetSuite, SuiteQL can be used to efficiently retrieve specific journal transaction details within… Continue reading SuiteQL: Retrieving Journal Transactions
Convert a Query to SuiteQL and Run It
The following sample creates a query for customer records, converts it to its SuiteQL representation, and runs it. /** * @NApiVersion 2.x */ require([‘N/query’], function(query) { var myCustomerQuery = query.create({ type: query.Type.CUSTOMER }); myCustomerQuery.columns = [ myCustomerQuery.createColumn({ fieldId: ‘entityid’ }), myCustomerQuery.createColumn({ fieldId: ’email’ }) ]; myCustomerQuery.condition = myCustomerQuery.createCondition({ fieldId: ‘isperson’, operator: query.Operator.IS, values: [true] });… Continue reading Convert a Query to SuiteQL and Run It
Retrieving Item Quantity in Transit Using SuiteQL
To determine the number of items in transit for a specific inventory item at a particular location, we can utilize the InventoryItemLocations table within SuiteQL. This table contains essential metrics such as quantityAvailable, quantityBackOrdered, quantityCommitted, quantityOnHand, and quantityOnOrder for all inventory locations. By querying this table, we can effectively calculate the quantity of items in… Continue reading Retrieving Item Quantity in Transit Using SuiteQL
How to check if a customer record has a particular employee as its sales team member using SuiteQL
In SuiteQL, directly verifying whether a specific employee belongs to the sales team associated with a customer record can be challenging, as the sales team members are stored as a sublist. We can utilize the table “CustomerSalesTeam” to achieve the desired functionality. The table defines the relationship between customers and their respective sales team members.… Continue reading How to check if a customer record has a particular employee as its sales team member using SuiteQL
How to paginate SuiteQL query results
If we have to obtain more than 5000 queries using a SuiteQL query, we will have to use SuiteQL.runPaged() function. For example, here we are trying to obtain the internal ID and subject of all phone call records that are assigned to the employee with internal ID -5 : let suiteql =`SELECT id, title FROM… Continue reading How to paginate SuiteQL query results