NetSuite SuiteScript 2 - Access sublist via Search

Question

I'm performing a search on customer payments with a given date range, and I need to fetch the invoice reference number of the invoice that has been paid for each customer payment. The invoice reference number is under the sublist apply where the field apply is set to true.

I'll put some piece of code/payload:

    search.create({           
        type: search.Type.CUSTOMER_PAYMENT,
        filters: [['lastmodifieddate', 'within', context.from_datetime, context.to_datetime]],
        columns: [
                'entity',
                'status',
            ]
    }).run().forEach(function(result) {
         // Do stuff
    });

And this is a (short version) of a customer payment payload:

{
"id": "103",
"type": "customerpayment",
"isDynamic": false,
"fields": {
    // payment main fields
},
"sublists": {
    // Other sublists
    "apply": {
        "line 1": {
            "apply": "T",
            "currency": "GBP",
            "refnum": "TEST-00002",
            // Other fields
        },
        "line 2": {
            "apply": "F",
            "currency": "GBP",
            "refnum": "TEST-00001",
            // Other fields            
        }
    }
}

So, in the search columns array, I want to grab the refnum field from the line item where the apply field is T. (in this case should return TEST-00002) It's good enough also to grab the whole apply sublist, then I'll work out the refnum looping into the object.

What I want to avoid is to load every time the payment record as it's going to slow down the search.

Is this possible? Anyone can help? Thanks a lot!

Jon Lamb Jon Lamb · Accepted Answer · 2018-08-21T15:46:35

I believe what you are looking for are the Applied To Transaction fields. You can access these as a join in the UI at the bottom of the list or via SuiteScript like below. In my account, the refnum field is the same as the document number of the Applied To transaction, so I can get the number with the following:

var customerpaymentSearchObj = search.create({
   type: "customerpayment",
   filters:
   [
      ["type","anyof","CustPymt"],
   ],
   columns:
   [
      "tranid",
      "entity",
      "amount",
      "appliedtotransaction",
      "appliedtolinkamount",
      "appliedtolinktype",
      search.createColumn({
         name: "tranid",
         join: "appliedToTransaction"
      }) // <--- This is the one
   ]
});
customerpaymentSearchObj.run().each(function(result){
   // .run().each has a limit of 4,000 results
   var refnum = result.getValue({ name: 'tranid', join: 'appliedToTransaction' });
   return true;
});

NetSuite SuiteScript 2 - Access sublist via Search

1 Answers