Published on March 17, 2022.

In this post, I'm going to share a few SuiteQL queries that you can use to get information about the custom lists that have been setup in a NetSuite account. This is a topic that I get a lot of questions about, primarily from developers that are trying to get access to the values in a custom list for use in both NetSuite-native and external applications.

The CustomList Table

Let's start by examining a table that can be very helpful when it comes to discovering the custom lists in a NetSuite account. That table is "CustomList."

Take a look at this query.

SELECT
	Name,
	Description,
	ScriptID,
	BUILTIN.DF( Owner ) AS Owner,
	IsOrdered
FROM 
	CustomList
WHERE
	( IsInactive = 'F' )
ORDER BY
	Name

And here's a sample of the results.

As you can see, this table gives everything we need to know about the instance's custom lists - from the Name, Description, and Script ID (which we'll use in just a minute), to the list's Owner, as well as an indication as to whether the list's values should be displayed in a sorted (IsOrdered) manner, or in the order that the values were entered.

Notice that I've excluded inactive custom lists from the results.

Getting A Custom List's Values

I mentioned above that a list's ScriptID value is important and helpful. The reason is that for each custom list, NetSuite creates a table in which the list's values are stored. And the name of that table is the list's ScriptID.

For example, in the list of tables shown above, one of the value lists is named "Bed Size" and has a ScriptID of "CUSTOMLIST_BED_SIZE." To see the list's values, we'd simply query the "CUSTOMLIST_BED_SIZE" table, like this.

SELECT
	Name, 
	ID
FROM
	CUSTOMLIST_BED_SIZE
WHERE
	IsInactive = 'F'
ORDER BY
	Name

The results look like this.

As you can see, the query returns the lists values. And for each value, the display name and actual value is returned. In other words, for the "Full" list option, the actual value that is stored is the ID value of 2.

If you look at the results of the first query, you'll see that the Bed Size custom list is configured so that the values are ordered (i.e. the IsOrdered value is true). As a result, to display the list's values properly, they should be sorted by name. And as you can see in the query against the CUSTOMLIST_BED_SIZE table, I'm sorting them using "ORDER BY Name."

And finally, also notice that, just as I did with the query against the CustomList table earlier, in the query against the CUSTOMLIST_BED_SIZE table, I'm filtering out inactive values in the value list.

Wrapping Up

In this short post, I've shared a few queries that you can use to access custom lists with SuiteQL. Whether you're a developer that's creating NetSuite-native solutions or external applications, if you need to access custom lists, then I think you'll find these queries to be helpful.