Render NetSuite SQL Queries Like Saved Searches

This article is relevant if you understand the capabilities of NetSuite Saved Search yet you need the reach of SuiteQL (SQL).

Background

I am in gratitude. With the advent of the N/query module in 2019, NetSuite has delivered the ability to programmatically define queries and use these in SuiteScripts. This opens up a world of possibilities where saved search simply could not reach the information that is tucked away in the NetSuite database.

Many have observed that the SuiteAnalytics Workbook is promising. But it simply does not yet deliver on much need capacities today. While I am confident in the NetSuite product team’s vision around that tool, we must take care of situations now.

I recently wrote about how we rewrote our license-free Content Renderer Engine 2.0 with NetSuite SuiteQL to allow multiple saved searches and SQL queries to join together to create a data universe that can then be used to generate PDF, HTML documents as well as reports — it can go even further and be used for application integration and be a building block for tools such as fast and flexible bulk record generators.

I must appreciate that Boban D., a senior consultant on the Prolecto Resources team, devoted himself to exploiting the power of the NetSuite SQL engine. Not only did he create the CRE 2.0 Engine, he also crafted our NetSuite SQL Query Tool which is foundational for administrators to craft NetSuite SQL queries.

Boban D. went further! Working with Hector C., another senior consultant on the Prolecto team, the two of them set out to solve client challenges using NetSuite SuiteQL that are easily available in the Saved Search point-and-click customizations accessible to most business analysts and NetSuite administrators.

Thus, this article discusses a new Prolecto Query Renderer tool that is available free of license charge to all of our clients.  Click images to see full screen examples of the tool.

Render SQL Queries in NetSuite Similar to Saved Search

The Prolecto Query Renderer tool was conceived as a substitute for many of the features built into NetSuite’s Saved Search. Thus, the tool supports an analyst to use these key capacities without the need to program:

1. Menu of Queries: NetSuite SQL-based outputs can be defined as a list of descriptive queries to offer to end-user communities. This menu can be offered in any role, including anonymous contexts.
2. Role and User Permissions: Not only can the queries be restricted to either a role or user, but specific field level content can also be further restricted. An administrator should exercise care because the tool allows you to bypass NetSuite’s built-in data restrictions and then tailor restrictions query-by-query. This opens a world of data expressions that otherwise may not be possible.
3. Dynamic Filters: a world of dynamic filter definitions is possible to allow administrators to create data inquiry systems. You can create filters that only have a small list of data values which allow you to tailor the experience.
4. Flexible Sublists: any record in NetSuite can be used to host the dynamic output of SQL-based result sets. Besides the typical results that are on tab or subtab, the tool can target the results to a single HTML field or placement on the main form. Thus, there is greater flexibility for the placement of result sets where it is most relevant.
5. Output Types: results can be formatted as a simple list with or without a page navigator. Furthermore, an HTML output type is available that can then use CSS to produce any type of display needed.
6. Field Output Options: by defining each field output option, you can format and enhance the rendering. For example, a boolean true/false field can render a checkbox or a Yes/No.
7. Row Highlighting: using result-based criteria, you can define HTML CSS to act on qualifying rows with a particular display treatment.
8. CSV Outputs: all outputs can be downloaded as CSV files. This is true even if the tool is rendering as a sublist on another record. Very convenient when working with lists hanging off contextual business records.  You can’t do this on native sublists and it sure would be nice if we could!
9. Drill Around: each field can be an opportunity to link to native NetSuite records. But consider that you can create links that navigate to other queries to dynamically create a rich “drill around” capacity that is next to impossible to do with NetSuite Saved Search.
10. Pivot: a simplified version of data pivot is offered that allows administrators to express information that is defined as row summaries into display columns. Consider outputting a sorted column of accounting periods by defining a simple SQL summary query that aggregates naturally on posting period.
11. Long Data Reach: indeed, SQL finally allows us to Solve Distantly Related NetSuite Saved Search Join Challenges. Consider how we can now render, on a sales order, information that is linked to a drop-ship purchase order and its related vendor bills. Using status, we can highlight information that is distant yet very meaningful to the user.
12. Execution History: every time the Query Renderer runs, a log of the SQL and related statistics is recorded affording you the ability to watch the performance and produce other diagnostics. You simply can not do this with Saved Search.

In due course, we anticipate ongoing enhancements to the tool as client situations present themselves.

Watch A Demonstration (13:54)

To fully appreciate how the tool can effectively replace a Saved Search for list rendering, you can watch a demonstration (13:54) of the Prolecto Query Renderer tool.

Solve Your NetSuite Saved Search Rendering Challenges

Like all the tools and algorithms offered by my firm, the Prolecto Query Renderer is available upon request to our existing clients.

Naturally, we assist our clients to solve their specific challenges by helping them work with our NetSuite expertise, knowledge of accounting, and innovation capacity. The real value of working with our organization is not our license-free tools — but that you are never really stuck — instead, building a relationship with a team of professionals that hold high standards for care means you indeed can realize the promise offered by the NetSuite platform.

If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you need to solve your NetSuite data expression using SQL but you are challenged to get this information to your users, 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