How To Hyperlink Multi-Select Fields in NetSuite Saved Search Results

As an independent Senior Consultant with the talented Prolecto team, I get to work on challenging and interesting projects. Recently, I have been working on extending our landed costs solution that helps clients get control of their true costs.

One of those clients made a simple request - to hyperlink columns in a saved search that I had built for them for some landed costs analysis. Pretty basic, right? We do that kind of stuff everyday. However, there was a problem...

Challenge

One of the fields I needed to hyperlink was a multi-select field that contained a list of transactions. When multi-select fields are added to saved search results, they show up as a comma-separated list. My goal was to convert those to individual hyperlinks.

I considered using the NS_CONCAT trick but that only works for summary searches so it was not an option (or, at least, I wanted something that would work for non-summary searches).

Note on HTML and Script Support in Saved Search Formulas

Before delving into the solution, please bear in mind that NetSuite, out of security considerations, has made some changes to HTML support in saved search formulas. Previously, you could execute pretty much any HTML, including JavaScript, in HTML formulas. With recent changes, NetSuite has pretty much banned JavaScript in this context and introduced a global preference that controls the use of (pure) HTML in saved searches altogether.

Note that even with the Formula (HTML) field, you might still be unable to execute JavaScript in saved search formulas. For example, I tried an approach similar to the one here using a <script></script> block but got the following error: ERROR: Results cannot display script output for security reasons. This is consistent with behavior, I've seen elsewhere where scripts in HTML have been disallowed e.g. causing my NetSuite Input Dialog to break after the 2022.1 release.

Solution

I used the REGEXP_REPLACE function to accomplish my objective. Here's the result first, followed by an explanation of how it works.

Note: When transactions of different types are combined, the order of the generated URLs might be incorrect! This is due to a NetSuite nuance that causes the transaction references to be sorted differently than the IDs when accessed via searches e.g. "Bill #xxx" which has internal reference "VendBill-123".

Let's look at the specifications in the Results subtab which produced the above output:

1. This is a single-value hyperlinked field that serves as the basis for our solution. There is nothing special here. Fun fact: Marty Zigman's shared an article back in 2012 (!) explaining how to do this.
2. This is the "raw" output added for reference. NetSuite will produce a comma-separated list for multi-select fields.
3. Using the REGEXP_REPLACE function, I replace every comma with a line break (<br/>) to get each PO # on a different line. Again, nothing spectacular.
4. This is where the "magic" happens. I use the same formula, in combination with back referencing to generate hyperlinks for each PO. Let's dig a bit deeper:
   • {createdfrom.custbody_pri_lc_product_purchaseorder.id} gives us the internal ID of each PO. Note that, although NetSuite does not allow references of more than 2 levels deep, the ID is a special field that can be referenced at the third level. Again, if you've been following Marty's blog, you know that from this 2020 article. So, the output of this formula will be something like 1111,2222 instead of the PO #s in the previous column.
   • '([^,]+)[,]?' This regular expression matches any non-comma characters. The parentheses are important as they tell the regular expression (a.k.a. regex or regexp) engine to group those results. If you're new to regular expressions, I might have lost you already. I'm not a regex expert either, but Google and visual tools are my best friends. www.regexper.com is my go-to tool to visualize a regexp; check out the visual representation of the above regex here. Similarly named but different www.regexr.com is my favorite for testing. Returning to my earlier example, this regex will match two groups (1111) and (2222); the comma will be part of the first match but will not be included in the group since it is outside the parentheses.
   • '<a href="/app/accounting/transactions/transaction.nl?id=\1">'||'View'||'</a><br/>' is similar to what we used previously to generate the URL with one significant difference. Notice the \1 in the expression. This is called a backreference in regex lingo. It basically tells the system to insert the first group there and that would be the internal ID of the PO. Now, you might be wondering how this works since \1 refers to the first group but we want to create a correct link for each transaction, not just the first one. The answer lies in the fourth parameter of REGEXP_REPLACE. By passing '0', we tell it to replace all occurrences of the pattern. It turns out that this is smart enough to apply \1 to each group, thus producing the desired outcome!

You can use www.regexper.com to visualize your regular expressions and www.regexr.com to test them. Both are free tools!

Notice that I ended up with two columns: One for the transaction numbers and another with the hyperlinks. However, by clearing out the header of the latter, it looks like it is one extended column. Ideally, I would have loved to have a single column where the transaction numbers are hyperlinked. Perhaps, there's a smart trick to achieve that. But for now, I am satisfied with the two-column approach.

Here are the formula templates for easier access:

// Transaction Reference Column
REGEXP_REPLACE({field_id}, ',', '<br/>', 1, 0)

// URL Column (note the .id at the end of the field id!
REGEXPR_REPLACE({field_id.id}, '([^,]+)[,]?', '<a href="/app/accounting/transactions/transaction.nl?id=\1">View</a><br/>', 1, 0)

Conclusion

I hope that you've found this tip helpful. Remember that, at the beginning of this article, I mentioned that this solution was the result of a question from a client regarding Prolecto's landed costs solution? Well, Marty has written several articles about this offering on his blog. Check them out and get in touch if you're facing any landed costs challenge or need help the best NetSuite systems' integrator in the space.

NetSuite Insights is proud to partner with Prolecto Resources Inc. - the unrivaled #1 NetSuite Systems Integrator and thought leader in the space! Learn more about how Prolecto can supercharge your NetSuite experience and deliver the best return on your NetSuite investment.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, learn how you can become an author/collaborator here.

Don't miss a beat - subscribe to our no-nonsense email list to have these game-changing insights hit your inbox as soon as they're published. Ignorance? Nah, you've got a smarter option. Choose wisdom, choose insights!