SuiteScript 2.0 – Summary Search on Joined custom record types

NetSuite has voluminous documentation. That’s both good news and bad. I never could find an example of how to summarize a joined field. Perhaps it is out there. Who knows? So… I fiddled with it until it surprized me and worked! The answer turned out to be both intuitive and tricky.

Here’s how this works.

  1. I’ve got a custom table called custom_record_logic_spa_filters
  2. It has a field called custrecord_spa which joins to custom_record_logic_spas.
  3. custrecord_customer then joins with Customers
  4. I want a list of customers.

Search Joins Diagram

Here is the code that rolls up by custrecord_spa and also by Customer.

Code Sample

It would be nice to remove the roll up on custrecord_spa. Unfortunately, you can’t. If you pull that level out, it blows up.

In this case, it was easy enough to roll up a list of customers in code after the search completed.

Here is the code in a form you can cut and paste into your project.

require([‘N/search’, ‘N/log’],
   function (search, log) {
      var mySearch = search.create({
         type: ‘customrecord_logic_spa_filters’,
         columns: [
            search.createColumn({name: ‘custrecord_spa’, summary: search.Summary.GROUP}),
            search.createColumn({name: ‘custrecord_customer’, join: ‘custrecord_spa’, summary: search.Summary.GROUP})
         ],
         filters: []
      });
      var myPages = mySearch.runPaged({ pageSize: 1000 });
      for (var i = 0; i < myPages.pageRanges.length; i++) {
         var myPage = myPages.fetch({ index: i });
         myPage.data.forEach(
            function (result) {
               var spa = result.getText({name: ‘custrecord_spa’, summary: search.Summary.GROUP});
               var customer = result.getValue({name: ‘custrecord_customer’, join: ‘custrecord_spa’, summary: search.Summary.GROUP});
               log.debug(customer);
            }
         )
      }
   }
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s