Join Multiple NetSuite Saved Searches to Build a Data Universe

This article is relevant if you use NetSuite and you are challenged to use Saved Searches or SuiteAnalytics Workbooks to reach data that is too distant to reach.

Background

Without question, NetSuite Saved Search is one of the most important tools to master to open up NetSuite’s capacities to get at and present information.  When working with Saved Search, the ability to link beyond a single table join many times becomes a challenge.  The community is pleased with SuiteAnalytics Workbooks that goes beyond by helping solve the single table join challenge.  Yet, even this long-time-coming reporting tool may have limitations because of the way or manner for which you need an angle to attack your query definition.

I have spoken before about this well known multi-join NetSuite challenge.  See these reference articles:

1. Learn NetSuite Two Dot Saved Search Notation for Distant Joins
2. How SuiteLets can Emulate NetSuite Saved Search to Join Multiple Searches
3. Solve Distantly Related NetSuite Saved Search Join Challenges

Yet, there are many times you need to gather up data that may take more than one search.    When you find you are in this situation, you must generally resort to using SuiteScript to run multiple searches and present the data in some manner.

The Popular But Less Ideal Approach to Copy Data

Due to the limitation of reach for data by the Saved Search tools, the most popular mechanism to overcome the situation is to create custom fields on tables that source data from other tables.  This often does the trick — until you find you need to actually store the data in the table and not be able to use on-demand, always fresh, lookup formulas.  Your costs to get this solved start to rise.  Furthermore, the database starts to get bloated and there is potential for out-of-sync situations.

Yet, what if there was a different way?

Linking Saved Searches to Create a Data Universe

In 2015, for a client that had a number of challenging requirements to generate complex shipping documents referencing multiple serial numbers, assembly builds, and configuration and certification information, I envisioned a pattern for linking Saved Searches that would allow us to organize information into an on-demand data universe.   We ultimately solved our client’s situation by building a tool called the Content Renderer Engine (or CRE for short).  Like all the intellectual property produced at my firm, we give this tool to our clients without a license fee.

The tool is designed to help NetSuite administrators build a data universe that then can be presented in many different ways — the most popular being an HTML page or PDF document.  However, once you have a data universe, you can do many things with it.  For example, we have produced complex NetSuite data integrations with other systems by generating flat, CSV, and XML files.  The point is that when one is presented with a NetSuite data requirement challenge, why not solve it by defining a data universe that can be used to solve that concern instead of carrying around a bunch of custom fields that may be used only in one use case?

A Background Business Problem which Needs Data

To make this more meaningful, we think about NetSuite challenges relative to business records.  Let’s use a customer payment record to help explain the need for data.  In our NetSuite Systems Integration Practice, we watch our customers’ accounts receivable payment patterns closely.  At the moment we detect that a customer makes a payment that doesn’t fit the way we previously invoiced, we can reliably predict that we are going to have issues that likely will increase the number of days our invoices will be paid — and our receivable follow up costs are going to rise.

When we are in this situation, we want to tell a story to our client.  In this story, we believe we are in an “out of sequence” or “partial payment” situation and we wonder why.  Thus, we need to show our client our perspective — perhaps they missed entering an invoice in their system and we can get back on track.   Showing this story demands more data than you can see from the NetSuite payment record. (click image to see a sample image of the story)

Defining a Conceptual Saved Search Data Tree

The idea behind the pattern is that we generally start with a business record like our customer payment above.  We then link to other records by joining with the payment record in question.  Hence, we define a Saved Search with the information we care about and then link it to the payment record.    Any field on the payment record can be used to join to another Saved Search as long as the values will equal (more operators are possible, but this is easiest to understand).  Any Saved Search we define can be the input for another Saved Search that needs to link.  We keep going linking up more and more Saved Searches until we are satisfied that we have all the data we need.

What becomes interesting is that we can have searches be expressed as aggregates (summaries) and these too link up.  The idea is to build up the data you need by conceptualizing a logical link strategy effectively unconstrained by a single Saved Search.  Once you have this power, a previously closed up world becomes open and able.  I suspect you love freedom like I do!

Getting Access to the Information

We built the tool to be able to inspect the data as you work with it so that you indeed can see that you linked correctly.  Furthermore, you can see the syntax of how you access the data nodes by also witnessing the data that was retrieved from the database.  The underlying data universe is JavaScript Object Notation (JSON) structure that is easy to consume in NetSuite’s technical environment.  The most natural place to pass the JSON is to an Advanced HTML/PDF template that can then transform the data universe to a document or other output.  In a previous article, Video: How to Extend Advanced PDFs with Content Renderer Engine, I demonstrate the tool in use.

Take Charge of Your NetSuite Data Access Challenge

One of the great things I love about the NetSuite platform is the ability to adapt to a business challenge.  When most say “no”, I usually say “yes” because having a deep appreciation for the architecture and the expansion capacity means that we can innovate our way to a solution.  Tools like the one we discussed are representations of solutions we have produced for our existing clients because we notice recurring patterns in the platform across our client base.  Every request to solve a problem becomes an opportunity to help all of our clients — and thus we work to deepen our relationship by demonstrating that we are in the knowledge and expertise game surrounding the NetSuite platform  — thus the tools become accelerators to solve our client’s immediate NetSuite platform concerns.

If you found this article helpful, feel free to receive notifications of other articles as I publish them.  If you are ready to tackle a NetSuite data challenge, 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