SuiteScript 2.0 Joined Search Example

Seriously, I don’t know why this was so difficult, but I could not find a full working example of an ad hoc search written in SuiteScript 2.0 that included a join. Let me give a quick shout-out to Marty Zigman. Thanks for getting me 90% of the way there!

Here’s Marty’s example: http://blog.prolecto.com/2016/10/22/netsuite-suitescript-2-0-search-join-and-summary-example/

I’m a bit stupified by the .run().getRange(0,1000). I was trying to use .run().each() and could not get it to work. I’m not a fan of the for-loop in this case. Whatever!… This works!

Here’s the example. This script lists files in a folder that begin with text of your choosing.

Code Example

And here’s the code so that you can easily copy it.

/**
*@NApiVersion 2.x
*@NScriptType ScheduledScript
*/
define([‘N/search’, ‘N/log’],
    function (search, log) {
        function execute(context) {
            var mySearch = search.create({
                type: search.Type.FOLDER,
                columns: [
                    search.createColumn({
                        name: ‘internalid’,
                        join: ‘file’
                    }),
                    search.createColumn({
                        name: ‘name’,
                        join: ‘file’
                    }),
                ],
                filters: [
                    search.createFilter(
                        {
                            name: ‘name’,
                            operator: search.Operator.IS,
                            values: [‘Your folder name here’]
                        }
                    ),
                    search.createFilter(
                        {
                            name: ‘name’,
                            join: ‘file’,
                            operator: search.Operator.STARTSWITH,
                            values: [‘Your file name here’]
                        }
                    ),
                ]
            });
            var result = mySearch.run().getRange(0, 1000);
            var ids = new Array();
            var names = new Array();
            for (var i = 0; i < result.length; i++) {
                ids.push(result[i].getValue({ name: ‘internalid’, join: ‘file’ }));
                names.push(result[i].getValue({ name: ‘name’, join: ‘file’ }));
            }
            log.debug(‘ids’, ids.join(‘,’));
            log.debug(‘names’, names.join(‘,’));
            return true;
        }
        return {
            execute: execute
        };
    })

5 thoughts on “SuiteScript 2.0 Joined Search Example

    1. var myPages = mySearch.runPaged({ pageSize: 1000 });

      for (var i = 0; i < myPages.pageRanges.length; i++) {
      var myPage = myPages.fetch({ index: i });
      myPage.data.forEach(
      function (result) {
      var internalid = result.getValue('internalid');
      customers.push(internalid);
      }
      )
      }

      Like

  1. TM says:

    Thanks for posting this, this helped me understand how to create a join. Still trying to figure out how to use an AND with it. I get an error that filters has to be an array, even though it is.

    Regarding not getting run().each to work: Were your forgetting to return true? I do this all the time.

    Like

      1. TM says:

        You can AND/OR with all array based filters but it doesn’t seem to like them when using createFilter objects and ‘AND’ string or trying to mix createFilter objects and array based filter criteria with the ‘AND’ string. I think you have to use the createFilter object when using joins? At least, I haven’t figured out the array based version of them.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s