NetSuite’s hidden SQL formula

By - May 15, 2019

Do you know about the NetSuite formula ‘ns_concat()’?  This formula is used to display the output as a single comma delimited string.  In order to use the formula in a saved search you need to set the summary type to ‘minimum’ or ‘maximum’ and use the formula(text) field.  Below is an example of a saved search using the ns_concat() formula:

Scenario: You want to have a saved search showing the subsidiaries for each GL account on a single line. You select the account saved search and set the result fields to be

  • Name ~summary type = group
  • Subsidiary (No Hierarchy) ~summary type = group

Output: Multiple lines for each GL displaying a single subsidiary (for example a GL that can be used for 5 subsidiaries appears 5 times, once for each subsidiary)

Solution: Set the result fields to be

  • Name ~summary type = group
  • Formula (text) = ns_concat({subsidiarynohierarchy}) ~summary type = maximum or minimum

Output: Each GL account appears on one line with the subsidiaries displayed in a comma delimited string


For more information on this topic or others related to NetSuite, contact RSM at or by phone at 855.437.7202.

Receive Posts by Email

Subscribe and receive notifications of new posts by email.