Published on November 11, 2024.
This SuiteQL query returns all customer invoices issued in the past 30 days, including the customer's name and PO number, sales order number, sales rep, status, balance due, and more.
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( BUILTIN.DF( Transaction.Status ), 'Invoice : ', '' ) AS Status, Transaction.ForeignAmountUnpaid AS BalanceDue, Transaction.DueDate FROM Transaction INNER JOIN TransactionLine ON ( TransactionLine.Transaction = Transaction.ID ) AND ( TransactionLine.MainLine = 'T' ) LEFT OUTER JOIN Transaction AS SalesOrder ON ( SalesOrder.ID = TransactionLine.CreatedFrom ) WHERE ( Transaction.Type = 'CustInvc' ) AND ( Transaction.TranDate >= BUILTIN.RELATIVE_RANGES( 'DAGO30', 'START' ) ) ORDER BY Transaction.TranID DESC
Hello, I'm Tim Dietrich. I develop custom software for businesses that are running on NetSuite, including mobile apps, Web portals, Web APIs, and more.
I'm the developer of several popular NetSuite open source solutions, including the SuiteQL Query Tool, SuiteAPI, and more.
I founded SuiteStep, a NetSuite development studio, to provide custom software and AI solutions - and continue pushing the boundaries of what's possible on the NetSuite platform.
Copyright © 2025 Tim Dietrich.