How to access 'Abbreviation' field of a custom list in NetSuite custom lists

Question

I have a custom list that is used as a matrix option of Inventory item. Its 'Color'. This custom list has an abbreviation column. I am creating a saved search on item and using Color field(join) and trying to access 'Abbreviation' field of color. Abbreviation on custom list is available on when 'Matrix Option List' is checked. Can someone please help me achieve this? I tried to do this through script and it seems like we cannot access 'Abbreviation' column through script. I also tried to use script to write a search directly on 'Color' - custom list and get the 'abbreviation' through search columns. It did not work. Is there a way to access 'Abbreviation' from custom lists?

Thanks in Advance

Answer 1

You can access it via suitescript by using the record type "customlist" and the internal id of the list like so:

var rec = nlapiLoadRecord('customlist', 5);
var abbreviation = rec.getLineItemValue('customvalue', 'abbreviation', 1);
nlapiLogExecution('DEBUG', 'abbreviation', abbreviation);

Keep in mind that the third argument of getLineItemValue is the line number, not the internal ID of the item in the list. If you want to find a specifc line item, you may want to use rec.findLineItemValue(group, fldnam, value).

---

Unfortunately, it doesn't look like this translates to saved searches. The suiteanswer at https://netsuite.custhelp.com/app/answers/detail/a_id/10653 has the following code:

var col = new Array();
col[0] = new nlobjSearchColumn('name');
col[1] = new nlobjSearchColumn('internalid');
var results = nlapiSearchRecord('customlist25', null, null, col);
   for ( var i = 0; results != null && i < results.length; i++ )
   {
      var res = results[i];
      var listValue = (res.getValue('name'));
      var listID = (res.getValue('internalid'));
      nlapiLogExecution('DEBUG', (listValue + ", " + listID));
   }

However, whatever part of the application layer translates this into a query doesn't handle the abbreviation field. One thing to keep in mind is that the 'custom list' record is basically a header record, and each individual entry is it's own record that ties to it. You can see some of the underlying structure here, but the takeaway is that you'd need some way to drill-down into the list entries, and the saved search interface doesn't really support it.

I could be wrong, but I don't think there's any way to get it to execute in a saved search as-is. I thought the first part of my answer might help you find a workaround though.

Answer 2

Here is a NetSuite SuiteScript 2.0 search I use to find the internalId for a given abbreviation in a custom list.

/**
 * look up the internal id value for an abbreviation in a custom list
 * @param string custom_list_name
 * @param string abbreviation
 * @return int
 * */
function lookupNetsuiteCustomListInternalId( custom_list_name, abbreviation ){
    var internal_id = -1;
    var custom_list_search = search.create({
        type: custom_list_name,
        columns: [ { name:'internalId' }, { name:'abbreviation' } ]
    });
    var filters = [];
    filters.push(
        search.createFilter({
            name: 'formulatext',
            formula: "{abbreviation}",
            operator: search.Operator.IS,
            values: abbreviation
        })
    );
    custom_list_search.filters = filters;
    var result_set = custom_list_search.run();
    var results = result_set.getRange( { start:0, end:1 } );
    for( var i in results ){
        log.debug( 'found custom list record', results[i] );
        internal_id = results[i].getValue( { name:'internalId' } );
    }
    return internal_id;
}

Answer 3

Currently NetSuite does not allows using join on matrix option field. But as you mentioned, you can use an extra search to get the result, you could first fetch color id from item and then use search.lookupFields as follows

search.lookupFields({ type: MATRIX_COLOR_LIST_ID, id: COLOR_ID, columns: ['abbreviation'] });

Note: Once you have internalid of the color its better to use search.lookupFields rather than creating a new search with search.create.