For transactions such as Bill Credit, Credit Memo, Customer Deposit, and Payment, we utilize the “transaction.amountremainingbasecurrency” attribute. However, for all other transaction types, we rely on the “transaction.foreignamountunpaid” attribute to determine the remaining balance.
Tag: suiteQL
Executing SuiteQL Queries Through REST Web Services
SuiteQL is a query language based on the SQL database query language. SuiteQL provides advanced dynamic query capabilities that can be used to access NetSuite records. You can execute SuiteQL queries through REST web services by sending a POST request to the suiteql resource, and specifying the query in the request body after the body parameter q. In… Continue reading Executing SuiteQL Queries Through REST Web Services
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
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