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

bknights · Answer 1 · 2017-02-23T16:15:53.340

6

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;
       ...
    }
  });
});

edited Feb 23 '17 at 16:15

answered Feb 23 '17 at 06:27

bknights

14,408
2
18
31

Thanks for the answer. What about actually returning the value? I've tried getValue with the label (all columns have a label), but i get `null` – Steve Reeder Feb 23 '17 at 10:18
See the edit. You can use the column itself as a key – bknights Feb 23 '17 at 14:20
Thanks BK, I'll try it out this morning :) – Steve Reeder Feb 23 '17 at 22:34
This is a really good answer. The only things to change to make it SuiteScript 2 ready are res.getAllColumns() change to res.columns and col.getLabel() to col.label – Sundance.101 Apr 18 '19 at 22:21

score 4 · Accepted Answer · answered Jan 21 '18 at 02:37

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.

score 2 · Answer 3 · answered Feb 23 '17 at 05:30

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.

score 2 · Answer 4 · answered Oct 06 '17 at 03:36

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

Get Saved Search values for multiple "formula" columns

4 Answers4