We are seriously extending NetSuite, and making the experience with the #1 ERP cloud software even more awesome.

Extract list of a transaction’s related records via script

A few years ago I had to implement a procedure that at a certain point had to extract all the payments applying to an Invoice. I just checked now, and I see that in a search I can use the filter "Applied To Transaction" (ID = appliedtotransaction) and this should be enough, but back then, either because I didn't spend enough time to investigate NS standard, or because this filter wasn't available yet, I pushed myself one more time into reverse engineering of the Invoice object.

Thanks to these investigations I found out that it's possible to read via script the information shown in the "Related Records" subtab (which includes for example the Payments applied to an Invoice). The way to access the sublist is quite straight, you just load the record and loop over the sublist with ID "links".

The only problem is that in this list there seem to be only the related transactions internal IDs and some UI information. For example the value corresponding to the list column "Type" is actually retrievable only as a label and not as record type ID or transaction type ID (e.g. "Item Fulfillment" rather than "itemfulfillment" or "ItemShip"), which means it cannot be used safely to identify which transaction type corresponds to that line (the label may vary depending on the language set in the UI).

Luckily there is a workaround, the only information within the sublist line that can identify the transaction type in a deterministic way is the URL (sublist field with ID "linkurl"), which will contain "/app/accounting/transactions/custpymt.nl" if it's a payment, "/app/accounting/transactions/itemship.nl" if it's an Item Fulfillment, and so on.

ketka: NetSuite apps and services

An example of usage of this trick is shown below, this function generates a JavaScript array of related records given a specific transaction. The example is written in SS 1.0, just because I could copy / paste it from some code I found, but can quickly be adapted SS 2.0, just substituting the 1.0 API with the corresponding 2.0 ones, while keeping the same algorithm.

If you copy / paste / execute the code below in the browser console within a NetSuite page, and then invoke the function with a transaction type / id that you know it has related records, you can quickly see that it will return an array describing them (note that since I didn't cover all the possible transaction types, if you test it on related records not covered below, the corresponding elements in the returned array will have type = null).

I re-adapted this code quickly from my old script, so if you want to use it in a business project I recommend to add proper error / edge cases handling, and of course complete the list of possible related records.

// given a transaction record type (salesorder, invoice, etc.) and internal ID, it
// returns an array where each object represents a related record attached to it, and
// is composed by:
// .type = the transaction record type (salesorder, invoice, etc.)
// .id = the transaction internal ID
// example of usage: getRelatedRecords('salesorder', 1234);
function getRelatedRecords(transactionRecordType, transactionInternalID) {
  var recTransaction = nlapiLoadRecord(transactionRecordType, transactionInternalID);
  var linksCount = recTransaction.getLineItemCount('links');
  var relatedRecords = [];
  for (var i = 1; i <= linksCount; i++) {
    var url = (recTransaction.getLineItemValue('links', 'linkurl', i) || '').toLowerCase();
    var relatedTranInternalID = recTransaction.getLineItemValue('links', 'id', i);

    // based on the URL, I will define the record type
    // NOTE: the URL read from the sublist is not the complete URL to the transaction
    //       but only to a new transaction of that same type, don't know if this is
    //       a NS bug or something designed, anyway given record type + internal ID
    //       the URL can be retrieved via NS API
    var relatedTranRecordType = null;
    if (url.indexOf('/app/accounting/transactions/salesord.nl') === 0) {
      relatedTranRecordType = 'salesorder';
    } else if (url.indexOf('/app/accounting/transactions/custinvc.nl') === 0) {
      relatedTranRecordType = 'invoice';
    } else if (url.indexOf('/app/accounting/transactions/custpymt.nl') === 0) {
      relatedTranRecordType = 'customerpayment';
    } else if (url.indexOf('/app/accounting/transactions/itemship.nl') === 0) {
      relatedTranRecordType = 'itemfulfillment';
    } else {
      // TODO: add additional else-if or handle fallback scenario
    }
    relatedRecords.push({
      type: relatedTranRecordType,
      id: relatedTranInternalID
    });
  }
  
  return relatedRecords;
}


In case the algorithm fails to identify the transaction types because of some change in the structure of the links sublist, a fallback way to keep the function working would be to load the types through a transaction search on the sublist extracted internal IDs, obviously with a single search call after looping the sublist. In order to avoid additional impact on performance and script units usage, I would still keep as primary algorithm the trick shown above.
alessio |
December 3, 2019

Leave a Reply

Your email address will not be published. Required fields are marked *