SuiteScript2.0 Getting values from a saved search formula column

Question

I am trying to get the value from a formula field in a saved search. I am getting all the results and then looping through them as such.

for(key in itemReplenishResults){
    log.debug("Single Data", JSON.stringify(itemReplenishResults[key]));
    var thisNumber = Number(itemReplenishResults[key].getValue("formulanumeric_1"))
}

The log for the single data read as I would expect;

{
    "recordType": "inventoryitem",
    "id": "2131",
    "values": {
        "itemid": "ITEMCODE",
        "displayname": "DISPLAYNAME",
        "salesdescription": "SALESDESCRIPTION",
        "type": [
            {
                "value": "InvtPart",
                "text": "Inventory Item"
            }
        ],
        "location": [],
        "locationquantityonhand": "",
        "locationreorderpoint": "0",
        "locationpreferredstocklevel": "1",
        "formulatext": "Yes",
        "formulanumeric": "1",
        "locationquantityonorder": "",
        "formulanumeric_1": "1",
        "formulatext_1": "Yes"
    }
}

But the value of thisNumber is returned as 0. I do not understand why this is not working?

Adam · Accepted Answer · 2021-11-10T03:36:16.207

The reason thisNumber is 0 is because the column formulanumeric_1 actually doesn't exist, so Number() is converting a null value to 0.

The problem

Formula columns are all named the same internally, which is why you can't directly get their values individually. For instance, if you have two Formula (Numeric) columns in your search, both internal column names will be "formulanumeric". Therefore, using getValue('formulanumeric') will get only the first formula value.

Logging a Result object will output the incorrect names as you have seen. If you log Result.columns you will see that the column names are actually the same.

Your choices

Directly get columns using indexes (if you care about ordering).

require(['N/search'], function(search) {
  // A saved search containing multiple formula result columns
  var s = search.load(1234);

  s.run().each(function(result) {
    log.debug(result.getValue(result.columns[5]));
    log.debug(result.getValue(result.columns[1]));
    log.debug(result.getValue(result.columns[3]));
  });
});

Loop through each column (if you don't care about ordering).

require(['N/search'], function(search) {
  // A saved search containing multiple formula result columns
  var s = search.load(1234);

  s.run().each(function(result) {
    for (var i = 0; i < result.columns.length; i++) {
      // Log each column's value in order
      log.debug(result.getValue(result.columns[i]));
    }
  });
});

Manually define columns and reference them (the most versatile option). Column names must start with "formula[type]" and can be appended with anything else.

require(['N/search'], function(search) {
  var s = search.create({
    type: 'salesorder',
    columns: [{
      name: 'formulanumeric1',
      formula: 'CASE WHEN 100 > 10 THEN 100 END'
    }, {
      name: 'formulanumeric2',
      formula: 'CASE WHEN 30 > 20 THEN 30 END'
    }]      
  });

  s.run().each(function(result) {
    log.debug(result.getValue('formulanumeric2')); // 30
    log.debug(result.getValue('formulanumeric1')); // 100
  });
});

NOTE: If you go with the first option, you'll have to be careful about reordering the columns in your initial search, as that will affect the indexing in the results.

score 0 · Answer 2 · answered Aug 21 '19 at 13:37

When I'm working with formula fields, I always use the column index values. An example of one that I have is below. This matches up with the fifth column I created (starting at 0, of course). Hope that helps.

transactionSearch.run().each(function(r){
  var var1=r.getValue(r.columns[5]);
  var var2=r.getValue(r.columns[6]);
  var var3=r.getValue(r.columns[7]);
  var var4=r.getValue(r.columns[8]);
  var var5=r.getValue(r.columns[9]);
  return true;
});

SuiteScript2.0 Getting values from a saved search formula column

2 Answers2

The problem

Your choices