Tag: suiteQL

SuiteQL Query to Identify Items Frequently Sold Together

To identify items frequently sold together within a single sales order, the query performs two joins between the Transaction and TransactionLine tables. The first join (aliased as SalesOrderLine1) isolates orders containing a specified item, as defined in the WHERE clause. The second join (aliased as SalesOrderLine2) retrieves additional items included in those same orders. To… Continue reading SuiteQL Query to Identify Items Frequently Sold Together

SuiteQL for Retrieving Notes Attached to a Transaction

When working with transactions in NetSuite, it’s often important to access the notes associated with them—especially for audit trails, collaboration, or historical context. These notes are stored in the TransactionNote table and can be retrieved using a simple SQL query. ✅ Sample Query to Retrieve Transaction Notes SELECT TO_CHAR(TransactionNote.NoteDate, ‘YYYY-MM-DD @ HH12:MI PM’) AS NoteDate,… Continue reading SuiteQL for Retrieving Notes Attached to a Transaction

suiteQL query to get invoices by a date range

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

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