This article is relevant if encountering inconsistent results while using NetSuite SuiteScript 2.x to query a multi-select field.
Background
Alex F., a Technical Analyst in our Technology Services Practice, contacted me about his client delivery challenge with querying multi-select fields using SuiteScript. He provided examples and results of the issue. I replicated these in another NetSuite account to verify Alex’s observations. Indeed, Alex has identified an anomaly that would likely perplex any SuiteScript developer.
I believe there may be a bug in the environment. I am approaching this situation similarly to the issue I discussed a month ago in my article Custom NetSuite Mass Updates Not Appearing in the Expected Category List. Due to the slow and costly process of reporting errors through NetSuite Support and, ultimately, the unpredictable time for resolution, Alex took the initiative and developed an effective workaround. NetSuite Support deserves to see this challenge.
NetSuite SuiteScript 2.x Inconsistent Results from Multi-Select Field Lookups
As I analyzed Alex’s assertion, I set up the situation in another NetSuite account. I, too, encountered the issue when querying a multi-select field. There are two key challenges:
- Structure: The format of the results is inconsistent.
- Value Coherence: The order of the IDs and their corresponding values is incorrect.
Below, you’ll find examples demonstrating how the results can vary in structure. This variability makes reliable programming challenging, as the data may return in an unpredictably altered format. We might circumvent the issue with the order of the IDs by implementing an additional query.
The field in question is a multi-select field that links to another table.
require(['N/search'], function(search) {
var r = search.lookupFields({type:"customrecord_pri_application", id: 165, columns: ["custrecord_pri_application_dist_accts"]})
log.debug (JSON.stringify(r))
});
Continuing from the previous analysis, here are the results of the searches for two different IDs, illustrating the inconsistencies in the JSON output:
The first result set is relatively straightforward, but the combinations of values and texts are incorrect:
debug {"custrecord_pri_application_dist_accts":[{"value":"477","text":"El Ryan FZC"},{"value":"304","text":"HealthSource"},{"value":"497","text":"Taboola"},{"value":"440","text":"The Highland Mint"}]} 2024-04-18 20:53:33.895
The second result set shows a different format and issues with alignment between values and texts:
debug {"custrecord_pri_application_dist_accts":{"value":"282,304,316,284,445,477,502,411,105,493,481,365,101","text":"Blackstone Industries LLC (Prospect),Caffe Luxxe,Circon Environmental,El Ryan FZC,Lim Brothers Import & Export Co., Ltd.,Prolecto Resources,Raney's,Rescue Agency,Ring 2.0 Production,Sportsmith, LLC,Taboola,The Rare Wine Co.,Wolf Gordon"}} 2024-04-18 20:27:34.69
These variations in structure and incorrect mappings between values and texts complicate the development of reliable scripts. The discrepancies in data presentation suggest that handling this issue might require additional queries or a workaround to normalize the data structure and align the values properly.
SuiteSQL Workaround for Retrieving NetSuite Multi-Select Fields
In 2021, I shared an article that detailed how one of our engineers guided the community on utilizing SuiteSQL to query NetSuite’s multi-select fields. While that was informative, Alex has introduced an intriguing workaround approach highlighting the misalignment of values and IDs.
Alex credits Chris Saxon’s article from September 8, 2021, titled “How to split comma-separated value strings into rows in Oracle Database“, which provides foundational knowledge for his method.
Alex demonstrates how to transform the same lookup into a naturally ordered table using NetSuite SuiteSQL in this workaround. The trick here is to get the SQL multi-select results from a comma-separated result set into conventional row output that can then be used to act as a where clause in the parent lookup query. The technique involves aliasing the result set for the multi-select field as ‘str’. Below, you can review the SQL pattern Alex uses to achieve more coherent results:
SELECT id , BUILTIN.DF(custrecord_pri_application_accts_client) FROM customrecord_pri_application_accts WHERE id IN ( WITH rws AS (SELECT custrecord_pri_application_dist_accts str FROM customrecord_pri_application WHERE id = 165) SELECT REGEXP_SUBSTR( str ,'[^,]+' ,1 ,level ) value FROM rws CONNECT BY LEVEL <= LENGTH ( str ) - LENGTH ( REPLACE ( str, ',' ) ) + 1 ) ORDER BY id
This approach ensures that the data from multi-select fields is returned in a structured and predictable format, making it more useful for consistent programming. Click the image to see the output using our license free NetSuite SQL tool.
Reporting SuiteScript Issues to NetSuite Support
Reporting anomalies to NetSuite Support is the logical step. However, this reporting process comes with its challenges. It might require multiple meetings to fully explain the issue, and there’s no guarantee of a resolution; it could simply result in an enhancement request that may never be addressed. However, I am more confident that this anomaly will be resolved. Considering the time and effort involved and my commitments to clients and staff, the cost-benefit ratio of pursuing this issue through NetSuite Support is less than favorable.
As I managed to produce some success in a previous article Custom NetSuite Mass Updates Not Appearing in the Expected Category List, to mitigate these perceived costs, I plan to create a NetSuite Support Request and use this article as yet another learning opportunity. This approach may help streamline the process and provide educational value to others facing similar challenges.
Addendum: Results from NetSuite Support
After reporting the issue to NetSuite, they provided a response shown in the next paragraph. It is reasonable because there is no easy fix without breaking existing applications in the environment.
Upon further checking, we have found a defect that was already answered and addressed by our engineers. What their response are is that the behavior you are encountering is assessed as fact of life. Please see below details as per our engineers:
This behavior is documented in SuiteScript 1.x docs on nlapiSearchRecord() (“Also note that in search/lookup operations, long text fields are truncated at 4,000 characters”) and on SuiteScript 2.x docs on search.lookupFields(options) (“Important: In search/lookup operations, custom multiselect fields of type “long text” are truncated at 4,000 characters.”).
Kindly see the considerations below as well:
- There is a simple workaround — retrieve the data with record.load() for the affected records.
- N/search is an obsolete functionality — we provide N/query since 2018.1 release.
- The reported behavior is not a simply and quick fix but will probably be resolved within another partially related global updated plan, there is no estimates on that however.
Engage with Trustworthy NetSuite Experts
Our firm is not only a trusted advisor and expert on NetSuite; we are active platform users—just like you. This direct engagement in managing our business operations via NetSuite enhances our expertise and ensures we provide and derive significant value from the platform.
If you find the insights shared here valuable, consider subscribing to receive updates on new posts. If you are searching for highly proficient NetSuite leadership, 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
