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.
Man, and here I was thinking I had nothing to learn from searches. Completely skipped that break out. This is super cool. Getting a comma separated list in a single line is awesome.
List of internal id’s, list of a customer’s contact’s emails or products owned……
Thanks for the heads up, Marty.
This could be used for all sorts of things. Though, one think i want to point out is that your formula will replace commas within numbers. To avoid this, you could first replace the commas in numbers with some other character, then run this formula, then replace the commas back in.
'' || REPLACE(REPLACE(NS_CONCAT(REPLACE(TO_CHAR({amount}, '$9,999.00')),',','~')), ',', ''),'~',',') || ''
It might not be pretty, but it gets the job done.
Ah yes. Thank you Aaron for the debugging and refinement.
Marty
This is great. One question: what about when you are displaying numbers in the thousands? Doesn’t your formula, which replaces all commas, split those numbers up with line breaks?
Indeed. Thank you Jeff. Aaron offered up a solution.
Marty
Marty,
Have you tried using this in a sublist? I can’t get the NS_CONCAT to work when viewing project transactions.
Thoughts?
Haven’t tried. Sublists often seem like second class citizens…
This is probably my favourite function in all of the saved searches. Netsuite should publish this article at the top of Suiteanswers.
Other possible applications:
– Showing project task status next to their names in a project search
– Project resources next to roles/assigned hours
– Sourcing the comma separated list into a custom field from a summary saved search, in a project for example, and using it to perform workflow actions (eg. Change project status on the basis of the comma separated list of project task statuses).
Hi Chris,
Those are great examples of use cases. I would love to see more. By the way, since you appear to be into Advanced Projects, we produced an Advanced Timesheet system here that goes well beyond NetSuite’s new Timecard feature:
https://www.enterprise-timesheet-manager.com/
Marty
Can this be used to show the components of an assembly build or Kit. We create assemblies and kits with serialized components and a serialized parent item. We oten create many assemblies or kits at one time rather than on demand. At first glance this looks like a great way to display the information I am seeking.
Hi Brett,
That’s a good question. I am accustom to think about this kind of query against transactional data, not master data. I suspect it may work if you can create an item search where the details are member components. Why don’t you give it a try and let us know what you find. It would be pretty useful.
Marty
Thanks for the article, Marty. I was able to implement the other day and it looks great.
The Excel, CSV, and PDF exports simply display the html code and the list of values, however. Is there a way to make searches using NS CONCAT more export friendly?
Hi Brent,
Good question. We need to remember that the output is simply strings. Hence, you don’t have to include HTML. Consider your target and output what you need.
Marty
FYI – If you try to NS_CONCAT long text fields, you’ll first need to cast them as VARCHARS
NS_CONCAT(CAST({notes} AS VARCHAR(1000))
Thanks for the tip Robert.
Brent, if you use an ascii character it will format friendly in excel and somewhat friendly in CSV.
REPLACE(ns_concat({title}),’,’,CHR(10))
I wonder if there is a way to provide unique detail. For example, we are building a list of customers, where each line is the customer listed once. Then for each column, we want to display all the brands we’ve ever sold to them. I can do that, but the brands are repeating as many times as they ordered a product with that brand instead of showing the brand listed just once. Any ideas how to solve this scenario?
If you are getting a brand list output, then it seems you are grouping on product as well. Can you bring forth a screen shot of your saved search?
Marty
Is there any way to order the NS_CONCAT results? It seems the default is by Internal ID, but could be random. I am using an item search and would like to order by a date field on transaction records (not necessarily the same order as the transaction date/Internal ID).
I don’t think there is a way from the search results. Many times, we are getting the results and then going the next step in JavaScript which is easy to parse and sort.
Also, consider using our Content Rendering Engine to get full control over the presentation. That result set can be consumed and organized by an HTML or PDF template. We are also working on a reporting portlet extending this capacity.
Marty
@emily – just accomplished this like so:
REPLACE(NS_CONCAT(DISTINCT {createdby}), ‘,’, ”)
Love this post.
LISTAGG is coming to simply our life:
” || LISTAGG(TO_CHAR({amount}, ‘$999,999.00’), ”) WITHIN GROUP (ORDER BY {datecreated}) || ”
Hi Carl,
Thank you for your suggestion. This looks most interesting and seems more in line with the Oracle approach to getting the data out. Here is more about LISTAGG.
Marty
Hi Marty,
Great blog you have here. I have hopefully a simple question.
I’m trying to make a saved search, that displays a formula text field. In this formula text, I need to display the agents internal ID (the rep/agent that is viewing this field). Would you be able to help me with this?
Regards,
David
That’s straight forward so long as they are linked. Have you tried to browse the field list in the formula builder to find the right field? Have you tried to use TO_CHAR{agent}) type reference? Otherwise, if you mean the user’s ID, that one is a bit tougher. One possibility is to create a custom field that uses a formula and then displays the user’s ID dynamically as the source.
Marty
Thank you for sharing the information on ns_concat. It’s working great in a saved search that displays tons of info about the item in addition to the ‘date || PO number’ as the detail within it EXCEPT that the dates and POs are not sorted. Is there any way to either have the ‘Date|| PO number’ displayed in the saved search sorted newest to oldest?
Margery, I suspect the issue is because you are concatenating strings and thus dates are not sorting as you want. I always format my dates as yyyymmdd where today’s date is 20180127. This produces the sorting I want. Will that work for you?
Marty
Wow – thanks for the quick response. So if I understand correctly I need to convert my date nested within the ns_concat statement. Is that part correct? Right now I have 2 formulas (one after the other) using ns_concat. The first grabs the vendor name and the 2nd grabs the PO#. I think I misspoke in my last message and said something different. So to get this to work would I need to concatenate (or maybe group) those fields with the date in your suggested format? Or could I just do the date field in it’s own formula using the ns_concat and then the other 2 formula fields will naturally just follow the same order as the date? What I don’t want to happen is the date field to be correctly but not related to the vendor or PO# field. So I’m not sure if separate formulas will work of if I have to concatenate all 3 (which is probably the reason why it has the word “concat” in it).
Margery, I believe you are looking for something like this:
replace(regexp_replace(ns_concat(distinct to_char({today}-{transaction.trandate},’99999999′)||’|’||{transaction.trandate}||’ – ‘||{transaction.number}||’ (‘||{transaction.mainname}||’)’),’\d+\|’,”),’,’,”)
This should return your desired data in one field. You can always split it out into multiple fields and each field should return the correct order as long as the fields ns_concat formula conains:
distinct to_char({today}-{transaction.trandate},’99999999′)
Hi Marty,
Wondering if there’s a way to space the amount column if the account column has more than one line just from being really long.
My amounts aren’t lining up because of the issue
https://photos.app.goo.gl/TmdoPUpbsHS0nGJ03
Thanks
Hi Gabriel, Have you tried to space the account column? to make sure 1 account should not breaking into multiple rows. You might be able to accomplish that by adding html space to the account column label.
Carl
Hi Marty,
Love the blog learning lots of great tricks, thank you so much!
I’m having a little bit of trouble with a modification to this concept and not sure if you’ve dealt with it before. Basically I’m trying to combine Transactions + Time records into a single search, the problem is that I get duplicate records. By swapping SUM for a sort of IF COUNT = 1 I was able to get the data combined as I needed it, however it’s sort of useless because it won’t let me aggregate the data so I end up with a very large out-of-system only sort of data set.
Here is one of the formula using the MIN/MAX summary type:
SUM(CASE WHEN Count({transaction.lineuniquekey}) OVER(PARTITION BY {transaction.lineuniquekey} ORDER BY {time.internalid} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) = 1 AND {transaction.type} = ‘Sales Order’ THEN ABS({transaction.amount}) ELSE 0 END)
Basically when the count of transaction’s internal ID = 1 grab the amount else be zero, and I want to be able to sum this because I have other fields set up to grab time/actual expenses and things like that. Hoping you have some helpful advice but if not no problem!
Hi Ben,
It’s a challenge to sort this out without getting closer to it. That’s an advanced use case that demands real fundamental understanding of the Oracle Analytical function. But I do see something that may be valuable. When we developed the NetSuite Enterprise Timesheet Manager and related article, we discovered that NetSuite does store the actual timesheet records on invoices but it is not available on Saved Search. But if you load the record via the API, you can get the reference. Thus, we added a custom column and then crated a user event to commit the reference to the time record upon update on the invoice record. This dramatically helped with saved search operations. Perhaps I should write an article about it if you are interested.
Marty
Hi Marty,
I’m using ns_concat to get the internal ids of grouped results. The issue is that when there is more than a couple hundred Ids grouped together, the formula causes the search to error out. I’m assuming because of a character limit on the formula field. Any ideas for a workaround? I was thinking of maybe somehow having it check for length and split on multiple columns.
Hi Alex,
How many records do you expect it to return in the ns_concat result? Can you confirm it is this call producing the timeout? Might you be able to produce a limit on the string output result? I suspect the usefulness of this function diminishes as the number of ns_concat results increases.
Marty
Hi Marty,
Can you tell me what the issue with this string is?
{custbody_contact.entityid} || ‘ – ‘ || {custbody_contact.phone} || ‘ ✉ ‘
Reuben,
This post is off topic. It seems like you may need help with saved searches. I can have one of our consultants help you. Please contact me at https://www.prolecto.com/services/netsuite-care/
Marty
Not familiar with complex searches. Is it possible to have ‘double’ aggregation in a search.
I am trying to create a search showing a summary ‘table’ with the following info
– number of unique customers that purchased 1 time
number of unique customers that purchased – 2 times
number of unique customers that purchased – 3 time
number of unique customers
– total revenue
-number of orders
for a particular date range
Hi Raul,
This is a good problem. We definitely would solve it with our Content Rendering Engine CRE (here is one reference article). I was able to create a saved search that I would feed to the CRE technology so that we can then summarize even further. Here are the results of the saved search.
We don’t charge for the tool but we do ask for an engagement to make sure you learn how to help yourself with it.
Hi Martin,
I have a requirement in which user(accounting role) should only able to create a new Budget Category. but the user should not able to access other Accounting List or should be restricted to only New Budget Category.
If we remove the permission user will not able to access and If we provide access to a user then he can create or access all other accounting lists that he should not able to access.
Please let me know if there is any workaround.
Hello Anil,
This question is off topic. I recommend sending me a request here: https://www.prolecto.com/services/netsuite-care/
Marty
Having great fun with this making a calendar showing lists customer deliveries by day of the week as a search I can display as a search Portlet on a dashboard. Many thanks.
Do you know how I can override the vertical centering on portlet results. The long lists NS_Concat returns look terrible centred!
Hi Stuart,
We have to hack at CSS to get control over layout when conventional methods do not work. Good luck!
Marty
Hi! Wonder if you can help on this one?
I need to pull out sublist data per column. If I don’t use the DISTINCT function I get a ton of unwanted repeated data, but adversely if I do, any similar values are stripped out. On that basis I’m having to include the internal ID as one of the preceding fields in order to get it to work..
NS_CONCAT(DISTINCT({custrecord_project_shipping_service_prj.internalid}) || ‘ ‘ || {custrecord_project_shipping_service_prj.custrecord_project_shipping_service})
That then gives me an output like this..
13 example 1, 14 example 2
Problem is that I don’t want the ID! Am I over thinking this one? Or otherwise is there a way of stripping the ID out by wrapping it in a regex_replace or something?
Hope you can help!
Hello Matt,
Check out this article. It solves the challenge of getting at the NS_CONCAT data via regular expressions which then can help you see a pattern to parse out what you need:
https://blog.prolecto.com/2020/05/30/convert-netsuite-ns_concat-to-hyperlinks/
Marty
Hi,
I’m trying to use NS_Concat to create a saved search that will show Bin number, then the count of items in that bin, and then ns_concat for a subgroup of all the item names in that bin. Can you help me? I’m a novice so as much detail as you can give is very appreciated!
Hello Emily,
I don’t think you will be able to use NS_CONCAT in that fashion. The summary function must be set to “Minimum”. It may be possible — but my efforts come up empty. Also, with the new SuiteQL capacities, this probably can be solved. We will soon deliver a tool that allows you to draw sublists just like saved searches.
Marty
Hi everyone,
You can sort array results, but you have to use listagg instead of ns_concat.
Example: in an opportunity search that joins activities (one to many), using this formula will join all activity dates/titles into one cell, for each activity, and sort based on activity date. Output is a table.
case when listagg({activity.date}) is not null then ” || listagg(”||{activity.date} || ” || {activity.title} || ”) within group (order by {activity.date} asc) || ” end
Lester
Hello Lester,
That is awesome! I need to play with this analytical function. Here is a reference:
https://www.techonthenet.com/oracle/functions/listagg.php
Marty
Hey Marty,
Do you know with NS_Concat can you get it to not include the commas? We have a formula where we are consolidating the MAX({guid}) ||’,’|| SUM({amountremaining}) || ‘,’ || concat({number})
However that last column we need it concat without the additional commas. Process we are uploading to will error out with the additonal commas at the end
Hello Eric,
Have you tried wrapping the result set into something like this? REPLACE(MAX({guid}) ||’,’|| SUM({amountremaining}) || ‘,’ || concat({number}), ‘,’, ‘ ‘)
Marty
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 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???