The SuiteQL query below returns all customer invoices issued in the past 30 days. SELECT Transaction.ID AS Invoice, Transaction.TranID AS InvoiceNumber, Transaction.TranDate AS InvoiceDate, Transaction.Entity AS Customer, BUILTIN.DF( Transaction.Entity ) AS CustomerName, Transaction.OtherRefNum AS CustomerPONumber, TransactionLine.CreatedFrom AS SalesOrder, BUILTIN.DF( TransactionLine.CreatedFrom ) AS SONumber, Transaction.Employee AS SalesRep, BUILTIN.DF( Transaction.Employee ) AS SalesRepName, Transaction.ForeignTotal AS TotalAmount, REPLACE(… Continue reading suiteQL query to get invoices by a date range
Tag: suiteQL
Get Record Display Names without JOIN in SuiteQL
When querying NetSuite’s SuiteAnalytics SQL, many record fields are stored as internal IDs (e.g., customer, employee, item). To retrieve the display name or label (just like what’s shown in the NetSuite UI), you can use the powerful BUILTIN.DF() function. What is BUILTIN.DF()? BUILTIN.DF() is a special NetSuite SQL function that returns the default display value… Continue reading Get Record Display Names without JOIN in SuiteQL
Joining Employees and Vendors in Purchase Order Using SuiteQL
When working with SuiteQL to analyze Purchase Orders, you may want to include additional information about related Entities (e.g., Vendors) and Employees (e.g., Sales Reps). However, not all Purchase Orders are associated with an Employee, so it’s important to structure your joins correctly. This guide demonstrates how to build a SuiteQL query that pulls Purchase Orders along with: The Entity (Vendor) information — always required. The Employee information — if available. INNER JOIN vs LEFT… Continue reading Joining Employees and Vendors in Purchase Order Using SuiteQL
Display Values from the Transaction Record Using SuiteQL BUILTIN.DF
When working with the Transaction record in SuiteQL, some fields return internal values that are not easily understood at a glance. For example: The Status field may return a single-letter code. Fields like Entity, Employee, and LastModifiedBy return internal IDs. The Type field displays abbreviations like SalesOrd or PurchOrd. These internal values are meaningful to the system but not very useful to end users or admins reviewing query results. To… Continue reading Display Values from the Transaction Record Using SuiteQL BUILTIN.DF
suiteQL query to get the customer payment application details
This SuiteQL query returns the invoices to which a customer payment was applied. SELECT Invoice.TranID AS InvoiceID, REPLACE( BUILTIN.DF( InvoiceMainLine.CreatedFrom ), ‘Sales Order #’, ” ) AS SONumber, Invoice.OtherRefNum AS CustomerPO, PTLL.ForeignAmount AS AmountPaid FROM Transaction AS Payment INNER JOIN TransactionLine AS PaymentLine ON ( PaymentLine.Transaction = Payment.ID ) AND ( PaymentLine.MainLine = ‘F’ )… Continue reading suiteQL query to get the customer payment application details
SuiteQL query to get company contacts
The SuiteQL query below returns all of the contacts associated with a specified company. SELECT Company.ID AS Company, Company.EntityTitle AS CompanyName, CompanyContactRelationship.Contact, BUILTIN.DF( CompanyContactRelationship.Contact ) AS ContactName, CompanyContactRelationship.Role, BUILTIN.DF( CompanyContactRelationship.Role ) AS RoleName FROM Entity AS Company INNER JOIN CompanyContactRelationship ON ( CompanyContactRelationship.Company = Company.ID ) WHERE ( Company.ID = 2707 )
SuiteQL query to get the Customer Deposits
This SuiteQL query below returns all deposits received for a specific customer. SELECT Transaction.ID AS Transaction, Transaction.TranID, Transaction.TranDate, BUILTIN.DF( Transaction.Entity ) AS Customer, BUILTIN.DF( TransactionMainLine.CreatedFrom ) AS SalesOrder, Transaction.ForeignTotal, BUILTIN.DF( Transaction.PaymentMethod ) AS PaymentMethod, Transaction.OtherRefNum, BUILTIN.DF( Transaction.Status ) AS Status FROM Transaction INNER JOIN TransactionLine AS TransactionMainLine ON ( TransactionMainLine.Transaction = Transaction.ID ) AND (… Continue reading SuiteQL query to get the Customer Deposits
SuiteQL query to get the item’s price level history
The query given below will give an item’s price level history. It is queried using the InvtItemPriceHistory table. SELECT BUILTIN.DF( PriceType ) AS PriceType, Version, Quantity, Price, Discount FROM InvtItemPriceHistory WHERE ( Item = 1223 ) ORDER BY PriceType, Quantity, Version
SuiteQL query to get the get an item’s price information
The query below will return all price levels for which the item has a set price. It does not include price levels for which the item doesn’t have a price specified. SELECT BUILTIN.DF( Pricing.PriceLevel ) AS PriceLevelName, Pricing.PriceQty, Pricing.UnitPrice FROM Pricing WHERE ( Pricing.Item = 1223 ) ORDER BY PriceLevelName, Pricing.PriceQty
Total Sales Orders per Day Using SuiteQL
SuiteQL in NetSuite enables the extraction and manipulation of data from your NetSuite account. It provides users with flexibility and efficiency when accessing data by allowing them to design complicated queries to retrieve specific information. Here’s an example SuiteQL query for calculating total revenues per day. SELECTTranDate,COUNT(*) as Count,SUM(ForeignTotal) as TotalFROMTransactionWHERE( Type = ‘SalesOrd’ )AND (… Continue reading Total Sales Orders per Day Using SuiteQL