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 netsuite@rsmus.com or by phone at 855.437.7202.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Receive Posts by Email

Subscribe to the NetSuite Cloud ERP blog and receive notifications of new posts by email.