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:
- Minimum Summary Type: You must select ‘Minimum” or you will get an error.
- 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.
'<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.
See Related Articles
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
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?
Hello Eric,
Have you tried wrapping the result set into something like this? REPLACE(MAX({guid}) ||’,’|| SUM({amountremaining}) || ‘,’ || concat({number}), ‘,’, ‘ ‘)
Marty
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
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!
Is there a way to run another aggregate within this? I’m using LISTAGG, and within it I’m concatenating Shipping Address State and quantity sold. It’s listing each state multiple times, with the quantity of each invoice. I’d like the sum of the quantity for each state. Window Function???