How to add filter or condition in Netsuite Workbook?

Hi,

In our previous posts, we discussed in detail about Workbook, Dataset and about using workbook in script.

You can read both using links below:

SuiteAnalytics - Dataset and Workbook

Using Workbook in Script

You can download our Android app and read on the go.

Now, we are going to learn about adding filter to workbook in script. Filters for workbook are almost similar to saved searches. 

In Suite Script 2.0, we always access filters first and then add our filters, we will follow same procedure here also.

Use "createCondition" method to create filter and then add this filter into workbook before calling run method.

Step 1: First create filter , in workbook Netsuite call it condition,

var myCondition =  myLoadedQuery.createCondition({
                        fieldId: 'subsidiary',
                        //formula: '{customer.currency}',
                        operator: "ANY_OF",
                        values: [1]
                    });

Step 2: Add this condition/filter to existing conditions

myLoadedQuery.condition = myCondition;

Step 3: Run Query

var resultSet = myLoadedQuery.run();

So working code should be like this:

/**
 *@NApiVersion 2.x
 *@NScriptType ScheduledScript
 */
define(['N/search', 'N/record', 'N/email', 'N/runtime', 'N/render', 'N/query', 'N/format'],
    function(search, record, email, runtime, render, query, format) {
        function execute(context) {
            try{

                var myLoadedQuery = query.load({
                        id: 'custworkbook10'
                    });

                var myCondition = myLoadedQuery.createCondition({
                        fieldId: 'subsidiary',
                        operator: "ANY_OF",
                        values: [1]
                       
                    });
                    myLoadedQuery.condition = myCondition;
                    var resultSet = myLoadedQuery.run();

                var resultSet = myLoadedQuery.run(); 

               var results = resultSet.results;
                var theColumns = resultSet.columns; 

                for (var i = 0; i < results.length; i++)
                    {
                        var mValues = results[i].values;
                        var column1 = mValues[0];

                }

            }
            catch(ex)
            {
                log.error('error', ex);
            }               
        }
        return {
            execute: execute
        };
    });

 We include way to add formula in example, you can use join or formula as mentioned in Step 1.

As we are going to finish this post, we have one task for you. Try to add column which is not in workbook and use sorting on that column. Share piece of code with us in comment section or use any of our social media channel.

Hope this will help you, happy coding.

Thanks

Netsuite Guru

Download Netsuite Guru Android App
Follow Us on Linkedin
Our FB Page