As a NetSuite Administrator, you may need to quickly identify the largest Invoices for each Customer. This can be useful for reviewing high-value transactions, supporting collections analysis, preparing customer reporting, or validating invoice activity.
In this article, we use SuiteQL to rank Customer Invoices by Invoice Amount for each Customer. The Invoice with the highest amount for each Customer receives rank 1.
Sample SuiteQL Query
The following sample returns Customer Invoices and assigns a rank based on Invoice Amount. The ranking restarts for each Customer.
SELECT
t.entity AS customer_id, -- Customer internal ID
BUILTIN.DF(t.entity) AS customer_name, -- Customer display name
t.tranid AS invoice_number, -- Invoice number
t.trandate AS invoice_date, -- Invoice date
t.foreignTotal AS invoice_total, -- Invoice amount used for ranking
RANK() OVER (
PARTITION BY t.entity -- Restart ranking for each customer
ORDER BY t.foreignTotal DESC -- Highest invoice amount gets rank 1
) AS invoice_rank -- Rank result
FROM
Transaction t
WHERE
t.type = 'CustInvc' -- Show customer invoices only
ORDER BY
t.entity,
invoice_rank,
t.trandate DESC
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.