NetSuite: Sorting NS_Concat Saved Search Results

A customer asked for help on their opportunity search. The search should not contain duplicate rows of opportunities. But the search should also contain joint activity fields. The challenge comes with the fact that opportunities can have multiple activities attached to them. Therefore in this one-to-many relationship, there would naturally be duplicate rows of opportunities.

That is unless of course we know how to use ns_concat. The below formula is an example of how to achieve the customer's goal:

case when ns_concat({activity.title}) is not null then '<table border =1>'||REPLACE(ns_concat(distinct '<tr><td>'||{activity.title}||'</td><td>' ||{activity.date}||'</td></tr>'),',','')||'</table>' end

To briefly explain, ns_concat converts an array into a string. So essentially, whatever is inside the ns_concat tags will be looped, until each of the elements in the array have been expressed. Referencing two variables in the same ns_concat will allows us to ensure that the variables correspond to the same activity record.

The function also apparently evaluates HTML tags. Therefore, if you want to loop table rows and table divisions, that is also possible given the above code. Just make sure the main table tag is outside the ns_concat, since that tag shouldn't be looped. The case when function prevents tables from generating if the rows do not have any content (or no activities are related to the opportunity). The case when condition requires the use of ns_concat as well, since the intended output already uses ns_concat. 

Moving on.

While the formula worked pretty accurately, the table rows will likely be unsorted. Such is the dilemma explained in the comments section of Marty Zigman's article. So how can we fix this.

This is where listagg comes in. I've been using this function a number of times in the past month and found it A LOT better than ns_concat. If you know how to use listagg, I argue that you won't ever use ns_concat again.

Below is the formula that returns pretty much the same output as above, only it has the table rows sorted.

case when listagg({activity.date}) is not null then '<table border = 1>' || listagg(distinct '<tr><td>'||{activity.date} || '</td><td>' || {activity.title} || '</td>') within group (order by {activity.date} asc) || '</table>' end

We can replace the ns_concat tag with listagg. then after the trailing parenthesis, we can append "within group (order by <variable> asc)". The variable would represent an activity field, so we can sort the table rows based on the results from that field, either in ascending or descending order.