Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

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

3 min read

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.

Tip: If your HTML in a saved search formula is being displayed as plain text, be sure to confirm that option Setup >> Company >> General Preference >> Disable HTML in Formula (Text) is unchecked.

Note that even with the above preference unchecked, you might still be able 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.

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.


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 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, check out how you can become an author/collaborator here.

Also, consider subscribing to our no-nonsense email list to get these insights delivered to your inbox as soon as they’re published. Sometimes, ignorance is a choice. Choose wisely!

Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

2 Replies to “Tip: Learn How To Hyperlink Multi-Select Fields in NetSuite Saved Search Results”

  1. Comment from a concerned user (Michael B.):

    I read your article titled “Tip: Learn How To Hyperlink Multi-Select Fields in NetSuite Saved Search Results” published last Friday. In it, you said,

    “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 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 above preference unchecked, you will no longer be able to execute JavaScript in saved search formulas.”

    What version of NetSuite does this apply to? I have numerous formulas in saved searches that use JS like the example below and I wonder if they will break with 2022.2. I have not noticed any release notes that mention this disabling of JS in formulas in saved searches.

    EXAMPLE:


    '<a target="_blank" href="#" onclick=
    ''try{
          mymotid='||{internalid}||';
          status="'||{status}||'";
          if(status=="Completed"){
                mymot=nlapiLoadRecord("manufacturingoperationtask",mymotid);
                lastmotid=mymot.getFieldValue("lasttask");
                if(mymotid!==lastmotid){
                      lastmot=nlapiLoadRecord("manufacturingoperationtask",lastmotid);
                      lastmotstatus=lastmot.getFieldValue("status");
                      ...
                }else{
                      wofilt=[];
                      wofilt[0]=new nlobjSearchFilter("tranid",null,"is","'||{workorder.number}||'");
                      wofilt[1]=new nlobjSearchFilter("formulatext",null,"isnot",0);
                      wofilt[1].formula="{quantity}-{quantitypicked}";
                      pndesccol=new nlobjSearchColumn("displayname","item");
                      srs=nlapiSearchRecord("transaction","customsearchwarehousecomponenttracker",wofilt,pndesccol);
                      if(srs==null){
                            wo=nlapiLoadRecord("workorder",'||{workorder.internalid}||');
                            wo.setFieldValue("orderstatus","G");
                            woid=nlapiSubmitRecord(wo);
                            this.textContent="Built";
                            this.removeAttribute("onclick");
                            this.href="/app/accounting/transactions/workord.nl?id="+woid;
                      }else{
                            sr=srs[0];
                            componentpn=sr.getText("item");
                            componentpndesc=sr.getValue("displayname","item");
                            alert("Unable to mark WO as Built. Notify supervisor. The following component is not issued:\n"+componentpn+" "+componentpndesc);
                            this.textContent="ERROR";
                            this.removeAttribute("onclick");
                      }
                }
          }else{
                alert("The WO is not finished. Cannot mark as Built.");
                this.textContent="ERROR";
                this.removeAttribute("onclick");
          }
    }catch(e){
          alert("ERROR: "+e.getCode()+": "+e.getDetails());
          this.textContent="ERROR";
    }
    return false;''
    >Mark WO Built</a>'

    1. Hi Michael,

      Thanks for reaching out. You’d have to test in Release Preview if your code still works. I tried executing a `` block and that no longer works. I’ve updated the section of the article that you referenced to clarify that.

      The changes in NetSuite are not tied specifically to any particular release; over the past few releases, NetSuite has tightened this area. For example, in 2022.1 they silently removed support for script tags in HTML in input dialogs which broke our NetSuite Input Dialog module. That was not mentioned in the release notes. So, while your code might still work, it might just be a matter of time. I suggest you have a contingency plan if you depend on this behavior for key business processes.

      Cheers.

Leave a Reply

Your email address will not be published. Required fields are marked *

You deserve to know as soon as we share a new NetSuite Insight.
We won't disturb you with anything else or share/sell your data.

 
Wait a minute!Would you like to know more about NetSuite Certification?

Ever thought of becoming NetSuite certified? Not sure which certification(s) to obtain or how best to prepare? Is certification really worth it? Or maybe you are already certified and would like to share your experience... We've got your covered with our exclusive series on NetSuite Certification!

Take our independent NetSuite certification survey to unlock this series and gain access to insights from other NetSuite professionals.

#netsuiteinsights #becauseyoudeservetoknow