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 design and build custom software for businesses running on NetSuite — from mobile apps and Web portals to Web APIs and integrations.
I’ve created several widely used open-source solutions for the NetSuite community, including the SuiteQL Query Tool and SuiteAPI, which help developers and businesses get more out of their systems.
I’m also the founder of SuiteStep, a NetSuite development studio focused on pushing the boundaries of what’s possible on the platform. Through SuiteStep, I deliver custom software and AI-driven solutions that make NetSuite more powerful, accessible, and future-ready.
Copyright © 2025 Tim Dietrich.