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 set up a conversation.

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

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

19 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.

  7. arin_12
    Posted August 18, 2017 at 2:37 am | Permalink

    When I use this code I get a error.

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

    TypeError: Cannot find function _marshal in object [object Object]

    I asked you this question here becos I was trying to use IN like query in the filter. I have multiple ids to be choose.

    Now the question is how do you find the “join: ‘role'” from “employee” record. I am referring to the current 2017 record browser and not able to find any join in name of role.
    TIA

  8. Posted August 26, 2017 at 11:30 am | Permalink

    I don’t get that error when I run your code. If you have multiple IDs, you may need to use a filter expression.

    Marty

  9. Viktor Schumann
    Posted January 12, 2018 at 6:01 am | Permalink

    Hi Marty,

    Do you have any recent information on SuiteQL and Multi Join capabilities?

    Kind Regards,
    Viktor

  10. Posted January 13, 2018 at 10:22 am | Permalink

    Hi Viktor, not yet. But I understand something new is coming for 2018.1…

  11. Umesh Shukla
    Posted September 27, 2018 at 3:19 am | Permalink

    Hi Marty,

    I have a SuiteScript 2.0 that load and search transaction saved search with posting period filter. In my filter I am using ‘anyof’ operator which is not working for ‘postingperiod’ field

    below is sample of my code:

    function getTransactionData(datain)
    {
    try
    {

    var objSearch = search.load(
    {
    id: datain.savedsearchid
    });

    objSearch.filters.push(search.createFilter({ name: “postingperiod”, operator: “ANYOF”, values: [“42”, “43”]}));
    //above filter filters only record with internalid 42
    result = readAllData(objSearch);
    return result;
    }
    catch (ex)
    {
    log.error(“getTransactionData”, ex);
    throw ex;
    }
    }

    Please note above issue is occurring only for saved search and even in saved search only for postingperiod field, if I search other object for example ‘account’ object with internalid filter using ‘anyof’ operator, works fine.

    can you please suggest me some solution for the same.

    Thank you very much.

  12. Posted September 30, 2018 at 6:43 am | Permalink

    Umeth,

    Did you try not quoting the values? Does it work with one value, not multiple array values? Finally, are you positive these are the internalid values you must retrieve?

    Marty

  13. Umesh Shukla
    Posted September 30, 2018 at 8:26 pm | Permalink

    Hi Marty,

    Thank you so much for looking into this.

    Yes, I have tried with quotes also.

    Yes it works for one value, but when I pass multiple value is in an array, anyof filters only for first value of array.

    Yes these are internalids. In my Netsuite account 42 is an internalid of ‘May 2003’ and 43 is an internalid of ‘June 2003’

    Thank you

  14. Umesh Shukla
    Posted September 30, 2018 at 8:35 pm | Permalink

    I have posted a query on Stack Overflow for the same:

    https://stackoverflow.com/questions/52260501/suitescript-2-0-filter-operator-anyof-not-working

    but till now no solution…

  15. Umesh Shukla
    Posted September 30, 2018 at 8:41 pm | Permalink

    Sorry for my above reply, I wanted to say:

    Yes, I have tried without quotes also.

  16. Posted October 1, 2018 at 9:55 am | Permalink
  17. Umesh Shukla
    Posted October 1, 2018 at 7:28 pm | Permalink

    Hi Marty,

    Thanks for article link. I looked into link but in this script ‘anyof’ not been used with ‘postingperiod’ field anywhere. As I mentioned in my query, this issue is only for ‘postingperiod’ field. If I use ‘anyof’ operator with other fields of transaction saved search (for example I create filter for account field using ‘anyof’ operator) it works fine.

    Thanks

  18. Posted October 4, 2018 at 5:37 am | Permalink

    Hello Umesh,

    You need to use the Join operator to get to multiple account periods. See this code example:

    var transactionSearchObj = search.create({
    type: "transaction",
    filters:
    [
    ["accountingperiod.internalid","anyof","10","100","119"]
    ],
    columns:
    [
    search.createColumn({name: "mainline", label: "*"}),
    search.createColumn({name: "recordtype", label: "Record Type"}),
    search.createColumn({name: "source", label: "Source"}),
    search.createColumn({name: "transactionnumber", label: "Transaction Number"}),
    search.createColumn({
    name: "trandate",
    sort: search.Sort.ASC,
    label: "Date"
    }),
    search.createColumn({name: "postingperiod", label: "Period"}),
    search.createColumn({name: "type", label: "Type"}),
    search.createColumn({name: "tranid", label: "Document Number"}),
    ]
    });
    var searchResultCount = transactionSearchObj.runPaged().count;
    log.debug("transactionSearchObj result count",searchResultCount);
    transactionSearchObj.run().each(function(result){
    // .run().each has a limit of 4,000 results
    return true;
    });

  19. Umesh Shukla
    Posted October 4, 2018 at 10:50 pm | Permalink

    Hi Marty,

    This works like a charm.

    Thank you so much…
    Thank you so much…
    Thank you so much…

    Regards:
    Umesh

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>