How to use joins for sublists values in search expressions

Question

nlapi allows to use joins to refer to values in another record or a sublist, when searching for a record. E.g. if there is a record FOO

FOO: {
  type: 'rectype',
  field1: '111',
  sublists:
    bar: [
      {value: 1,}, {value: 2,}
    ]
}

I can search for this record with a query, provided there is a join, let's say, bar to the bar sublits:

nlapiSearchRecord('rectype', null,
  [
    ['field', 'equalto', 'bar',],
    'and',
    ['bar.value', 'equalto', '1',],
  ],
  [ new nlobjSearchColumn('anotherfield', null, null), ]
);

This works. And the question is: is there a way to find a record (the one from the example) by specifying more sublist values, like:

nlapiSearchRecord('rectype', null,
  [
    ['field', 'equalto', 'bar',],
    'and',
    ['bar.value', 'equalto', '1',],
    'and',
    ['bar.value', 'equalto', '2',],
  ],
  [ new nlobjSearchColumn('anotherfield', null, null), ]
);

I've tried numerous approaches with different expressions, but it finds nothing with such a query.

A more concrete example is to find a bomrevision, which has specific components and bomquantities (the commented code is just for demonstration purposes of some approaches with the filter expression I've tried):

nlapiSearchRecord('bomrevision', null, [
    ['isinactive', 'equalto', 'F',],
    'and',
    ['component.item', 'is', '4942',],
    'and',
    ['component.item', 'is', '4936',],
    //[
    //  'and',
    //  ['component.bomquantity', 'equalto', '38',],
    //],
    //'and',
    //[
    //  ['component.item', 'anyof', '4936',],
    //  'and',
    //  ['component.bomquantity', 'equalto', '38',],
    //],
  ],
  [ new nlobjSearchColumn('name', null, null), ]
);

Thank you

Answer 1

I understand your question to be "How can I find items where the component list includes multiple specified items?"

The challenge here is that when you join to the component list, NetSuite returns a result for each line in the list. This means any line that contains 'Item 1' will not contain 'Item 2' and vice versa, so no result meets the criteria.

You can work around this using the following approach:

1. Group the results by 'main' or 'parent' record ('FOO' or 'bomrevision' in your examples).
2. Use NS_CONCAT to give a field in each result that contains values from all the sublist results. (Note that NS_CONCAT is undocumented, so use at your own risk).
3. Use summary criteria formulae to inspect the concatenated field for each value of interest.

Example (tested with assembly items and their member items as I don't have access to an account with BOM revisions):

[
   ["type","anyof","Assembly"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({memberitem.name}) )","contains","ITEM 1"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({memberitem.name}))","contains","ITEM 2"]
] 

Or, for your 'bomrevision' (untested, adapted from above):

[
   ["min(formulatext: NS_CONCAT({component.item}) )","contains","4942"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({component.item}))","contains","4936"]
]

Be aware that under some circumstances NetSuite will default to returning the text of a list field rather than the value. If this is the case (and the values shown in your example are internal IDs as I'm assuming), you may need to replace component.item with component.item.id, or change the search values to the text name instead.

There may be a more straightforward way of doing this so I'd be glad to hear from anyone with a better solution.

---

Edit in response to request in comments:

Here is the full code of a working example in my account:

var assemblyitemSearch = nlapiSearchRecord("assemblyitem",null,
[
   ["type","anyof","Assembly"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({memberitem.name}) )","contains","ITEM 1"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({memberitem.name}))","contains","ITEM 2"]
], 
[
   new nlobjSearchColumn("itemid",null,"GROUP").setSort(false), 
   new nlobjSearchColumn("displayname",null,"GROUP"), 
   new nlobjSearchColumn("salesdescription",null,"GROUP"), 
   new nlobjSearchColumn("formulatext",null,"MIN").setFormula("NS_CONCAT({memberitem})")
]
);

This is an Item search filtering by Member Items, as the closest thing in my account to the BOM Revisions in the example. The only change I have made is to replace the item names with "ITEM 1" and "ITEM 2". You would need to replace these with relevant items from your system.

I can go to a Sales Order page and open developer tools on my browser and add the above code to create a search, then append the following additional code to retrieve results and print out to the console:

var data = [];
assemblyitemSearch.forEach(r => {
    var vals = {};
    r.rawValues.forEach(v => {
        vals[v.name] = v.value
    });
    data.push(vals);
});
console.table(data); 

This returns a table with results exactly as expected.

---

Edit 2: Adding example using Sales Orders and Items as possibly more consistent between different accounts.

var salesorderSearch = nlapiSearchRecord("salesorder",null,
[
   ["type","anyof","SalesOrd"], 
   "AND", 
   ["trandate","within","thismonth"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({item}))","contains","ITEM 1"], 
   "AND", 
   ["min(formulatext: NS_CONCAT({item}))","contains","ITEM 2"]
], 
[
   new nlobjSearchColumn("trandate",null,"GROUP"), 
   new nlobjSearchColumn("tranid",null,"GROUP"), 
   new nlobjSearchColumn("formulatext",null,"MIN").setFormula("NS_CONCAT({item})")
]
);