Netsuite offers AR Aging Report. However, you might not be able to expose the field that you wish to expose. For example, users want to expose the Internal ID of the transactions on certain reports, such as A/R Aging Report and A/R Aging Detail report. You can create a saved search to do so.
- Using Administrator role, Navigate to Lists > Search > Saved Searches > New
- Select Transaction
- Enter a Search Title
- Under Criteria tab > Standard subtab, enter the following filters:
- Account Type = is Accounts Receivable
- Posting = is True
- Date = is on or before (click Custom > Day > Today)
- Amount Remaining = is not equal to 0.00
|
Field |
Summary Type |
Formula |
Custom Label/ Summary Label |
|
Name |
Group |
|
|
|
Date |
|
|
|
|
Type |
|
|
|
|
Document Number |
|
|
|
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end |
Current Open Balance |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end |
30 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end |
60 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end |
90 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end |
> 90 Days |
|
Formula (Currency) |
Sum |
(Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end)+ (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end) |
Total Open Balance |
|
Field |
Summary Type |
Formula |
Custom Label/ Summary Label |
|
Name |
Group |
|
|
|
Date |
|
|
|
|
Type |
|
|
|
|
Document Number |
|
|
|
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end |
Current Open Balance |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end |
30 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end |
60 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end |
90 Days |
|
Formula (Currency) |
Sum |
Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end |
> 90 Days |
|
Formula (Currency) |
Sum |
(Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) < 1 then {amountremaining} else 0 end)+ (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 1 and 30 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 31 and 60 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) between 61 and 90 then {amountremaining} else 0 end) + (Case When substr({amount},1,1) = '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then ({amountremaining}*-1) When substr({amount},1,1) <> '-' and (NVL({daysoverdue}, Round({today}-{trandate}, 0))) > 90 then {amountremaining} else 0 end) |
Total Open Balance |
6. Under Available Filters tab, add Date and set Show In Filter Region = True
7. Click Save & Run
The CFO’s Secret Weapon: The Concentrus Advantage ROI Roadmap™
We have built the ROI roadmap, calculator, and benchmarks CFOs need to manage ERP like a capital investment—because that’s what it is.
Schedule a free strategy call with our ERP experts today!
References
Gartner. (2023). Enterprise Resource Planning (ERP) Insights. Retrieved from Gartner: https://www.gartner.com/en/information-technology/topics/enterprise-resource-planning
About Us
Concentrus is a leading provider of innovative cloud-based enterprise resource planning (ERP) solutions, including NetSuite. Our team of NetSuite experts offers a range of services, including NetSuite price analysis, NetSuite training, and NetSuite integration services.
Our goal is to help businesses of all sizes maximize their investment in NetSuite by providing expert NetSuite cost optimization and implementation strategies. With years of experience as a NetSuite partner, our NetSuite administrators and NetSuite consultants are well equipped to help businesses of all sizes with their NetSuite consulting needs.
Whether you're looking for a NetSuite consultant to help with your NetSuite implementation or you need ongoing NetSuite support, Concentrus is here to help.
Read About Our Implementation Methodology
Want more NetSuite Tips and Tricks? Check out our Short & 'Suite videos
