Get Record Display Names without JOIN in SuiteQL

When querying NetSuite’s SuiteAnalytics SQL, many record fields are stored as internal IDs (e.g., customer, employee, item). To retrieve the display name or label (just like what’s shown in the NetSuite UI), you can use the powerful BUILTIN.DF() function.

What is BUILTIN.DF()?

BUILTIN.DF() is a special NetSuite SQL function that returns the default display value (aka the name/label) of a foreign key field. It’s part of NetSuite’s BUILTIN function suite.

Syntax:

BUILTIN.DF(field_reference)

Example Usage:

SELECT
  id,
  tranid,
  BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF(transaction.entity)) AS customer_name,
  BUILTIN_RESULT.TYPE_STRING(BUILTIN.DF(transaction.employee)) AS sales_rep
FROM
  transaction
WHERE
  type = 'SalesOrd'

🔍 Explanation

• transaction.entity: returns internal ID of the customer.
• BUILTIN.DF(transaction.entity): converts it into the actual customer name.
• BUILTIN_RESULT.TYPE_STRING(...): ensures the result is treated as a string in SQL output.

This is especially useful when the field points to a record, and you want the name, not just the ID.

Benefits

• Mimics how names appear in the NetSuite UI.
• No need to join related records manually.
• Keeps queries clean and readable.