Reveal Both Aggregate and Detail in NetSuite Saved Search

This article is relevant if you are looking to produce a NetSuite Saved Search that has both summarized information and related detail.

Background

During NetSuite’s Suiteworld 2014 (#NSW14) annual event, one of the sessions was on NetSuite Advanced Searches.   The session discussed an undocumented formula function called NS_CONCAT.  When the session attendees realized that they could show both summary and detail information on a single row in a saved search, they all yelped with delight!  Let’s look more closely at it.

How the NS_CONCAT Function Works

NS_CONCAT works as an aggregate function.  The idea is that you use the function to roll up the detail, in string format, as a comma separated list.  That list then can be formatted to draw nicely to the end user.  When you use this function, keep this in mind:

  1. Minimum Summary Type:  You must select ‘Minimum” or you will get an error.
  2. String: you must supply string type data to the function.  Use the TO_CHAR() Oracle PL/SQL function to help convert from other data types.
Here is a formula that will format currency data nicely on screen:
'<div style=text-align:right;>' || REPLACE(NS_CONCAT(TO_CHAR({amount}, '$9,999.00')), ',', '<br>') || '</div>'

Expand Your SuiteScript NS_CONCAT Thinking

The possibilities available in this function get me excited.  Many times, when writing SuiteScript, I need to perform a search and I need both detail and summary information.  Often, I need the internalids of the result set so I can act on them.  Now, if I can get a comma separated list of internalids in a single result, I can then use a JavaScript split function to quickly throw them into an array.  This new capacity may help us break through the imposed search result row limits.  I am sure there will be things to watch out for — such as the size of the text buffer in column being returned. Let’s have at it!

Get Help to Enhance your Reports and Saved Searches

The NetSuite platform is great for taking control over the situation.  If you need assistance with your NetSuite account, contact us.

 

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube

| Tags: , , , , , | Category: NetSuite, Reporting, Technical | 54 Comments

54 thoughts on “Reveal Both Aggregate and Detail in NetSuite Saved Search

  1. Donna Balmes says:

    Hi, May I know what is the NS_Concat formula to be used when I wanted to show both the GL impact on Checks record in 1 line in Saved search?

  2. Marty Zigman says:

    Hello Eric,

    Have you tried wrapping the result set into something like this? REPLACE(MAX({guid}) ||’,’|| SUM({amountremaining}) || ‘,’ || concat({number}), ‘,’, ‘ ‘)

    Marty

  3. Marty Zigman says:

    Hello Donna,

    What happens when you perform NS_CONCAT({account}) on a check transaction? To get everything to be on one line, make sure that you group on document number only.

    Marty

  4. Brandon Flippo says:

    Good afternoon, Marty. When using this functionality for Ship Dates, how do I sort the “Detail” by date within each line?

    Example: ” || REPLACE(NS_CONCAT({shipdate}), ‘,’, ”) || ”

    Thanks!

Leave a Reply

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