CloudExtend Excel for NetSuite allows you to see and run your NetSuite saved searches directly in Excel. Once your search is built in NetSuite you can use CloudExtend Excel for NetSuite to retrieve the latest results directly from Excel instead of logging into NetSuite.

Create an A/R Aging saved search in NetSuite

To see your A/R aging details in Excel follow the instructions below to create a NetSuite saved search with the required data.

Create a new NetSuite saved transaction search (Reports -> Saved Searches -> All Saved Searches -> New -> Transactions

Criteria

Use (or modify) the following criteria.

  • Account Type: any of Accounts Receivable
  • Status: none of Invoice: Paid in Full, Invoice: Pending Approval
  • Amount Remaining: not equal to 0

Results

Remove all suggested columns and set 'show totals' = True.

Add the following formulas.

Add the related customer record by scrolling to the bottom of the list of criteria fields to see a list of related records (they all end in ...)

Select Customer... (yours may say Customer/Projects...) and select name from the dialog box.

Summary Type: Group

Formula (Currency)

Summary Type: sum

Formula: case when trunc({today})-{duedate} < 0 then {amount} end

Custom summary label: Current

Formula (Currency)

Summary Type: sum

Formula: case when trunc({today})-{duedate} between 1 and 30 then {amount} end

Custom summary label: '1-30

Formula (Currency)

Summary Type: sum

Formula: case when trunc({today})-{duedate} between 31 and 60 then {amount} end

Custom summary label: 31-60

Formula (Currency)

Summary Type: sum

Formula: case when trunc({today})-{duedate} between 61 and 90 then {amount} end

Custom summary label: 61-90

Formula (Currency)

Summary Type: sum

Formula: case when trunc({today})-{duedate} > 90 then {amount} end

Custom summary label: >90

Formula (Currency)

NVL(case when trunc({today})-{duedate} < 0 then {amount} end,0)+ NVL(case when trunc({today})-{duedate} between 1 and 30 then {amount} end,0) + NVL(case when trunc({today})-{duedate} between 31 and 60 then {amount} end,0) + NVL( case when trunc({today})-{duedate} between 61 and 90 then {amount} end ,0)+ NVL(case when trunc({today})-{duedate} > 91 then {amount} end,0)

Custom summary label: Total Amount Outstanding

Saved your search following your naming conventions.

Criteria

Results

Run your saved search from Excel

  1. Load CloudExtend Excel for NetSuite and select 'saved searches' from the menu icon.
  2. Select 'Transaction' on the select a record screen to see a list of all your transaction based saved searches.
  3. Start typing the name of your A/R saved search (if you don't see it consider making it public)
  4. Select your search and choose 'Download as Raw Data'
  5. Once the data is downloaded add any formatting desired. Choosing format as table will add some nice shading to rows and provide a drop down filter as well.

Visualize your data in Excel

👉CloudExtend does not offer consulting on how to build or troubleshoot NetSuite saved searches. Please reach out to NetSuite professional services, your local consultant, or any number of NetSuite forums for help on constructing your search.

Did this answer your question?