NetSuite SuiteScript 2.0 Search Join and Summary Example

This article is relevant if you need to understand the NetSuite SuiteScript 2.0 syntax for producing a search with either a join or summary operator.

Background

NetSuite is making investments to grow its SuiteScript 2.0 API.  Thus, to keep up, we old-timers need all the help we can get to learn the new syntax.  The NetSuite Help document is still under development and thus code examples are forthcoming.  At the time of this writing, I had a hard time finding some code examples for searching using the Join and Summary syntax.  So I thought I would offer up an example.

SuiteScript 2.0 Search Example with Join and Summary Operators

//search on employees based on specific roles by joining to role table
var filters = [
   search.createFilter({
        name: 'isinactive',
        operator: search.Operator.IS,
        values: ['F']
   }),
   search.createFilter({
        name: 'internalid',
        join: 'role',
        operator: search.Operator.ANYOF,
        values: JSON.parse(JSON.stringify(roleList))
   })
];

//get the values of the roles in the role table via SuiteScript 2.0
var s = search.create({
  'type':'employee',
  'filters':filters,
  'columns':[
            search.createColumn({'name':'firstname'}),
            search.createColumn({'name':'lastname'}),
            search.createColumn({'name':'internalid', join: 'role'}),
            search.createColumn({'name':'name', join: 'role'})
            // if we wanted to run a summary search, here is the syntax
            //,search.createColumn({'name':'created','summary':search.Summary.MAX})
            ]
}).run();
s = s.getRange(0,1000);

Join A Team of NetSuite Innovators

If you have achieved your NetSuite Certification and wish to be part of the leading team producing NetSuite innovations for the community, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's 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 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , | Category: NetSuite, Technical | 6 Comments

6 Comments

  1. Aaron
    Posted October 24, 2016 at 8:25 am | Permalink

    Doesn’t SuiteScript 2.0 use boolean values for its filters, rather than ‘T’ and ‘F’?

  2. Posted October 25, 2016 at 3:38 pm | Permalink

    Hello Aaron,

    Yes, you are correct, you can use JavaScript true or false. However, we have found it is inconsistent likely because NetSuite is working to stabilize the new 2.0 API. Apparently the syntax offered, which mimics 1.0, is also available.

    Marty

  3. Posted March 16, 2017 at 9:51 am | Permalink

    The query rendered seems to implicitly execute an INNER JOIN. Is there a way to do a LEFT JOIN as well? For example, if I wanted to get a list of customers along with the name of their parent customer, I would join through the parentcustomer relationship. However, an inner join would omit customers that do not have a parent, so a left join would be preferable.

  4. Posted March 18, 2017 at 5:58 am | Permalink

    Hi Dan,

    Yes, this example is an inner join. The trick to getting a left join is to produce criteria with ORs so that you reach out to the joined customers even if they don’t have a parent. Then, you may need to use formulas in your result set that remove information that does not meet criteria.

    Marty

  5. Arin_12
    Posted July 6, 2017 at 2:48 am | Permalink

    JSON.parse(JSON.stringify(roleList)) — Does this works ? For me it gives error.
    Assume that your rolelist as
    roleList = {“internalid”:”1234″}.

    org.mozilla.javascript.EcmaError: TypeError: Cannot find function _marshal in object [object Object]

  6. Posted July 12, 2017 at 5:09 pm | Permalink

    This works for me in a browser.

    var roleList = {internalid:1234}
    var x = JSON.parse(JSON.stringify(roleList))

    But I am confused by your question relative to this article.

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>