Solving the NetSuite Aged Inventory Challenge

This article is relevant if you are thinking about how to create a NetSuite aged inventory report.

Background

One of our clients came to us recently with a challenge to produce an aged inventory report. They have a crafty NetSuite Administrator on staff that comes to us for his company’s more difficult challenges. His goal was to limit the output to only include the most recent item receipts for items that are currently on-hand. The Administrator was able to get pretty close using Saved Searches.

He brought us a clear articulation of what he wanted. Here is an example of his request.

Widget-A Receipts: $50 Each Unit at Cost
---------------------------------------
current to 60 Days = 10 Units Received
91 - 120 Days = 10 Units Received
121 - 180 Days = 10 Units Received
181 - 369 Days = 20 Units Received

If we have 25 Units On-Hand I would expect to see in my Aging report:

0-60        61-120      121-180       181-360       361+
------------------------------------------------------------
10            10           5             0            0


0-60        61-120      121-180       181-360       361+
-------------------------------------------------------------
$500         $500         $250          $0          $ 0

See the related saved search definitions and output. The challenge is that you can’t get your hands easily on the right data elements to get both the quantity and costs factors all together as you are crossing transaction based time horizons with information too distant from reach.

Overcoming NetSuite Saved Search Challenges

NetSuite saved searches are quite good in that they bring the power of the database to the hands of many who do not need to learn conventional structured query language (SQL) database inquiries techniques. Yet the tool has challenges reaching joined information (the multiple hop challenge) and performing subsequent calculations once you get a result set (meaning, you want to take a result, perhaps an aggregate or summary, and further calculate another column — the classic example being calculating gross margin after producing total revenue and total costs). While we are hopeful for the new NetSuite SuiteAnalytics Workbook feature, my early evaluation shows some continued challenges that will need to continue to be overcome.

Solving the Aged Inventory with a SuiteLet

While we could have used our Content Renderer Engine tool to produce multiple searches and then put the presentation together into an HTML page or PDF (or other — it’s just structured strings), the client wanted to emulate some specific filtering techniques and introduce some different output options. Thus we solved it using the standard “Build a SuiteLet” approach.

Here, I present some screenshots to help you see what is possible without too much effort:

1. Presentation of Filter Criteria: bring forth the key filtering elements. Using URL parameters, it is easy to recall these parameters for future use via bookmarks.
2. Output Display Options: select the range of data to output; including parameterizing the aging buckets.
3. Draw into NetSuite List: using NetSuite lists and grid, the output can be natively viewed and sorted.
4. Export into CSV: present an option to export data into CSV convenient to take to Excel or other spreadsheets.

Click screenshots to get a larger image.

Demonstration of SuiteLet Technology to Emulate Saved Search Lists

We solved the challenge using a conventional technique available via the NetSuite platform. Thus my goal here is to help produce inspiration for others who seek to learn the NetSuite platform so it will unlock their potential. In our client’s case, they have a great administrator — every once in a while, it’s good to have NetSuite developer type around to “go that much further”. If you are looking for expert NetSuite assistance, let’s have a conversation.

Be Sociable, Share!

• 
• 

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube