CODEWITHSUNDEEP
javascriptnetsuitesuitescript
Salvatore Q Zeroastro
Salvatore Q Zeroastro

Reputation: 758

NetSuite SuiteScript 2 - Access sublist via Search

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!

Upvotes: 1

Views: 3074

Answers (1)

Jon Lamb
Jon Lamb

Reputation: 1473

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;
});

Upvotes: 1

Related Questions