This article is relevant if you seek to improve the output of NetSuite advanced PDF documents and you need to get access to information that is difficult to reach.

Background

In a recent NetSuite implementation, a client wanted to use the Invoice Grouping feature to produce a highly custom consolidated invoice statement. The invoice grouping feature leverages a grouping record to connect related invoices together. Gaining access to the invoice detail data related to grouped invoices creates an opportunity to produce different document presentations.

Before NetSuite offered the Invoice Group feature, I wrote about How to Produce a NetSuite Consolidated Invoice. That article has relevance today but from a different angle.

NetSuite’s Advanced PDF technology is a framework for producing high-quality documents. Since 2015, I have written many articles about how we extended the capacities of this native feature primarily through the use of a free-of-license charge Content Renderer Engine technology. See some of the following general references:

2022: Learn the Framework to Extend NetSuite Content Generation
2021: Content Renderer Engine 2.0 with NetSuite SuiteQL
2020: Join Multiple NetSuite Saved Searches to Build a Data Universe
2015: Video: How to Extend Advanced PDFs with Content Renderer Engine

Overcoming NetSuite PDF Generation Data Reach Challenges

NetSuite documentation generation often presents several significant real-life challenges to overcome. The most serious challenge is the ability to reach distantly related data elements from the native Advanced PDF tool.

If the NetSuite out-of-the-box method does not provide the information elements you need, the most common technique used by Administrators is to create custom fields to copy data from distant tables to the target recordtype so that it can be seen by the Advanced PDF technology. This causes some overhead by bloating the database and is error-prone.

The general approach NetSuite suggests is to use the scripting platform to reach the data you need and generate the related XML documents. Because the API is open, we can develop tools for this situation.

Instead, why can’t we use Saved Search, SuiteQL (SQL), Record Loads or other techniques conveniently to get to the data in question? Indeed, sometimes some data elements are reachable via the SQL engine but not in Saved Search (or vice versa). An approach to using the database’s natural capacities is far superior because we use the information’s relational nature to gather the needed data. Yet, this approach, while possible, needs better tools for Administrators to be highly productive.

Illustration of Approach to Pull Invoice Group Data Into PDF Generation Engine

With the free-of-license-charge Content Renderer Engine, we use all the good things NetSuite gives us with Advanced PDF technology, but we solve all those hard-to-reach data challenges without scripting.

In the case of our Invoice Group work, one of our consultants tried to use the following simple SQL syntax:

select * from transaction tr where tr.groupedto = 1

The consultant received the following error: Search error occurred: Field ‘groupedto’ for record ‘transaction’ was not found. Meaning, that the NetSuite platform does not always expose information elements in all of the data access technologies. So we need to hunt for another method.

The good news is that the ‘groupedto’ field is available from Saved Search. Thus, our consultant needed to reference a Saved Search with the following syntax:

{
	"type": "invoice",
	"filters":
	[
		["mainline","is","T"],"AND",["groupedto","anyof",${RECORD.id}]
	],
	"columns": ["internalid"]
}

The syntax above allowed our consultant to start with the Invoice Group record and get a list of all the internalids of the invoices connected to it. We combine general search logic with Advanced PDF template syntax to create dynamic queries. With the result of that list, another query could be generated to get all the data elements across those grouped invoices.

With the list of invoices, another query can get all the detail together (note, there are many ways to shape queries — which is part of the art). The idea is that you chain together saved searches and NetSuite SQL to create a rich data universe that can ultimately be supplied to a NetSuite Advanced PDF (Freemarker with BFO Syntax for more technically minded readers) to craft the desired document.

With a powerful tester tool, you can see all your business data making it much simpler to understand the template syntax needed to get the desired output.

Click the related images to gain a better understanding of the functions.

Empowered NetSuite Administrators and Power Users

This article is meant to show NetSuite Administrators and Power Users that you can answer questions that the business needs even if the native NetSuite tools appear to be limited. If you understand Saved Search and are learning NetSuite SQL and Advanced PDF template syntax, you can leverage our free-of-license-charge technology to expand your innovative capacities.  Like all of our intellectual property, we give this free of charge to NetSuite end users as a way to add value to a service relationship.

Our expertise helps NetSuite Administrators become more productive, which leads to better results. If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to extend your capacity to solve your NetSuite Advanced PDF challenge, 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