SuiteScript 2.0: Error thrown when adding GROUP column to existing saved search

Question

Any assistance is appreciated.

I have a saved search function which returns a saved search object:

        const getResultsFromSearch = (intSearchId, intItemId, withSubFilter) => {
        var objSearchObject = nSearch.load({
            id: intSearchId,
            type: nSearch.Type.TRANSACTION
        });

        // Append dynamic filter for Item ID
        objSearchObject.filters.push(nSearch.createFilter({
            name: "anylineitem",
            operator: nSearch.Operator.IS,
            values: [intItemId]
        }));


        if (withSubFilter == null) {
            //Group results by subsidiary
            objSearchObject.columns.push(nSearch.createColumn({
                name: "subsidiary",
                summary: nSearch.Summary.GROUP,
                sort: nSearch.Sort.DESC,
            }));
        }
        else {
            // Append dynamic filter for subsidiary
            objSearchObject.filters.push(nSearch.createFilter({
                name: "subsidiary",
                operator: nSearch.Operator.IS,
                values: [withSubFilter]
            }));
        }
        return objSearchObject;
    }

I am fairly confident that the above works fine, because when I fire runPage().count, it returns the number of results. The logic is: If no filter parameter is added (i.e. 'withSubFilter' is NULL), add a new grouped column, ELSE add a new filter.

The error is thrown immediately when I attempt to loop the results as followed:

            var objSearchResults = getResultsFromSearch(intSearchId, intItemId, null);

            objSearchResults.run().each((result) => {
            idx++
            var getSubsidiary = result.getValue({name: 'subsidiary', summary: nSearch.Summary.GROUP});
            log.debug({title: "Get subsidiary " + idx, details: getSubsidiary});
            return true;
        })

The above works when no GROUPED column is used. The moment I use a grouped column, the unexpected_error is thrown.

Any ideas?

Answer 1

I think that should work but I have often had issues with altering saved searches.

What I tend to do is load the saved search and then use its filters and columns as templates:

var tpl = nSearch.load...
var srch = nSearch.create({
    type:'transaction',
    filters: addMyFilters(itemIntId, tpl.filters),
    columns: adjustColumns(tpl.columns)
}).run().each...

where addMyFilters might look like:

function addMyFilters(intItemId, tplFilters){
    return [nSearch.createFilter({
        name: "anylineitem",
        operator: nSearch.Operator.IS,
        values: [intItemId]
    })].concat(tplFilters); // return a new array
}

and similarly with the columns. This has never given me issues.

Answer 2

Typically I like to work with the labels. That way it doesn't matter if it's grouped or not.

Also, I HIGHLY recommend the Chrome/Edge plugin called "NetSuite Saved Search Code Export" which allows you to build your search in the UI, then export it as SS1.0 or SS2.x code.

Once you have the search object built, I use the following code to turn a search result into a nicely labelled Javascript object, and it's not limited to 1000 results either.

Note, I pass into this function, the record type, filters, and columns from my usual code.

I also have another external function safeName() which removes all non alphanumeric values:

Also note, it is in v2.x formatting, so it can be used as a library module with SS2.x or SS2.1 code.

function keepAfterLast (str, char) {
    if (!str) return '';
    else if (!char) return str;
    else return str.split(char).pop();
}

function safeName(str, replaceWith) {
    return str.replace(/[^0-9a-zA-Z]/g, !replaceWith ? '' : replaceWith);
}


function returnSearchResults(searchRequest) {
    var returnResults = [];

    var searchObject = search.create({
        type: searchRequest.type,
        filters: searchRequest.filters,
        columns: searchRequest.columns,
    });
    
    var results = searchObject.run(), searchResults = [], searchid = 0;
    do {
        var resultslice = results.getRange({start: searchid, end: searchid + 1000});
    
        resultslice.forEach(function (slice, idx) {
            var columns = slice.columns;
            var r = {};
            columns.forEach(function (col, colNum) {
                var colLabel = '';
                if (col.label) colLabel = safeName(col.label.toLowerCase());
                else {
                    if (col.join) col.label += safeName(col.join.toLowerCase()) + '_';
                    colLabel += safeName(col.name.toLowerCase());
                }
    
                var colLabelNoH = '_' + colLabel;
                r[colLabel] = {};
                // r[colLabelNoH] = null;
                var v = slice.getValue(col);
                var t = slice.getText(col);
                if (v && t) {
                    r[colLabel].value = v;
                    r[colLabel].text = t;
                    try {
                        if (v.indexOf(' : ') > -1) {
                            if (!r[colLabelNoH]) r[colLabelNoH] = {};
                            r[colLabelNoH].value = keepAfterLast(v, ' : ');
                        }
                    } catch (e1) {
                    }
                    try {
                        if (t.indexOf(' : ') > -1) {
                            if (!r[colLabelNoH]) r[colLabelNoH] = {};
                            r[colLabelNoH].text = keepAfterLast(t, ' : ');
                        }
                    } catch (e1) {
                    }
                } else if (v && !t) {
                    r[colLabel] = v;
                    try {
                        if (v.indexOf(' : ') > -1) {
                            if (!r[colLabelNoH]) r[colLabelNoH] = {};
                            r[colLabelNoH] = keepAfterLast(v, ' : ');
                        }
                    } catch (e1) {
                    }
                } else if (!v && t) {
                    r[colLabel] = t;
                    try {
                        if (t.indexOf(' : ') > -1) {
                            if (!r[colLabelNoH]) r[colLabelNoH] = {};
                            r[colLabelNoH] = keepAfterLast(t, ' : ');
                        }
                    } catch (e1) {
                    }
                } else {
                    r[colLabel] = v;
                }
            })
            returnResults.push(r);
            searchid++;
        });
    } while (resultslice.length >= 1000);

    return returnResults;
}

**Some use cases **

Firstly the chrome/edge plugin generates your search object:

The popup gives me SS2.0 code whether it's grouped/summary or not

For a grouped example, here's my search:

The popup will generate some code using expressions for filters.

From that I take what i need to build the search:

            let lifeIsGood = true;
            
            let filters = [
                ["isinactive","is","F"]
            ]
            
            // if certain conditions are met, add filters
            if (lifeIsGood) {
                filters.push("AND");
                filters.push(["internalid",'anyof',[1,2,3,4]]);
            }

            let columns = [
                search.createColumn({
                    name: "itemid",
                    summary: "GROUP",
                    sort: search.Sort.ASC,
                    label: "Item Name"
                }),
                search.createColumn({
                    name: "unitstype",
                    summary: "GROUP",
                    label: "Primary Units"
                }),
                search.createColumn({
                    name: "custitemsc_availableunits",
                    summary: "GROUP",
                    label: "Available Units"
                }),
                search.createColumn({
                    name: "parent",
                    summary: "COUNT",
                    label: "Parent"
                }),
                search.createColumn({
                    name: "matrix",
                    join: "parent",
                    summary: "COUNT",
                    label: "Matrix Item"
                }),
                search.createColumn({
                    name: "formulatext",
                    summary: "GROUP",
                    formula: "CASE WHEN {matrix}='T' THEN 'matrix-parent' WHEN {parent.matrix}='T' THEN 'matrix-child' ELSE 'non-matrix' END",
                    label: "Matrix"
                })
            ]
            
            // add some columns if conditions are met
            if (lifeIsGood) {
                columns.push(
                    search.createColumn({
                        name: "matrix",
                        summary: "GROUP",
                        label: "Item Matrix?"
                    })
                )
            }

            var searchRequest = returnSearchResults({
                type: "item",
                filters: filters,
                columns: columns,
            });

The result I would expect from this search, would be a nice javascript Array of Objects, where i can reference the direct values as searchRequest[0].itemname or searchRequest[0].primaryunits.value.

It does not matter if the summary type is Sum, Max, Min, Group, Avg etc.. It also means you can have multiple formula columns.

Just ensure your column labels are all unique once you remove all non-alphanumeric fields.

[
   {
      itemname: "INVITEM1 : INVITEM1.24.BLU",
      _itemname: "INVITEM1.24.BLU",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   },
   {
      itemname: "INVITEM1 : INVITEM1.24.GRN",
      _itemname: "INVITEM1.24.GRN",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   },
   {
      itemname: "INVITEM1 : INVITEM1.24.GRY",
      _itemname: "INVITEM1.24.GRY",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   },
   {
      itemname: "INVITEM1 : INVITEM1.24.RED",
      _itemname: "INVITEM1.24.RED",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   },
   {
      itemname: "INVITEM1 : INVITEM1.24.SIL",
      _itemname: "INVITEM1.24.SIL",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   },
   {
      itemname: "INVITEM1 : INVITEM1.24.YEL",
      _itemname: "INVITEM1.24.YEL",
      primaryunits: {
         value: "2",
         text: "Rolls"
      },
      availableunits: {
         value: "2",
         text: "Roll"
      },
      parent: "1",
      matrixitem: false,
      matrix: "matrix-child",
      itemmatrix: false
   }
]

One thing also to note, is that if your search has non-summary result columns, as well as summary, using that search in script will cause unexpected errors. Script executed searches should either have ALL columns summarised, or NO columns summarised.