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

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 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

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.

Answer 4

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
        }
    }
}