SuiteScript Example - Rendering Query Results in a PDF

Created: November 14, 2020

With the advent of Analytics Workbooks and the N/query module, NetSuite has also added a method for rendering your query results within your PDF templates. Let's say you have a function to define your query, like so:

function findIndividuals () {
  const customerQuery = q.create({
    type: q.Type.CUSTOMER,
    columns: [
      { fieldId: 'email' },
      { fieldId: 'firstname' },
      { fieldId: 'lastname' }
    ]
  })
  
  customerQuery.condition = customerQuery.createCondition({
    fieldId: 'isperson',
    operator: q.Operator.IS,
    values: true
  })
  
  return customerQuery
}

Notice we don't actually run the query; we just create the Query instance. From there, we can leverage a TemplateRenderer from the N/render module and its addQuery() method, like so:

function renderPdf (response) {
  const xmlContent = file.load({ id: './custom-query.ftl' }).getContents()
  const renderer = render.create()
  renderer.templateContent = xmlContent
  renderer.addQuery({
    templateName: 'individuals',
    query: findIndividuals(),
    pageIndex: 0,
    pageSize: 10
  })
  
  const pdf = renderer.renderAsPdf()
  response.writeFile({ file: pdf, isInline: true })
}

where ./custom-query.ftl (our custom template) contains a section like this:

<h4>Individuals</h4>
<p>
  <#list individuals![]>
  <table>
    <tr>
      <th>#</th>
      <th>Individual</th>
      <th>Email Address</th>
    </tr>
    <#items as ind>
    <tr>
      <td>${ind?counter}</td>
      <td>${ind[1]} ${ind[2]}</td>
      <td>${ind[0]}</td>
    </tr>
    </#items>
  </table>
  <#else>
    None.
  </#list>
</p>

The templateName we specify in addQuery() is the same variable name we use within our template to access the query results; in our example, that's individuals. From there, we can loop through (#list) the results, and we access values from individual columns with Array indexes (e.g. ind[1]). The order of the columns in the template is the same order in which they were defined when we created the Query.

Combining all those components renders a PDF like so:

HTH

-EG

This is an example from the Rendering PDFs with SuiteScript Cookbook.