Contact Us
Three men collaborating over a whiteboard diagram, while one points to a specific point on it.

Utilize SuiteQL and custom JSON fields to extend NetSuite’s vendor payment and check PDF transparency, exposing all applied bills and credits—including those fully consumed by credits—directly within the PDF.

The Problem

  • Bills fully offset by vendor credits do not appear on Vendor Payment or Check PDFs.
  • Advanced PDF Templates cannot source those bills, their amounts, or any custom fields out of the box. Even native fields have limited access.
  • The PDF total often varies from bills less credits shown, confusing vendors.

Goal

  1. Display bills fully consumed by vendor credits on the vendor payment
  2. Source custom fields and inaccessible native fields to the vendor payment
  3. Improve vendor visibility to payments and credits utilized

Context

NetSuite provides a strong foundation for vendor payment and check PDFs, but certain scenarios - like bills fully offset by credits - can benefit from additional visibility.

By leveraging two custom JSON fields on the Vendor Payment record, and a user event script with SQL injected, we enhanced NetSuite’s standard behavior to make all applied bills and credits available to Advanced PDF Templates. The result is clearer, more complete payment documentation.

Once the data is on the record, Advanced PDFs can finally use it.

  • Works for Vendor Payment PDFs
  • Works for Check PDFs
  • Supports custom fields from bills and credits
  • No changes to native NetSuite logic

How It Works

  1. Capturing Applied Vendor Credits

We query the credit application chain directly, leveraging a join on the applied credit table to access bill credit data:

JOIN AppliedCreditTransactionLineLink acl
    ON vp.id = acl.paymenttransaction

This reliably returns every credit applied to the payment and allows us to source additional native and custom fields inaccessible via the Advanced PDF Template alone.

  1. Capturing All Consumed Bills

This is a critical piece - a subquery, a union, and a join on the previous transaction table expose bills and bills consumed by credits on the payment:

FROM PreviousTransactionLineLink PTLL
                JOIN Transaction bill
                    ON bill.ID = PTLL.PreviousDoc

+

FROM Transaction vp
                JOIN AppliedCreditTransactionLineLink acl
                    ON vp.ID = acl.PaymentTransaction
                JOIN PreviousTransactionLineLink ptll
                    ON ptll.NextDoc = acl.NextDoc

By following both direct and credit-driven paths, we get a complete list of bills—and their true paid amounts.

  1. Storing the Results as JSON

SQL queries aggregate the results, and the script updates the payment record on save to store the data in the two JSON fields:

 var consumedBillsJson = JSON.stringify(consumedBillsResults || []);
        record.submitFields({
            type: record.Type.VENDOR_PAYMENT,
            id: paymentId,
            values: {
                custbody_consumed_bills_json: consumedBillsJson ...
  1. Parsing the JSON via Advanced PDF 

Once the JSON lives on the payment record, parsing it in Advanced PDFs is straightforward.

 <#assign consumedBills = record.custbody_consumed_bills_json?eval />

The same approach works for credits, checks, and any custom fields you include in the SQL.

The Result

  • Vendor Payment and Check PDFs now display all applied bills and credits, including bills fully consumed by credits.
  • PDF totals accurately reconcile with the underlying transaction activity, eliminating confusion for vendors and AP teams.
  • Advanced PDF Templates can source custom fields and previously inaccessible native fields from bills and credits.

(I would love to deploy in a demo environment and include the same mock-up below without sensitive data from a client)

image.png

Related Articles

Tagged with Troubleshooting, Financial Management, Training