This article is relevant if you would like to work with NetSuite’s NS_CONCAT aggregate function and turn the results into hyperlinks and URLs.

Background

Back in 2014, I wrote an article, Reveal Both Aggregate and Detail in NetSuite Saved Search, which helped to describe a NetSuite function that allows you to get at detail information in an aggregated / summary search.  Given NetSuite’s hierarchical database nature, the opportunity for information that is summarized at one level but the need to reveal elements of the underlying summary (the detail) is highly probable.

While I thought the NS_CONCAT function was novel, it wasn’t that useful in that is just revealed a comma-separated list of values.  Since writing that article 6 years ago, I always had it in the back of my mind that I could make it better.

Transforming NetSuite NS_CONCAT for Hyperlinked Information

In my mind, if I was going to use an aggregate search that can reveal elements of the detail, I thought that a hyperlink to that detail was the best use case.    To solve this, I set up a simple scenario.

1. Vendor Bills: many of the consultants on our team are outside contractors and thus we pay them via vendor bills.
2. Payments: we pay our team every two weeks.

In this simple scenario, we will aggregate the vendor bills and group by payments.  We want to reveal a hyperlink to each of the vendor bills yet summarize at the payment level.

Two elements of information are needed in the hyperlink:

1. Internal ID: we need the internal ID of the vendor bill (transaction).
2. Descriptions: we need the hyperlink description; in this case, we will reveal the vendor bill document number.

Build a Custom Delimited NS_CONCAT Data Set

Thus, the first step was to come up with a way to get the needed information in the NS_CONCAT function and build a delimited set of data that can be later parsed apart.   Here is the function that I came up with:

NS_CONCAT('(' || {appliedtotransaction.number} || '{*}' || {appliedtotransaction.internalid} || ')')

In this scenario, I am using ‘{*}’ to represent a delimiter between the two categories of data: the vendor bill number and the internal ID.  I also decided to play safe and delimit the detail data results with parenthesis so that I could more easily isolate out the comma that NetSuite will produce automatically.  Click to see the related image.

Build a Regular Expression to Parse the Custom Delimited NS_CONCAT Data Set

Next, using regular expressions, we can build a parser.  I originally tried to work with NetSuite’s underlying Oracle engine to use PL/SQL based REGEX.  However, it appears that it would not support the replacement elements I needed with REGEX groups for the work I needed to transform the results into hyperlinks.  Thus, I decided I would stay in the JavaScript world and parse in the browser the output.

This is the regular expression that would break the output into two parts using groups: the document name and the internal ID.

/\(([^,]+)\{\*\}(\d+)\),*/g

Generate the Output Into a Script

Once I had the regular expression, I was then able to use a simple JavaScript function to replace the parsed information into a hyperlink.  This then became the formula in my aggregate search.  Click the related image to see more.

'<script>
var r = /\(([^,]+)\{\*\}(\d+)\),*/g; 
var s = ''' ||  
NS_CONCAT('(' || {appliedtotransaction.number} || '{*}' || {appliedtotransaction.internalid} || ')') || ''';
var h = ''<a class="dottedlink" target=_blank href="/app/accounting/transactions/transaction.nl?id=$2">$1</a><br />'';
var o = s.replace(r, h);
document.write(o);
</script>'

Demand More from your NetSuite Investment

While I suspect there are other approaches to producing similar results using NS_CONCAT, I can now let that gnawing “what if” puzzle rest knowing that it is indeed solved.    This is yet another example of why I love the NetSuite platform.    We can all leverage the built-in capacities which give us so much to start with when solving business problems — and then we can go the extra length to produce meaningful enhancements without the old school need to run a complex and expensive information technology project.

If you found this article meaningful, feel free to subscribe to be notified of new articles as I post them.  If you would like to work with expert individuals in NetSuite, accounting, and technology, let’s have a conversation.

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