NetSuite SuiteQL BUILTIN.DF Drops Rows on NULL

A SuiteQL query that returns fewer rows after you add BUILTIN.DF may not be a filter problem. In the case reported in the source thread, NetSuite appeared to evaluate BUILTIN.DF eagerly and drop the entire row when the input was NULL, even with the call inside a CASE expression. Watch for it with reference fields like Account, Subsidiary, or Entity on transaction accounting lines where the line can be empty.

Why BUILTIN.DF Silently Removes Rows

BUILTIN.DF resolves a numeric internal ID to its display value. The documentation lists it alongside BUILTIN.CURRENCY, BUILTIN.CONSOLIDATE, and BUILTIN.HIERARCHY as a supported SuiteQL built-in. In practice the function works fine for populated references.

In the reported case, the problem appeared when the source column was NULL. NetSuite did not return the row with a NULL display value; the row was missing from the result set entirely. Stripping BUILTIN.DF from the SELECT increases the row count, which is the first clue.

If the row count goes up when you remove a BUILTIN.DF column, check that column's source for NULLs before you touch the filters.

This is different from standard SQL, where a function returning NULL keeps the row. We couldn't find row elimination for BUILTIN.DF described in Oracle's documentation. What we have is the behaviour reported in the source thread, where rows went missing once BUILTIN.DF received a NULL reference. Oracle's own SuiteQL performance guidance recommends BUILTIN.DF over a join when you only need a display value, so don't treat this as a rule. Test it in your own account.

How Eager Evaluation Breaks CASE Guards

A common guard is:

CASE WHEN TransactionAccountingLine.Account IS NOT NULL 
     THEN BUILTIN.DF(TransactionAccountingLine.Account) 
     ELSE NULL END

The intent is to call BUILTIN.DF only when the column is not NULL. In the reported case, NetSuite still evaluated BUILTIN.DF before the CASE logic ran, and reordering the branches or nesting the call did not help. If any BUILTIN.DF in the SELECT received NULL for its argument, the row disappeared.

The same eager evaluation applies to joins. A left join that produces NULLs for the right side will cause rows to vanish once BUILTIN.DF references the joined column.

Reproduce the Behavior with a Minimal Query

Run a baseline without display functions using SuiteQL through the N/query module in SuiteScript:

SELECT
  t.tranid,
  tal.account
FROM Transaction t
JOIN TransactionAccountingLine tal ON tal.transaction = t.id
WHERE t.type = 'VendBill'
  AND t.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')

Note the row count. Add a display function:

SELECT
  t.tranid,
  BUILTIN.DF(tal.account) AS account_name
FROM Transaction t
JOIN TransactionAccountingLine tal ON tal.transaction = t.id
WHERE t.type = 'VendBill'
  AND t.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')

If some accounting lines have a NULL account, those transactions disappear from the result set. The WHERE clause is unchanged, only the SELECT changed.

Aggregates Hide the Drop and How to Detect It

Row loss is easier to spot with COUNT(*), but aggregates can mask it. A query that sums amounts by account will under-report totals once BUILTIN.DF drops NULL-account lines.

Run a sanity check before and after adding the display function:

SELECT COUNT(*) AS line_count, SUM(tal.debit) AS total_debit
FROM Transaction t
JOIN TransactionAccountingLine tal ON tal.transaction = t.id
WHERE t.type = 'VendBill'

Then add the display column. Put it in a subquery so the outer aggregate stays valid:

SELECT COUNT(*) AS line_count, SUM(x.debit) AS total_debit
FROM (
  SELECT tal.debit, BUILTIN.DF(tal.account) AS account_name
  FROM Transaction t
  JOIN TransactionAccountingLine tal ON tal.transaction = t.id
  WHERE t.type = 'VendBill'
) x

If the row count falls from 1,240 to 1,182 after adding BUILTIN.DF(tal.account), 58 lines had a NULL account and were removed. The SUM will also drop, often by a material amount. Compare the two results and keep both for the audit trail.

This check matters for any report that feeds a reconciliation, such as a vendor bill total compared against the AP balance. Dropping lines silently changes the number.

Safe Patterns to Get Display Values Without Losing Rows

Replace BUILTIN.DF with an explicit lookup when NULLs are possible. Joins preserve rows, functions do not.

Explicit join for Account display

SELECT
  t.tranid,
  a.<display_column> AS account_name
FROM Transaction t
JOIN TransactionAccountingLine tal ON tal.transaction = t.id
LEFT JOIN Account a ON a.id = tal.account
WHERE t.type = 'VendBill'
  AND t.trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')

Choose the Account name or number column you want to show. Look up the exact field ID in the Records Catalog before you run it. LEFT JOIN keeps the transaction line even when tal.account is NULL, and the display value will be NULL instead of dropping the row.

Explicit join for Subsidiary on transaction lines

The same pattern applies to TransactionLine.Subsidiary, TransactionLine.Item, and other reference fields:

SELECT
  tl.item,
  s.<display_column> AS subsidiary_name
FROM TransactionLine tl
LEFT JOIN Subsidiary s ON s.id = tl.subsidiary
WHERE tl.transaction = 12345

If you need multiple display values, join each reference table once. The cost is a few extra joins versus one BUILTIN.DF call, but row integrity is preserved.

Keep BUILTIN.DF only when NULL is impossible

BUILTIN.DF is acceptable for mandatory references that are never NULL in your data model, such as Transaction.entity on a posted bill. For optional references, custom records, or lines that can be blank, use an explicit join.

When you must use BUILTIN.DF, filter NULLs out in the WHERE clause before projection:

SELECT
  t.tranid,
  BUILTIN.DF(tal.account) AS account_name
FROM Transaction t
JOIN TransactionAccountingLine tal ON tal.transaction = t.id
WHERE t.type = 'VendBill'
  AND tal.account IS NOT NULL

This prevents row loss, but also removes the lines you may want to see. Choose the approach based on whether NULL lines are meaningful for the report.

Check results after each added display column. If the row count drops, a BUILTIN.DF call is receiving NULL. Swap that column for a left join and the count stabilizes.