Performing a Sum or Grouped Query in SuiteScript 2.0

Question

Can any give a solid example of how to perform a grouped or summed query, ie "How many sales orders has Jane Smith done this month" in suitescript 2.0. The NetSuite Help Center examples and docs for 2.0 are so scattered and half-baked imho.

Any help would be appreciated. Below is the code im using. which returns an UNKNOWN ERROR. If you change search.Summary.GROUP and search.Summary.COUNT to lowercase (search.Summary.group, etc), then you get one result where the orderCount column actually contains the internal id, not an aggregate count

var results = [],
        GROUP = search.Summary.GROUP,
        COUNT = search.Summary.COUNT;

var mySalesOrderSearch = search.create({
    type: 'salesorder',
    columns: [
        "trandate",
        {
            name: 'salesrep',
            summary: GROUP
        }, {
            name: 'internalid',
            summary: COUNT
        }],
    filters: [{
        name: 'mainline',
        operator: 'is',
        values: ['T']
    }, {
        name: "trandate",
        operator: "within",
        values: ["thisyear"]
    }]
});

mySalesOrderSearch.run().each(function (result) {
    var repName = result.getText({
        "name": "salesrep",
        "summary": GROUP
    });

    var orderCount = parseInt(result.getValue({
        "name": "internalid",
        "summary": COUNT
    }), 10);

    var msg = {
        "title": "Order Count by Sales Rep",
        "details": repName + " has sold " + orderCount + " orders."
    };

    results.push(msg);
    log.debug(msg);
});

return results;

And here is the result when I change the summary to lowercase (search.Summary.group, etc)

{
    "error": false,
    "msg": "TESTING_POST_METHOD",
    "data": [
        {
            "title": "Order Count by Sales Rep",
            "details": "Victor Beisel has sold 524963 orders."
        }
    ]
}

The 524963 number in the details key is the internal id of a sales order, not the true count. And here is the result when you use the capitalized summary token (search.Summary.GROUP, etc) as stated by the NetSuite docs

{
    "error": true,
    "msg": {
        "type": "error.SuiteScriptError",
        "name": "UNEXPECTED_ERROR",
        "message": null,
        "stack": [
            "each(N/searchObject)",
            "TESTING_POST_METHOD(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:159)",
            "<anonymous>(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:40)",
            "post(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:117)"
        ],
        "cause": {
            "type": "internal error",
            "code": "UNEXPECTED_ERROR",
            "details": null,
            "userEvent": null,
            "stackTrace": [
                "each(N/searchObject)",
                "TESTING_POST_METHOD(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:159)",
                "<anonymous>(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:40)",
                "post(/SuiteScripts/XpelAffiliateOrders/XPELSS2_3PO_Index.js:117)"
            ],
            "notifyOff": false
        },
        "id": "",
        "notifyOff": false
    },
    "data": null
}

erictgrubaugh · Accepted Answer · 2016-10-27T14:34:01.010

8

// Assuming N/search is imported as `s`
var mySalesOrderSearch = s.create({
    type: 'salesorder'
    // Use the summary property of a Column to perform grouping/summarizing
    columns: [{
        name: 'salesrep',
        summary: s.Summary.GROUP
    },{
        name: 'internalid',
        summary: s.Summary.COUNT
    }],
    filters: [{
        name: 'mainline',
        operator: 'is',
        values: ['T']
    }]
});

mySalesOrderSearch.run().each(function (result) {
    var repId = result.getValue({
        "name": "salesrep",
        "summary": s.Summary.GROUP
    });
    var repName = result.getText({
        "name": "salesrep",
        "summary": s.Summary.GROUP
    });
    var orderCount = parseInt(result.getValue({
            "name": "internalid",
            "summary": s.Summary.COUNT
    }), 10);

    log.debug({
        "title": "Order Count by Sales Rep",
        "details": repName + " has sold " + orderCount + " orders."
    });
    return true;
});

Grouping specifically by month is a little more difficult as you'll need to use a formula with TO_CHAR on the appropriate date field to get the correct month.

edited Oct 27 '16 at 14:34

answered Oct 23 '16 at 20:55

erictgrubaugh

8,519
1
20
28

The syntax is correct but it is returning 1 log only. and the orderCount variable is holding the actual internalid, not a count. The initial reason I asked this question is because we feel this may be a another bug in 2.0, for we have tried various syntax and we either get an error, or 1 result where the "count" is not correct and returns the field value instead – Coldstar Oct 24 '16 at 14:12
Also, search.Summary.GROUP and search.Summary.COUNT throw the errors mentioned above. Changing GROUP and COUNT to lowercase return the 1 result only, but no error – Coldstar Oct 24 '16 at 14:15
The code im using based off the example you gave is posted above – Coldstar Oct 25 '16 at 22:09
2

AFAIK when you perform a summarized search in SuiteScript, *all* columns must be grouped or summarized in some manner. Try either removing your `trandate` column, or adding a grouping/summary on it as well. – erictgrubaugh Oct 25 '16 at 23:27
You were right by making sure all columns are of the correct summary. Also we were missing the 'return true;' on the last line of the mySalesOrderSearch.run().each body. – Coldstar Oct 26 '16 at 15:31
Ah yes, I did miss that in my example. Glad you got it worked out! – erictgrubaugh Oct 26 '16 at 16:36

Performing a Sum or Grouped Query in SuiteScript 2.0

1 Answers1