CODEWITHSUNDEEP
netsuitesuitescriptsuitescript2.0
adn
adn

Reputation: 99

Suitescript N/Search Filter by custom sublist line field

I am trying to add a search filter for a custom line item field in a suitelet and it is returning no results.

   function getExpenseSearch(soNum){
      log.debug('getExpenseSearch entered')
      log.debug('soNum: ' + soNum)
      var billSearch = search.create({
         type: 'transaction',
         filters: [
         [ 'type', search.Operator.ANYOF , ['VendBill']], 'and',
         ['mainline', search.Operator.IS,['F']], 'and',
         ['custcol_connected_so', search.Operator.ANYOF, [soNum]] ///<=== this is the problem why is it not registering?
         ],
         columns: ['trandate',
                   'tranid',
                   'amount'
         ]
      }).run().getRange({start: 0, end: 100})
      log.debug('return billSearch[0].tranid: ' + billSearch[0].tranid) //<== always undefined

      return billSearch
      }

I have isolated the problem to the sublist field custcol_connected_so is a List field (of sales orders) soNum is the netsuite internal id of the record

I have already tried the following:

In the records browser there is no join table for the vendorBill so I would think just the custcol_connected_so filter should work fine.

Upvotes: 0

Views: 1895

Answers (2)

bknights
bknights

Reputation: 15447

Taking another look there are a couple of issues.

Your syntax for getting the tranid is incorrect (or at least not supported) and you would not be able to get the mainline amount with a single query because you are only going to be able to return the line level amount. In the example below you could use ref.id to load the sales order or to do a lookup Fields call:

This works in my account:

require(['N/search'], search=>{
    search.create({
        type:'creditmemo',
        filters:[
            ['mainline', 'is', 'F'], 'AND',
            ['custcol_linked_transaction', 'anyof', [2403605]]
        ], 
        columns:['tranid', 'custcol_linked_transaction']
    }).run().each(ref=>{
        console.log(ref.id, ref.getValue({name:'tranid'}), ref.getValue({name:'custcol_linked_transaction'}));
        return false;
    });
});

Upvotes: 1

bknights
bknights

Reputation: 15447

If soNum is the visible sales order number you'd need to get the SO's internal id in order to run that search. Consider:

function getExpenseSearch(soNum) {
   log.debug('getExpenseSearch entered')
   log.debug('soNum: ' + soNum)

   var soInternalId = null;
   search.create({
      type: 'salesorder',
      filters: ['tranid', 'is', soNum]
   }).run().each(function(ref) {
      soInternalId = ref.id;
      return false;
   });

   var billSearch = search.create({
      type: 'vendorbill',
      filters: [
         ['mainline', 'is', 'F'], 'and',
         ['custcol_connected_so', 'is', soInternalId] ///<=== this is the problem why is it not registering?
      ],
      columns: ['trandate',
         'tranid',
         'amount'
      ]
   }).run().getRange({
      start: 0,
      end: 100
   })
   log.debug('return billSearch[0].tranid: ' + billSearch[0].tranid) //<== always undefined

   return billSearch
}

Upvotes: 0

Related Questions