Web Development

Comma Separated List of Values in NetSuite Saved Searches

I had a problem recently whereby I needed to get out the values from a multi-valued field in a NetSuite saved search as a comma deliminated string field.

Let’s take an example; imagine I have a field on a customer record called “Interested Subjects” and it contains a selection of marketing subjects which interest the user. The point of this post is to be able to get those interested subjects out in a single string which is comma deliminated.

Normally, you can do this with stragg or listagg in pl/SQL however, NetSuite does not actually allow those operators. So what can we do instead? Well it turns out NetSuite’s SQL API does stragg and listagg by default when you group.

To get this working simply group on a unique identifier per customer, I chose internal ID, and select “maximum” from the grouping options for your multi-valued field, in my case “Interested Subjects”. The result will be something along the lines of:

ID | Maximum of Interested Subjects
1  | Wildlife,Birding,Natural History

This is such a common problem I am sure this will help others.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s