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?

Answer 1

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

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

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

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