Get Saved Search values for multiple "formula" columns

Question

Morning Gurus,

I have a saved search within Netsuite with multiple "formula" columns.

For example, there are several formulapercent' named columns, although thelabel' for each is unique.

However when using nlobjSearchResult.getValue('formulapercent') naturally I only get the first formulapercent column value.

How do I specify in getValue which of the formula columns I want to return the value for?

I really don't want to use a column number, in case I need to insert a new column to the saved search within Netsuite later.

Hoping for something along the lines of nlobjSearchResult.getValue('formulapercent','<label>')

I have tried the multi parameter option, but it does not work.

Simple fix?

Cheers

Steve

Answer 1

Found a different way to do this with 2.x. Hope this helps.

    var list = serverWidget.createList({
        title: 'Search Results'
    });

    // Add columns to the list
    list.addColumn({
        id: 'column1',
        type: serverWidget.FieldType.TEXT,
        label: 'Item'
    });
    list.addColumn({
        id: 'column2',
        type: serverWidget.FieldType.TEXT,
        label: 'Sales Description'
    });
    list.addColumn({
        id: 'column3',
        type: serverWidget.FieldType.CURRENCY,
        label: 'formula A'
    });
    list.addColumn({
        id: 'column4',
        type: serverWidget.FieldType.CURRENCY,
        label: 'formula B'
    });
    // Add more columns as needed...

    // Load the (transaction) saved search
    var mySavedSearch = search.load({
        id: 'customsearch4340'
    });
    // Replace 'customsearch_my_saved_search' with the ID of your saved search
    mySavedSearch.run().each(function(result) {
        // Create a new object to store the result values
        var resultObject = {};
        
        // Iterate over the columns in the result
        result.columns.forEach(function(column, index) {
            // Create a new column name
            var newColumnName = column.name;
            if (resultObject[newColumnName]) {
                // If the column name already exists in the resultObject, append "_n" to it
                var n = 1;
                while (resultObject[newColumnName + "_" + n]) {
                    n++;
                }
                newColumnName += "_" + n;
            }

            // Assign the value from the result to the new column in the resultObject
            if (newColumnName === 'item' || newColumnName === 'custitem_oc_isac2' || newColumnName === 'custitem_oc_isac3' || newColumnName === 'custitem_oc_productgroup' || newColumnName === 'custitem_oc_buyer') {
                resultObject[newColumnName] = result.getText(column);
            } else {
                resultObject[newColumnName] = result.getValue(column);
            }
        });

        // Add the search result to the list
        list.addRow({
            column1: resultObject['item'],
            column2: resultObject['salesdescription'],
            column3: resultObject['formulacurrency_10'],
            column4: resultObject['formulacurrency_12'],
            // Add more values as needed...
        });
        
        i++;
        
        // Continue processing until you've processed 4000 results
        return i < 4000;
    });        

    context.response.writePage(list);
}

return {
    onRequest: onRequest
};

});

Answer 2

Thought I'd add an answer I have since learned.

Instead of generically numbering the columns. For example:

var column = []
column[0] = new nlobjSearchColumn('formulanumeric').setFormula('myformula1');
column[1] = new nlobjSearchColumn('formulanumeric').setFormula('myformula2');
searchresults = nlapiSearchRecord(.......);

Instead of this, I found the easiest way to retrieve the formula column values was to uniquely define the columns:

var colformula1 = new nlobjSearchColumn('formulanumeric').setFormula('myformula1');
var colformula2 = new nlobjSearchColumn('formulanumeric').setFormula('myformula2');

var searchresults = nlapiSearchRecord('item',null,filters,[colformula1,colformula2]);

To then grab the formula results:

var formulares1 = searchresults[i].getValue(colformula1');
var formulares2 = searchresults[i].getValue(colformula2');

Removes the issue if column orders change.

Thought this might help somebody.

Answer 3

This works for me using SuiteScript 2.0. Place this into a function and pass in the needed variables

if(join){
    if(summary){
        if(String(name).startsWith("formula")){
            return result.getValue(result.columns[column])
        }else{
            var searchResult = result.getValue({
                                name: name,
                                join: join,
                                summary:summary
                            });
            return searchResult
        }

    }else{
        if(String(name).startsWith("formula")){
            return result.getValue(result.columns[column])
        }else{
            var searchResult = result.getValue({
                                name: name,
                                join: join
                            });
            return searchResult
        }
    }

}else{
    if(summary){
        if(String(name).startsWith("formula")){
            return result.getValue(result.columns[column])
        }else{
            var searchResult = result.getValue({
                                name: name,
                                summary: summary,
                            });
            if((column==7 || column ==8 || column==10 || column==12) && type=='cases'){

                return dropDown_Obj[column].getKeyByValue(searchResult)
            }
            return searchResult
        }

    }else{
        if(String(name).startsWith("formula")){
            return result.getValue(result.columns[column])
        }else{
            var searchResult = result.getValue({
                                name: name
                            });
            return searchResult
        }
    }
}

Answer 4

What I generally do is add a label to the saved search formula columns. Then:

var f1Val, f2Val, etc;
results.forEach(function(res){
  var cols = res.getAllColumns();
  cols.forEach(function(col){
    switch(col.getLabel()){
       case 'formula1' : f1Val = res.getValue(col); break;
       case 'formula2' : f2Val = res.getValue(col); break;
       ...
    }
  });
});

Answer 5

There is a method in the nlobjSearchResult object called getAllColumns(). Then I use the index of the formula columns to get the value.

I dont't know of any other way to get the values of the formula columns. Do note that if you use this method, if you change the order of the columns in the saved search it will break your script.