This article is relevant if you are seeking to understand how you can analyze open NetSuite sales orders and invoices to understand payment and credit memo history and thus use SuiteLet technology to join multiple saved searches.

Background

The Controller at one of our clients requested assistance with better NetSuite invoice inquiry mechanisms to assess how payments and credit memos were being applied. Typically, when attempting to solve NetSuite data analysis inquiries, we reach for Saved Search technology (hardly ever the built-in reports). Even with the new SuiteAnalytics Workbook tool, there are times that we have trouble getting at information and outputting in the desired output.

After more fully understanding our client’s request, our consultant, Marko O., used his wisdom and experience to summon his innovation capacity to solve our client’s request. Marko was instrumental in the build-out of our Content Renderer Engine (CRE) which is enjoyed by all of our clients to join together multiple saved searches to create powerful outputs that are very challenging to do in NetSuite otherwise.

SuiteLet Technology to Emulate Saved Search

Our client wanted the ability to query transactions based on multiple attributes and narrow in the records that match. Marko’s analysis of the challenge is that two saved searches were needed to get the reach required to pull in information that spans Sales Orders, Invoices, Credit Memos, and Payments. One search needed to focus on invoices that span a date range. Another saved search needed to focus on credit memos and payments that spanned a different date range.

Part of the challenge in our client’s data set was that not all invoices were backed by sales orders — a segment of their business was fulfilled by retail stores that ran their own independent systems. The data had integrity — yet it was sourced through different business practices which means that when combined, nulls and other linkages may or may not exist.

Depending on the nature of the underlying data, conventional saved search filtering will behave in ways that may not be desired. Since there is not the concept of a left join in NetSuite Saved Search, you can’t always reach for a column on the join without possibly affecting the results when data may not be there. With different source narratives in the data, this was also part of Marko’s puzzle.

These challenges can be overcome when you take matters in your own hands. First, Marko built a SuiteLet that created a number of filtering elements. These elements were the desired information attributes that our client needed in their inquiry work. Date was fundamental as our client wanted to understand the story between sales orders, invoices, credit memos, and payments.

Taking the filter criteria selected by the user, Marko then intelligently mapped the elements to the right targeted saved search (remember, there are two searches that are needed to get the data required). Once the filter elements were added as user-selected criteria, Marko understood the power of taking one search result and then feeding that in, dynamically, to another second saved search (this is a key feature of our Content Renderer Engine). This dynamic criteria method produces the link we want between the two saved searches.

Once Marko dynamically generated the saved searches with underlying criteria and linking information, he is then presented with two saved search results. He can then use JavaScript to effectively flatten out the information as if it was one table. At that point, it is straight forward to render that information into a NetSuite table.

To put some finishing touches on it, Marko produced an option that would allow the client to export the results in CSV format making it simple for them to pull the resulting information into a conventional spreadsheet. Our client reported great satisfaction in Marko’s work. Please click on the related image for a closer view.

Solve your NetSuite Data Inquiry and Analysis Requirements

In our experience working with our clients, Saved Search, and now the new Suite Analytics Workbooks, do a great job helping to query NetSuite information that is actionable. There are times we need more. The conventional IT wisdom is to build a data warehouse by extracting the data out and then hitting it with other reporting tools. Yet, sometimes, we just need “extra help” to go further than what we can do with the tools out-of-the-box — and the investment in a data warehouse is simply not warranted. Is not avoiding other IT systems the promise of the NetSuite platform?

If you found this article valuable, feel free to sign up for email notifications of new articles. If you would like to query for NetSuite data and are having trouble making Saved Search tools reach and perform as desired, let’s have a conversation.

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