Published on January 25, 2024.

I've written many times about how helpful the BUILTIN.DF function can be when you're developing NetSuite SuiteQL queries. I often use it to get the name associated with a related record, and with BUILTIN.DF you can do that without joining to the related record.

For example, in this query I'm using the function to get the Sales Reps that are assigned to customers, and I'm also using it (in combination with the REPLACE function) to get customer statuses.

SELECT
	ID,
	CompanyName,
	BUILTIN.DF( SalesRep ) AS SalesRep,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status	
FROM
	Customer
ORDER BY
	CompanyName

Using BUILTIN.DF With Multiple IDs

Recently, while working on a custom mobile app, I used the BUILTIN.DF function in a different way. One of the app's functions provides users (in this case sales reps) with a way to get a quick, high-level overview of their customers. The reps wanted a way to see the contacts associated with each customer.

There's an interesting column available to us via the Customer table. The column is ContactList, and it provides a comma-delimited list of the IDs of all contacts that are related to the customer. For example, this query...

SELECT
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep,
	BUILTIN.DF( ContactList ) AS ContactNames,
	ContactList AS ContactIDs
FROM
	Customer
WHERE
	ID = 1064

...returns this...

Click the image to view a larger version.

Notice that the ContactList column (which I'm aliasing as "ContactIDs") contains three integer values: 1502, 2587, and 2588. Those are the IDs of the contacts that are related to that customer.

By itself, the ContactList value, and the list contact IDs that it provides, isn't very useful. But it is helpful when you combine it with the BUILTIN.DF function. If use the column with the BUILTIN.DF function, it will return the names of the related contacts.

Continuing with the query above...

SELECT
	ID,
	CompanyName,
	REPLACE( BUILTIN.DF( EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( SalesRep ) AS SalesRep,
	ContactList AS ContactIDs,
	BUILTIN.DF( ContactList ) AS ContactNames	
FROM
	Customer
WHERE
	ID = 1064

And here are the results.

Click the image to view a larger version.

I think the takeaway here is that you can use the BUILTIN.DF function on both a single record ID and a comma-delimited list of record IDs. And if the IDs and names of the related contacts are all that you need, then this technique can be very helpful and convenient.

Other Supported Tables

This technique also works with a few additional similar tables, including the Vendor, Partner, Job, and OtherName tables. (Those tables also include ContactList columns.)

For example...

SELECT
	ID,
	CompanyName,
	BUILTIN.DF( ContactList ) AS ContactNames,
	ContactList AS ContactIDs
FROM
	Vendor
ORDER BY
	CompanyName

SELECT
	ID,
	CompanyName,
	BUILTIN.DF( ContactList ) AS ContactNames,
	ContactList AS ContactIDs
FROM
	Partner
ORDER BY
	CompanyName

Getting Additional Related Contact Info

If you do need additional information about a company's related contacts, then you'll need to use a few JOINs. Start by joining from the Customer to the CustomerCompanyContact table, and from there to the Contact table.

For example, consider this query...

SELECT
	Customer.ID,
	Customer.CompanyName,
	REPLACE( BUILTIN.DF( Customer.EntityStatus ), 'Customer-', '' ) AS Status,
	BUILTIN.DF( Customer.SalesRep ) AS SalesRep,
	Contact.EntityTitle,
	Contact.Email
FROM
	Customer
	INNER JOIN CustomerCompanyContact AS CCC ON
		( CCC.ContactsCompany = Customer.ID )
	INNER JOIN Contact ON
		( Contact.ID = CCC.Contact )
WHERE
	Customer.ID = 1064

Which returns this result.

Click the image to view a larger version.

Wrapping Up

As always, I hope you find this post - and the queries I've shared in it - to be helpful.